Multi-User QueryDef?

[Jaazaniah]

I'm putting together a form meant purely to lookup values in a
consolidated Access db. I've got most everything working with one
small hitch - how to make the VB-generated SQL query multi-user
friendly without blowing the size of the db through the stratosphere?
I started out using a set named Query to test the options of the form
"SearchQueryResults", but with the possibility of two users running a
search at once (or even one still having it open while the script
tries to redefine it), I can't keep doing that. I've seen a few
examples around that use CurrentDb.CreateQueryDef(""), but when I try
using that method, I can't open it. I am currently using
DoCmd.OpenQuery, as CurrentDb.CreateQueryDef("").OpenRecordSet doesn't
seem to be working. I don't want to define a serialized query
everytime, as it creates more hassle for cleanup and possible issues
with needing to compact more often. If it helps in methods at all,
we're expecting no more than 25 concurrent users. Any help would be
greatly appreciated.

[Allen Browne]

There are a couple of several issues contributing to your problem.

1. Split the database.
Any multi-user database should be split.
If that's a new concept, see:
http://allenbrowne.com/ser-01.html
Once split, the querydefs (which are in the front end) do not interfere with
each other.

2. Use forms for interfaces
Don't open queries directly. Use forms - in Database view if you want that
appearance.

If you display the search results in a form, you can assign the SQL
statement to the RecordSource property of the form. You don't need a saved
query, and there is no bloating.

In fact, you may be able to just set the Filter of the form if you prefer.
For a downloadable example of how to build this dynamically, see:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Jaazaniah" <Ja****@gmail.comwrote in message
news:11*********************@p10g2000cwp.googlegro ups.com...

I'm putting together a form meant purely to lookup values in a
consolidated Access db. I've got most everything working with one
small hitch - how to make the VB-generated SQL query multi-user
friendly without blowing the size of the db through the stratosphere?
I started out using a set named Query to test the options of the form
"SearchQueryResults", but with the possibility of two users running a
search at once (or even one still having it open while the script
tries to redefine it), I can't keep doing that. I've seen a few
examples around that use CurrentDb.CreateQueryDef(""), but when I try
using that method, I can't open it. I am currently using
DoCmd.OpenQuery, as CurrentDb.CreateQueryDef("").OpenRecordSet doesn't
seem to be working. I don't want to define a serialized query
everytime, as it creates more hassle for cleanup and possible issues
with needing to compact more often. If it helps in methods at all,
we're expecting no more than 25 concurrent users. Any help would be
greatly appreciated.