Delete table or query only if it exists

[MvP14]

I have a command that creates a table1. However, when I run it two times, I get an error message because table1 already exists. That's why I used the command DoCmd.DeleteObject "Table1" in the Err_Create_Table section. However, when there is no table1, I get an error message.

Is it possible to do the command only when the table exists by using If ... Then ...?

Thanks for any response!

 

[Mile-O]

Make a query based on the hidden system table: MSysObjects with your table's name as the criteria.

If the count of the query is 0 then the table does not exist - if it is 1 then the table does exist, if it's any other value then there's definitely something wrong.

 

[MvP14]

How do I do that?

 

[Mile-O]

There are a couple of ways to do it - personally, I'd use DAO like this:

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset("SELECT Name From MSysObjects WHERE Name = 'tblTest';")
    
    ' check if table exists: if 1, then it does, if 0 then it does not
    If rs.RecordCount = 1 Then
        ' your operation here
    Else
        ' your operation here
    End If

    rs.Close
    db.Close

The attachment shows the hidden tables with a query.

 

[MvP14]

You have done it yet again! Thank you!

 

[tembenite]

'Checking if table exists, if yes delete table
If ObjectExists(acTable, "tblSESSIONAVERAGE") Then DoCmd.DeleteObject acTable, "tblSESSIONAVERAGE"

Grabbed from
http://www.access-programmers.co.uk/forums/showthread.php?t=46716&highlight=if+table+exists

 

[modest]

Put this in a module:

Public Function QueryExists(queryName As String) As Boolean
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    
    Set db = CurrentDb
    QueryExists = False
    
    For Each qdf In db.QueryDefs
        If qdf.Name = queryName Then
            QueryExists = True
            Exit Function
        End If
    Next qdf
End Function

Call it with something like this from somewhere else:

    Dim queryName As String                         'query name
    queryName = "BlahBlah"                          'set the name
    
    
    If QueryExists(queryName) Then
        <if true do this>
        Else
            <if false do this>
    End If