Determine if a table exist

rdfernandez · March 25th, 2005, 10:37 AM

Hi! I am creating a database that needs to determine if a certain table already exist or not. How do I do this?

Thank you.

BrianWren · March 25th, 2005, 12:52 PM

You can select the table, and see if that results in an error (using the VBA methods to select an item in the database window), or you can loop through the TableDefs collection, checking the name for each item against the name that you are after.

rjweers · March 25th, 2005, 09:12 PM

Try something like this:

Code:

If CurrentDb().TableDefs(strTableName).Name = strTableName Then
    ' nop - just doing this if to see if Err 3265 occurs
End If
If Err.Number = 0 then
    ' table exists
ElseIf Err.Number = 3265 Then ' "Item Not Found in the collection"
    ' table does not exist
    Err.Clear
    Debug.Print "table does not exist"
Else
    ' unhandled error
    Err.Raise
End If

Randall J Weers
Membership Vice President
Pacific NorthWest Access Developers Group
http://www.pnwadg.org

Ray Pinnegar · March 27th, 2005, 06:53 AM

Try the following function - hope it helps

Function Gen_test_if_table_exists(TableName) As Boolean

   Dim tdfLoop
   Gen_test_if_table_exists = False
   With CurrentDb
        'Debug.Print .TableDefs.Count & " TableDefs in " & .Name

        ' Enumerate TableDefs collection.
      For Each tdfLoop In .TableDefs
           'Debug.Print " " & tdfLoop.Name
         If TableName = tdfLoop.Name Then
            Gen_test_if_table_exists = True
            Exit Function
         End If
      Next tdfLoop

    End With

End Function

Cheers Ray

Cheers Ray

rdfernandez · March 30th, 2005, 04:40 AM

Hi guys! Thanks for the help.
I will try it out.

Have a nice day.