How to create/delete queries with VBA like you can with tables?

[tetsuo2030]

Greetings all,

(Access 2003)

The bosses want me to take an old version of a database designed with a single-user-at-a-time mentality and update it to allow multiple, simultaneous users. The biggest trouble is that, based on its current design, simultaneous users could be potentially overwriting each other's data. I do have a work-around: I'll set up new temp-tables by capturing their in-house network usernames (JOHNSMITH) and treat it as a string variable that I can dump into the current code (with some modifications, of course). Essentially, I create/delete user-specific temp-tables by attaching the username to the end (i.e. old tbl_temp would now be tbl_temp_JOHNSMITH). So, when a user logs in, it autocreates their temp-tables, and all the things they do happen in their temp-tables (update master at the end).

But I hit a snag: one of the code-chunks references a saved query ("qry_bogus"); qry_bogus is just a dummy query whose querydef gets rewritten again and again. thus, it would be feasible for one user to click a button, setting the querydef to one line of SQL, then another person would come in, click their button and redefine it again, ruining the initial user's querydef--same problem I had before my temp-table solution.

So, can I create/delete a query strictly through VBA the way I create and delete tables? Is there a CreateQueryDef ability? Is there a "DROP QUERY"? That way, all I'd have to do is create/delete qry_bogus_JOHNSMITH the way I create/delete tbl_temp_JOHNSMITH. Each user would then be redefining the querydef of his own personal "qry_bogus".

Can this be done?

[beacon]

Hi tetsuo,

To answer your questions:

So, can I create/delete a query strictly through VBA the way I create and delete tables?

Yes

Is there a CreateQueryDef ability?

Yes

Just search for "VBA Create Query" online and you'll pull up a number of easy examples to work with.

Hope this helps and good luck,
beacon

[tetsuo2030]

Wow...you're right. I had it stuck in my head to look for "DROP QUERY" and couldn't find anything; should have looked for CreateQueryDef

Thanks!!

[NeoPa]

You can use CurrentDb.CreateQueryDef(Name:="", SQLText:="Blah blah blah") to create a new query that is Temporary. That is, it is session level and is dropped automatically when the session terminates (You close the database or Access). Alternatively, you can use

Call DoCmd.CopyObject()

to make a copy of qry_Bogus and give it a name that reflects the user and the Date/Time it was created. If managed correctly this can be deleted when finished with, and you never again need worry about query updates affecting other users. This is designing for multiple-users, and makes the code re-entrant (which is necessary for multi-user projects).