Check if Table Exists...

Here are two different Functions to check if a Table exists.  Can come in handy if you want to check for a Table prior to importing into it.
The difficult I do immediately, the impossible takes a little bit longer.
Function fDoesTableExist(strTableName As String) As Boolean
On Error GoTo errHandler
    Dim db As DAO.Database
    Dim tdf As TableDef
    fDoesTableExist = False
    Set db = CurrentDb()
    For Each tdf In db.TableDefs
       If tdf.Name = strTableName Then
         Set db = Nothing
         fDoesTableExist = True
         Exit Function
       End If
    Next tdf
    Set db = Nothing
  Set tdf = Nothing
  Exit Function
  Select Case Err.Number
    Case 3265
      fDoesTableExist = False
    Case Else
      MsgBox Err.Number & ": " & Err.Description, vbCritical, "Error in fDoesTableExist()"
  End Select
  Resume exitRoutine
End Function
Tips (Main)
Creating a Multi-Value field using Alphabet
Copy Fields Down from above Record
Loop thru records and OutPutTo seperate .RTF or .PDF
Modified Spell Check
Code Snippets
Lock\Unlock Bound Controls
Loop while renumbering two columns
Create a Table with Dynamic Field Names
Snippets for Exporting to Excel
Log Field Changes
Log Record Deletions
Check for Duplicate Values
Selecting an Excel Worksheet from Access
Send eMail to Multiple Recipients
Cancel Save in a Bound Form
Automatically Send eMail Notifications
Looping Records to Send eMail
Insert (or Remove) Blank Line
Function fTableExists(strTableName As String, Optional db As DAO.Database) As Boolean
On Error GoTo errHandler
'6.7.2010 David Fenton
'Based on testing, when NOT passed an existing database variable, this is the fastest
      Dim strSQL As String
      Dim rs As DAO.Recordset
      If db Is Nothing Then Set db = CurrentDb()
      strSQL = "SELECT MSysObjects.Name FROM MSysObjects " & _
                "WHERE MSysObjects.Name=" & Chr(34) & strTableName & Chr(34) & "" & _
                    "AND MSysObjects.Type=6"
      Set rs = db.OpenRecordset(strSQL)
      fTableExists = (rs.RecordCount <> 0)
  If Not (rs Is Nothing) Then
     Set rs = Nothing
  End If
  Exit Function
  MsgBox Err.Number & ": " & Err.Description, vbCritical, "Error in fTableExists()"
  Resume exitRoutine
End Function
This site uses cookies to collect data on usage. By continuing to browse this site you consent to this policy. Find out more here.