How to check if a query exists and if it finds to delete

[Jack]

Hi
I have the following code:
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strsql As String
Set db = CurrentDb



Set qdf = db.CreateQueryDef("test", sql)

DoCmd.OpenQuery "test"

Before the Set qdf statement I need to check if the query test exists. If it
does I need to delete it.

How does one programmatically do this. Thanks.

 

[Jack Leach]

I would write a little function to check the QueryDefs collection (I assume
this will work, haven't tried it though)


Public Function fQueryExists(QryName As String) As Boolean
Dim qd As QueryDef
For Each qd In CurrentDB.QueryDefs
If qd.Name = QryName Then
fQueryExists = True
Exit Function
End If
Next
End Function


then you can check from your procedure:

If Not fQueryExists("testqry") Then
Goto Exit_Procedure
End If

Set qdf = Currentdb.....


hth

--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

 

[PieterLinden via AccessMonster.com]

Hi
I have the following code:
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strsql As String
Set db = CurrentDb

Set qdf = db.CreateQueryDef("test", sql)

DoCmd.OpenQuery "test"

Before the Set qdf statement I need to check if the query test exists. If it
does I need to delete it.

How does one programmatically do this. Thanks.

If the query does not exist, the delete command will throw an error.

This should work:
Public Sub DeleteQueryDef()
On Error GoTo ErrHandler
DoCmd.DeleteObject acQuery, "QueryThatDoesNotExist"
Exit Sub
ErrHandler:
If Err.Number = 7874 Then
Err.Clear
Exit Sub
End If
End Sub

 

[Dirk Goldgar]

Hi
I have the following code:
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strsql As String
Set db = CurrentDb



Set qdf = db.CreateQueryDef("test", sql)

DoCmd.OpenQuery "test"

Before the Set qdf statement I need to check if the query test exists. If
it
does I need to delete it.

How does one programmatically do this. Thanks.

And yet another take on this: if the querydef exists, don't delete it, just
change its SQL property:

'----- start of code -----
On Error Resume Next
Set qdf = db.QueryDefs("test")
Select Case Err.Number
Case 0 ' the query already exists, so change it
qdf.SQL = strsql
Set qdf = Nothing
Case 3265 ' the query doesn't exist, so create it
db.CreateQueryDef "test", strsql
Case Else
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
End Select
On Error GoTo 0 ' or your error-handler
'----- end of code -----

 

[Jack]

Thanks everybody for the generous help. I appreciate it so much.
Regards.