Tables:Checking to See if a Table Exists

There are two ways of doing this covered in this web page. One is by a function typed in a module, a method almost identical to the method covered on my web page dealing with detecting if a query exists. The other (original) method is by error-trapping.

"Function" Method

1. Create a new module
2. Enter the following code into the module:

   
   Public Function DoesTableExist(strName As String) As Boolean
       Dim loDb As Database
       Dim loQdf As TableDef
       On Error Resume Next
       Set loDb = CurrentDb
       Set loQdf = loDb.TableDefs(strName)
       DoesTableExist = Not CBool(Err)
       Err.Clear
   End Function
   

3. Save and close the module. Name it something like basDoesTableExist
4. The code which would perform the actual checking (again, almost identical to the "does query exist" code) is:

   
   If DoesTableExist("nameoftable") = True Then
   ' Code to execute if table DOES exist 
   Else
   ' Code to execute if table does NOT exist
   End If
   

   ---

   Error-Trapping Method

   The other (original) way to check and see if a table exists is by a method the newsgroups referred to as error-trapping--attempting to perform an operation on a table. If that table doesn't exist, it creates an error message. Code designed to error-trap can then take over, under the assumption that the error occurred due to the table not existing when the operation was attempted.

   Here is some sample code for doing this:

   On Error Resume Next Dim tdz As TableDef Set tdz = CurrentDb.TableDefs("tblTempPOs") ' Replace "tblTempPOs" with the name of the table you're looking for If Err Then ' Enter the code you want to occur if the tables do NOT exist Else 'Enter the code you want to execute if the tables DO EXIST End If