Check if query exists

[Access9001]

How do I check if a query exists in a given db in Access VBA?

 

[SOS]

You can use this function if you want (put it in a Standard Module and name the module something like modFunctions):

Function QueryExists(strQueryName As String) As Boolean
    Dim db As DAO.Database
    Dim tdf As DAO.QueryDef
    
    On Error GoTo err_handler
    Set db = CurrentDb
    Set tdf = db.QueryDefs(strQueryName)
    
    QueryExists = True
    
QueryExists_Exit:
    Exit Function
    
err_handler:
    Select Case Err.Number
    Case 3265
        QueryExists = False
        Resume QueryExists_Exit
    Case Else
        MsgBox Err.Description, vbExclamation, "Error #: " & Err.Number
        Resume QueryExists_Exit
    End Select
End Function

and then you can call it like:

If QueryExists("Query1") = False Then
...
End If

 

[Access9001]

Is there a way to pass in the database path?

 

[SOS]

Change this:

Set db = CurrentDb

To this:

Set db = OpenDatabase("PathAndDatabaseNameHere")

 

[Access9001]

Works beautifully -- thanks!

 

[SOS]

You are welcome.

 

[poduska]

Perfect solution
Thanks

 

[globu86]

There's a drawback though to your solution: it leaves an error in the DBEngine.Errors collection. AFAIK this error is persistent (unless there's another error) until closing de DB.
To avoid this problem, I'm using this function:

Function QueryExists(strQueryName As String) As Boolean
Dim qdfLoop As DAO.QueryDef

For Each qdfLoop In CurrentDb.QueryDefs
If strQueryName = qdfLoop.Name Then
'Debug.Print Now, "[QueryExists] Query '" & strQueryName & "' exists"
QueryExists = True
Exit For
End If
Next qdfLoop
End Function

 

[theDBguy]

There's a drawback though to your solution: it leaves an error in the DBEngine.Errors collection. AFAIK this error is persistent (unless there's another error) until closing de DB.
To avoid this problem, I'm using this function:

Hi @globu86. Welcome to AWF!

Thanks for sharing your solution. Just FYI though, this is a 20-yr old thread. But, we still appreciate your contribution. Cheers!

 

[Pat Hartman]

You can also link to the MSysObjects table in the other database and just use a query to determine if the query exists.

 

[MarkK]

To elaborate on what Pat is saying, objects that exist in an Access database are listed in a hidden table called MSysObjects, and you can query that table. Queries have a type of 5, so you can write code like...

Sub Testing123()
    Debug.Print QueryExistsInDb("C:\PathToYour\Database.accdb", "Query1")
End Sub

Private Function QueryExistsInDb(DbPath As String, QueryName As String) As Boolean
    Const sql As String = _
        "SELECT Count(*) FROM MSysObjects IN '{0}' WHERE Name = p0 and Type = 5 "
    
    With CurrentDb.CreateQueryDef("", replace(sql, "{0}", DbPath))
        .Parameters(0) = QueryName
        With .OpenRecordset
            If Not .EOF Then QueryExistsInDb = .Fields(0).Value
        End With
    End With
End Function

... to find out of a particular query exists in a particular database.