Deleting an Existing Query using VBA (Access 2000)

[Douglas J. Steele]

Easiest way is just to delete it, and trap the error that will be generated
if the query doesn't exist.

On Error Resume Next
db.QueryDefs.Delete "TempTable"

Is there any reason you're naming the query, though? It's not necessary.

 

[L.A. Lawyer]

I want to programmically see if a query already exists and then delete it in
order to replace it with another query of the same name. Here is how I am
creating the new one:

Dim temp As String
temp = Me!CaseIDListed
Dim db As Database, qdf As querydef, strSQL As String
strSQL = "Select * from CasesQuery where cases.caseid = " & temp & ";"
Set db = CurrentDb
Set qdf = db.CreateQueryDef("TempTable", strSQL)
RefreshDatabaseWindow

This generates an error message when TempTable already exists.

How do I fix this?

 

[Peter De Baets]

Try this:

on error resume next
DoCmd.DeleteObject acQuery, "TempTable"
err.clear
on error goto 0 '* put your "on error" statement here

.... this code will delete your query if it exists. If it doesn't exist, then
the error generated will be ignored.

Hope this helps,

 

[Dirk Goldgar]

I want to programmically see if a query already exists and then
delete it in order to replace it with another query of the same name.
Here is how I am creating the new one:

Dim temp As String
temp = Me!CaseIDListed
Dim db As Database, qdf As querydef, strSQL As String
strSQL = "Select * from CasesQuery where cases.caseid = " & temp & ";"
Set db = CurrentDb
Set qdf = db.CreateQueryDef("TempTable", strSQL)
RefreshDatabaseWindow

This generates an error message when TempTable already exists.

How do I fix this?

Since you always want to replace the query if it exists, the simplest
thing to do is to just delete it and ignore the error that occurs if it
doesn't exist:

Dim temp As String
temp = Me!CaseIDListed
Dim db As Database, qdf As querydef, strSQL As String
strSQL = "Select * from CasesQuery where cases.caseid = " & temp &
";"
Set db = CurrentDb

On Error Resume Next
db.QueryDefs.Delete "TempTable"
On Error GoTo 0 ' or your own error-handler

Set qdf = db.CreateQueryDef("TempTable", strSQL)
RefreshDatabaseWindow

Depending on your needs, you could possibly take a different approach --
if the querydef already exists, just update its SQL property:

Set qdf = Nothing
On Error Resume Next
Set qdf = db.QueryDefs("TempTable")
On Error GoTo 0 ' or your own error-handler
If qdf Is Nothing then
Set qdf = db.CreateQueryDef("TempTable", strSQL)
Else
qdf.SQL = strSQL
End If
RefreshDatabaseWindow

I believe the latter approach would be the most efficient and lead to
less bloating of your database.