Any better way to do the query

  • Thread starter Thread starter Jack
  • Start date Start date
J

Jack

Hi,
I am using the following query using some complex sql in stlinkcriteria to
run the query.
CODE:
Dim sql As String
sql = "select * from qryRptIndustrialCar where " & stLinkCriteria & ""
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strsql As String
Set db = CurrentDb

Set qdf = db.CreateQueryDef("test", sql)

DoCmd.OpenQuery "test"

Basically I am using this for user to click a button that will run the query
to generate the output.

Now if a user is generating the query and is keeping the query open I am
wondering if another user tries to open the query whether the
other user will be prevented from opening the same query.

I would like to know if that will be the case. If so, what is the best
approach to resolve this. Thanks.
 
Jack said:
Hi,
I am using the following query using some complex sql in stlinkcriteria to
run the query.
CODE:
Dim sql As String
sql = "select * from qryRptIndustrialCar where " & stLinkCriteria & ""
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strsql As String
Set db = CurrentDb

Set qdf = db.CreateQueryDef("test", sql)

DoCmd.OpenQuery "test"

Basically I am using this for user to click a button that will run the
query
to generate the output.

Now if a user is generating the query and is keeping the query open I am
wondering if another user tries to open the query whether the
other user will be prevented from opening the same query.

I would like to know if that will be the case. If so, what is the best
approach to resolve this.
Click to expand...

I believe that will be the case, or at least there is a good chance that
they will interfere with one another. The resolution is not to let two
users run the same front-end database. If you are going to have multiple
simultaneous users, you should split your database into a back-end file
(containing only the tables) and a front-end file (containing the queries,
forms, reports, macros, and modules). The front-end has only linked tables,
pointing to the back-end database. Then each user has his own copy of the
front-end. Because each user has his own copy, they don't interfere with
one another when it comes to the design of front-end objects. Furthermore,
if one user's front-end database is lost or corrupted, that user can just go
get a new copy of the "master" front-end, which you store on a server,
without affecting any of the other users.

This design principle holds even if you don't do tricksy stuff like
modifying the design of objects on the fly.
 
Thanks Dirk for the nice explanation. We have sql server as backend and
access as front end. The application runs via Citrix server and there is just
one application on the server. So our architecure seems to be such that if a
user is using this and not closing the query then this query is blocked for
other users.
Under this scenario what remedy do I have.
Regards.
 
Jack said:
Thanks Dirk for the nice explanation. We have sql server as backend and
access as front end. The application runs via Citrix server and there is
just
one application on the server. So our architecure seems to be such that if
a
user is using this and not closing the query then this query is blocked
for
other users.
Under this scenario what remedy do I have.
Click to expand...


Although I haven't worked with the Citrix scenario myself, I am told that
the best way to set it up is to give each user their own copy of the
front-end *on the Citrix server*, stored in the user's profile.
 
Thanks Dirk for the nice explanation. We have sql server as backend and
access as front end. The application runs via Citrix server and there is just
one application on the server. So our architecure seems to be such that if a
user is using this and not closing the query then this query is blocked for
other users.
Under this scenario what remedy do I have.
Click to expand...

My clients all use Citrix in this way. There's a shared backend (either SQL or
a .mdb file); each user has an individual copy of a .mde frontend, linked to
the backend tables, stored in their private folder on the Citrix server.
 
Back
Top