Deleting a temp query

  • Thread starter Thread starter alan
  • Start date Start date
A

alan

I have a module that creates a temp query and then deletes
it before the module closes. This all works fine provided
the module completes. If for any reason the module is
stopped, the query is not deleted and the next time the
module runs it produces an error when it tries to create
the query saying the query already exists. I can manuualy
delete the query and rerun the module but I need to be
able to check to see if the query is there before creating
it. I just don't know how to do that. Any help? Thanks.
 
Probably the easiest way would be to trap the error that tells you that the
query is still there, and delete it if the error occurs - eg:

On error resume next
Line of code that might give the error
If Err.Number = the error number you get Then
Code to delete the temp query
Repeat the line that gave the error
Err.clear
End if
On Error GoTo 0 (or goto your normal error handler).

Or, much simpler and I don't know why I did not think of it before, put this
at the start of the procedure:

On Error Resume Next
Code to delete the query
On Error GoTo 0

Finally, you can make sure that the query always gets deleted in the
original procedure by using an error handler in this procedure, eg:

On Error GoTo HandleErrors

'Your normal code goes here

ExitHere:
On Error Resume Next
Code that deletes the temp query
Exit Sub (or Function)

HandleErrors:
Select Case Err.Number
Case Else
MsgBox Err.Description & " (" & err.Number & ")"
End Select
Resume ExitHere

If it was mine then I'd use both the second and the third suggestions.

Hope this helps
 
copy this function to a module:

Function fnDeleteQueryDef(dbDAO As DAO.Database, strQryName As String) As
Boolean
On Error GoTo Err_Handler

Dim qrydef As DAO.QueryDef

fnDeleteQueryDef = False

For Each qrydef In dbDAO.QueryDefs
If qrydef.Name = strQryName Then
dbDAO.QueryDefs.Delete (strQryName)
fnDeleteQueryDef = True
Exit For
End If
Next

Exit_Routine:
Set qrydef = Nothing
Exit Function

Err_Handler:
'fnGenErrorHandler
Resume Exit_Routine

End Function

Now call this step in your code:
Dim SqlStr as string
SQLStr = "qry_tempqry"
bDelStat = fnDeleteQueryDef(DB, SQLStr)
----------------
 
Back
Top