Query Def and multi user environment

[Andy_Khosravi]

I'm trying to build a search utility for users to find 'inquiries' in
my database that involves several tables. This is normally easy to do
with the query builder, but I have a unique situation involving a multi
select listbox. Unfortunatly, my SQL skills are somewhat limited, so
I'm not sure if there is an easy way around it. To simplify the
explanation, I'll simplify the table/field setup to get at the meat of
the question.

I have a table with a field called IssueID in table tblMain, and
another field called ProductType in tblProductType. Each IssueID may
have many ProductTypes. tblMain and tblProductType linked together in a
one to many relationship.

Now, I have a search form, which allows users to enter in parameter
query criteria for tblMain. However, criteria for the linked table
tblProductTypes are in a multi select list box. Clicking the 'Find'
button needs to find all matches in tblMain limited to only IssueID's
that contain at least one of the ProductTypes in tblProductType.

It's easy enough to build an SQL statement of the listbox by cycling
through all selected objects and concatenating a string together. The
sticking point is that I need (well, at least I think I need) an actual
object to reference for the INNER JOIN statement in the SQL statement
for tblMain.

I researched some code for QueryDef's, but I'm not sure this will do
the trick. I found the following code in the Help File, which is easy
enough to understand.

Sub NewQuery()
Dim dbs As Database, qdf As QueryDef
Dim strSQL As String

' Return reference to current database.
Set dbs = CurrentDb
' Refresh QueryDefs collection.
dbs.QueryDefs.Refresh
' If RecentHires query exists, delete it.
For Each qdf in dbs.QueryDefs
If qdf.Name = "RecentHires" Then
dbs.QueryDefs.Delete qdf.Name
End If
Next qdf
' Create SQL string to select employees hired on or after 1-1-94.

strSQL = "SELECT * FROM Employees WHERE HireDate >= #1-1-94#;"
' Create new QueryDef object.
Set qdf = dbs.CreateQueryDef("RecentHires", strSQL)
' Open query in Datasheet view.
DoCmd.OpenQuery qdf.Name
Set dbs = Nothing
End Sub

The problem here is that I'm seeing the old query getting deleted and a
new one made each time this would run. I'm thinking that could be a
problem in a multi user environment if two users clicked the 'Find'
button at the same time. I.E., the queryDef is made by User1, but
before the SQL statement executes to reference that new query, User2
has already deleted and replaced the query with their own criteria
based off the multiple objects user2 selected in the listbox.

Is there a way I can do this all in a single SQL statement? Or are my
fears about queryDefs in multi-user environments unfounded?

Thanks in advance for any help or advice any of you may have.

[pietlinden]

The problem here is that I'm seeing the old query getting deleted and a
new one made each time this would run. I'm thinking that could be a
problem in a multi user environment if two users clicked the 'Find'
button at the same time. I.E., the queryDef is made by User1, but
before the SQL statement executes to reference that new query, User2
has already deleted and replaced the query with their own criteria
based off the multiple objects user2 selected in the listbox.

Is there a way I can do this all in a single SQL statement? Or are my
fears about queryDefs in multi-user environments unfounded?

Why would this be a problem at all if you split the FE and BE? Then
the qdf would be in the copy of the FE on the local machine, so by
definition, only one user could use that qdf at a time....

[Andy_Khosravi]

pi********@hotmail.com wrote:

The problem here is that I'm seeing the old query getting deleted and a
new one made each time this would run. I'm thinking that could be a
problem in a multi user environment if two users clicked the 'Find'
button at the same time. I.E., the queryDef is made by User1, but
before the SQL statement executes to reference that new query, User2
has already deleted and replaced the query with their own criteria
based off the multiple objects user2 selected in the listbox.

Is there a way I can do this all in a single SQL statement? Or are my
fears about queryDefs in multi-user environments unfounded?

Why would this be a problem at all if you split the FE and BE? Then
the qdf would be in the copy of the FE on the local machine, so by
definition, only one user could use that qdf at a time....

I wish I could split it. But, I'd have to distribute the FE to 500+
people (It has low numbers of concurrent users before any of you freak
out), and any time a change was made I'd then have to figure out an
effective means of redistributing it to all 500 people. Since it's not
a capital project, the IS department wont give me any backup by just
pushing it out to the designated users.

It's basically a research tool that people will be using infrequently
(perhaps two or three times a month for some). They don't use it often,
but when they do it has to work without any hassles. (such as upgrading
their FE to the latest version).

If I could develop something I knew I'd never have to change, then I'd
do it in a heartbeat. However, they are constantly re-organizing
departments and/or workflows, which can require significant changes to
the UI. In short, this is something that I have to constantly maintain,
and that just isn't easy to do in a FE/BE setup.

[pietlinden]

Okay, I see the problem!

You could do something like grabbing the currentuser or the machine
name and save the query with that name, which would make it unique.
(Or some variation of that, say as a suffix)

You might want to look at Tony Toews' site to read up on distributing
the FE automatically. Not trying to do the fingerwag thing, but just
open up some options you may not have considered.

http://www.granite.ab.ca/access/ufad.htm

Tony covers a lot of common problems, so you might want to take a look
before you write FE distribution off.

Hope it helps,

Pieter

[Andy_Khosravi]

Some good ideas there. I am already capturing Novel login names for
other purposes, makes sense that I could use that to prevent
collisions.

And thanks for the pointer to the Automatic FE distributer. I'll check
that out. A performance gain from distributed setup would certainly be
welcome as this database is beginning to chug under current workload.