How to check whether a query already exist 1

[jane30]

Hi, in my Access 2000 database, I need to check whether a query already exist; If yes, delete; not, create a new one. Much thanks in advance.

 

[LarryDeLaruelle]

Jane:

This is the code to test for the presence of a query and if it exists to delete it. It call a function name ObjectExists (see below)

If ObjectExists(acQuery, "qbeQueryName&quot Then
DoCmd.DeleteObject acQuery, "qbeQueryName"
End If

Paste this code in a module and save it (I use modObjectExists):

Function ObjectExists(ObjType As Integer, ObjName As String) As Boolean
'Determines whether or not a given object exists in database
'If ObjectExists(acTable, "tblOrders&quot then ...

Dim strTemp As String
Dim db As Database
Dim strContainer As String

On Error Resume Next
Set db = CurrentDb()

Select Case ObjType
Case acTable
strTemp = db.TableDefs(ObjName).Name
ObjectExists = (Err.Number = 0)
Case acQuery
strTemp = db.QueryDefs(ObjName).Name
ObjectExists = (Err.Number = 0)
Case acMacro, acModule, acForm, acReport
Select Case ObjType
Case acMacro
strContainer = "Scripts"
Case acModule
strContainer = "Modules"
Case acForm
strContainer = "Forms"
Case acReport
strContainer = "Reports"
End Select
strTemp = db.Containers(strContainer).Documents(ObjName).Name
ObjectExists = (Err.Number = 0)
End Select

End Function

You can use it to test for the existence of any object type.

HTH



Larry De Laruelle
ldelaruelle@familychildrenscenter.org

 

[jane30]

exactly what i'm looking for. Thanks a lot.

 

[LarryDeLaruelle]

Jane:

My pleasure. Let me know if you have any questions/problems with the code.

Larry De Laruelle
ldelaruelle@familychildrenscenter.org

 

[kchernak]

Larry

I tried this code and get a run time 3265 error on the line " strTemp = db.QueryDefs(ObjName).Name" if the qry does not exist. Is it just me? I try exact code cut an paste.

thanks

 

[psemianonymous]

Let me highlight the important line:

Function ObjectExists(ObjType As Integer, ObjName As String) As Boolean
    'Determines whether or not a given object exists in database
    'If ObjectExists(acTable, "tblOrders") then ...
    
    Dim strTemp As String
    Dim db As Database
    Dim strContainer As String

On Error Resume Next

    Set db = CurrentDb()
    
    Select Case ObjType
        Case acTable
            strTemp = db.TableDefs(ObjName).Name
            ObjectExists = (Err.Number = 0)
        Case acQuery
            strTemp = db.QueryDefs(ObjName).Name
            ObjectExists = (Err.Number = 0)
        Case acMacro, acModule, acForm, acReport
            Select Case ObjType
                Case acMacro
                    strContainer = "Scripts"
                Case acModule
                    strContainer = "Modules"
                Case acForm
                    strContainer = "Forms"
                Case acReport
                    strContainer = "Reports"
            End Select
        strTemp = db.Containers(strContainer).Documents(ObjName).Name
        ObjectExists = (Err.Number = 0)
    End Select
    
End Function

The On Error Resume Next should be doing as it says...resuming to the next line. That's the basis for how this function works. If it isn't working for you...well, just make sure you haven't changed the error handling in any way.

 

[LarryDeLaruelle]

kchernak:

What version of Access are you using?

Also, what references do you have set? Error 3265 is "Item not found in this collection"; sounds like a missing refernece.

Let me know and we'll see if we can get it working for you.



Larry De Laruelle
ldelaruelle@familychildrenscenter.org

 

[TonyJollans]

Hi jane30,

I don't think you can have cut and pasted ALL of Larry's code. If the query doesn't exist you WILL get an error but the code picks it up and uses it to tell whether or not the Query exists.

Access 2000 makes this kind of thing a bit easier by providing collections of access objects - for queries, the AllQueries collection. You can run through the collection and check explicitly for your Query rather than assuming it doesn't exist when you get an error trying to reference it.

Function QueryExists(QueryName as String) As Boolean
    QueryExists = False
    Dim objQuery As AccessObject
    For Each objQuery In Application.CurrentData.AllQueries
        If objQuery.Name = QueryName Then QueryExists = True
    Next
End Function

Call this function like ..

If QueryExists(

QueryName

) Then ...

Enjoy,
Tony