Check to see if Query Exists

[RainX]

Hi all,

Basically what i wanted to do was see if a query by a particular name exists and if it does than delete it

heres what i have right now

   With db
    .QueryDefs.Delete "Dynamic_Query"
    Set qdfNew = .CreateQueryDef("Dynamic_Query", strSQL)
    .Close
    End With

How would i change it so
.QueryDefs.Delete "Dynamic_Query" would only be executed if it exists or instead just have it overwrite it?

Thanks alot

 

[RainX]

Okay i figured it out.

I'll post it here so if anyone needs it they can use it

   With db
    If QueryExists(strQuery) Then
    .QueryDefs.Delete strQuery
    End If
    Set qdfNew = .CreateQueryDef(strQuery, strSQL)
    .Close
   End With


Function QueryExists(Namex As String) As Boolean
  on error resume next
  QueryExists = not (CurrentDb.QueryDefs(Namex) is nothing)   
end function

Take care!

 

[ghudson]

This will do it...

    Dim qryDef As QueryDef
    
    For Each qryDef In CurrentDb.QueryDefs
        If InStr(1, qryDef.Name, "YourQueryNameHere") > 0 Then
            MsgBox "query exists" 'remove this after testing
            DoCmd.SelectObject acQuery, qryDef.Name, True
            DoCmd.DeleteObject acQuery, qryDef.Name
        Else
            MsgBox "query does not exist" 'remove this after testing
        End If

    Next qryDef

 

[Cosmos75]

Here's a method from a KB article
ACC2000: Sample Code to Check for Table or Query in a Database