Overwriting queries in code

[Lauren]

I'm trying to write a code in which one of the results is
the creation of 5 queries. This code is used often, so I
need to have the old queries overwritten every time.
Currently I have the code written so that before the
queries are created, the old ones are deleted, but the
code is running slow and I think it is this process that
is doing it. I've tried "DoCmd.SetWarnings False", but I
still get "This query already exists..." message. Does
anyone know a better, more efficient way?

 

[Jen]

Hi,

Try going to tools>options>edit/find and deselect
the "action queries" checkbox. I've run into this problem
with overwriting tables and not wanting to see the
messages.

Jen

 

[Andrew Smith]

You can change the SQL of a saved query using a DAO query def object:

Dim qdf as DAO.QueryDef
Dim strSQL as String

strSQL = "SELECT Something FROM Somewhere WHERE Something = SomethingElse"
Set qdf = CurrentDb.QueryDefs("NameOfQuery")
qdf.SQL = strSQL

Will this do what you want?

 

[tracey meyer]

You could try the qureydef procedure or use some recordset
objects. I have had similar needs in the past. I
created new procedures for each query via qureydef and
created new procedures for each query to be deleted (this
gave me the ability to handle errors). I ran the delete
query procedure as the first line in the create query
procedure. And then I ran all of the create query procs
from another procedure which also gave me the ability to
handle errors. Note: if you are creating a query that
returns information that is then used as criteria in
another query, then you may want to explore the recordset
object If you are upadating, deleting or appending data
then you can just run a sql statement via (docmd.runSQL).
The recordset and runSQL methods do not create an object,
the querydef does create an object. If you are using
access 2000 or 2002 then I would suggest using the ADO
methods.