Function to Check If a Table Exists in Access Database

We can create a function to check whether a specific table exist in MS Access Database. There are at least two functions we can create to evaluate the existence of a table in Access, one for external database and the other for current database. Let's name isTableExist function for checking the existence of a table in external database and isTableExistCurrentDbs function for checking the existence of a table in current database.

Function to check if a table exist in Access external database

Function isTableExist(strTableName As String) As Boolean
  Dim tbl As DAO.TableDef
On Error GoTo Err_Msg
  
  isTableExist = False
  For Each tbl In daoDbs.TableDefs
    If tbl.Name = strTableName Then
      isTableExist = True
      Exit Function
    End If
  Next tbl
Exit_Function:
  Exit Function
Err_Msg:
  MsgBox "Function isTableExist, Error # " & str(Err.Number) & ", source: " & Err.Source & _
  Chr(13) & Err.description
  Resume Exit_Function
End Function

 

Implementation

To execute isTableExist function, at first, we must open a database. It is performing letDbsOpen function. After opening a database, we determine the table to be searched for. For example, there is a database that has been predetermined in the letDbsOpen function. We may run the function in Immediate Window as follows:

?letDbsOpen

Next, we determine the table name, say for example, a table named tblVoucherTemp as seen in the above figure. To check the existence of tblVoucherTemp, simply type the following code in Immediate Window:

?isTableExist("tblVoucherTemp")
True

The output is true

?isTableExist("tblVoucherTmp")
False

The above code produce False result, because no table named tblVoucherTmp exists.

 

Function to check if a table exist in Access current database

Function isTableExistCurrentDbs(strTableName As String) As Boolean
  Dim objTbl As Object
On Error GoTo Err_Msg
  
  isTableExistCurrentDbs = False
    
  For Each objTbl In Application.CurrentData.AllTables
    If objTbl.Name = strTableName Then
      isTableExistCurrentDbs = True
      Exit Function
    End If
  Next objTbl
Exit_Function:
  Exit Function
Err_Msg:
  MsgBox "Function isTableExistCurrentDbs, Error # " & str(Err.Number) & ", source: " & Err.Source & _
  Chr(13) & Err.description
  Resume Exit_Function
End Function

 

Implementation

To execute isTableExistCurrentDbs function, we do not have to perform letDbsOpen function. Using the current database, we determine the table to be searched for. For example, there is a table named tblTempVoucherTemp in the current database.

To check the existence of tblTempVoucherTemp, simply type the following code in Immediate Window:

?isTableExistCurrentDbs("tblTempVoucherTemp")
True

The output is true

?isTableExistCurrentDbs("tblTempVoucherTmp")
False

The above code produce False result, because no table named tblTempVoucherTmp exists.