Check if access table exists

Question

I want to log web site visits' IP, datetime, client and refferer data to access database but I'm planning to log every days log data in separate tables in example logs for 06.06.2010 will be logged in 2010_06_06 named table. When date is changed I'll create a table named 2010_06_07. But the problem is if this table is already created.

Any suggestions how to check if table exists in Access?

Wagram

You might want to consider using one table then using a parameterized query to generate your "daily" views. This will give you greater flexibility. For instance, what if you want to see results by the week? With a query, it is a simple date change for the parameters. If you had seven tables, you would have to physically create a union query with the specific tables. – Thornberry

Answer 1

You can use the hidden system table MSysObjects to check if a table exists:

If Not IsNull(DlookUp("Name","MSysObjects","Name='TableName'")) Then
    'Table Exists

However, I agree that it is a very bad idea to create a new table every day.

EDIT: I should add that tables have a type 1, 4 or 6 and it is possible for other objects of a different type to have the same name as a table, so it would be better to say:

If Not IsNull(DlookUp("Name","MSysObjects","Name='TableName' And Type In (1,4,6)")) Then
    'Table Exists

However, it is not possible to create a table with the same name as a query, so if you need a look up to test for a name, it may be best to add 5, that is query, to the Type list.

Lalittah

I expanded a little on the answer. – Lalittah

Thanks. It would be easier if there was a sql query for this but I think there is no such a thing :)( – Wagram

There is: SELECT [Name] FROM MSysObjects WHERE [Name]='TableName' And Type In (1,4,6) – Lalittah

Answer 2

Here's another solution, will be a bit faster than looping over all of the tables.

Public Function doesTableExist(strTableName As String) As Boolean
    Dim db As DAO.Database
    Dim td As DAO.TableDef
    Set db = CurrentDb
    On Error Resume Next
    Set td = db.TableDefs(strTableName)
    doesTableExist = (Err.Number = 0)
    Err.Clear
End Function

Distort

Sorry, is that an assignment of 0? doesTableExist = (Err.Number = 0) I am a bit confused what this line means – Gerome

The Err.Number = 0 is a comparison. It results in a boolean, which then gets assigned to doesTableExist. – Distort

Answer 3

I tested various methods for finding out if a table exists several years ago. Here is the code for all of them as I implemented, including my simple test routine.

    Public Function TableExists(strTableName As String, Optional ysnRefresh As Boolean, Optional db As DAO.Database) As Boolean
    ' Originally Based on Tony Toews function in TempTables.MDB, http://www.granite.ab.ca/access/temptables.htm
    ' Based on testing, when passed an existing database variable, this is the fastest
    On Error GoTo errHandler
      Dim tdf As DAO.TableDef
    
      If db Is Nothing Then Set db = CurrentDb()
      If ysnRefresh Then db.TableDefs.Refresh
      Set tdf = db(strTableName)
      TableExists = True
    
    exitRoutine:
      Set tdf = Nothing
      Exit Function
    
    errHandler:
      Select Case Err.Number
        Case 3265
          TableExists = False
        Case Else
          MsgBox Err.Number & ": " & Err.Description, vbCritical, "Error in mdlBackup.TableExists()"
      End Select
      Resume exitRoutine
    End Function
    
    Public Function TableExists2(strTableName As String, Optional ysnRefresh As Boolean, Optional db As DAO.Database) As Boolean
    On Error GoTo errHandler
      Dim bolCleanupDB As Boolean
      Dim tdf As DAO.TableDef
    
      If db Is Nothing Then
         Set db = CurrentDb()
         bolCleanupDB = True
      End If
      If ysnRefresh Then db.TableDefs.Refresh
      For Each tdf In db.TableDefs
        If tdf.name = strTableName Then
           TableExists2 = True
           Exit For
        End If
      Next tdf
    
    exitRoutine:
      Set tdf = Nothing
      If bolCleanupDB Then
         Set db = Nothing
      End If
      Exit Function
    
    errHandler:
      MsgBox Err.Number & ": " & Err.Description, vbCritical, "Error in mdlBackup.TableExists1()"
      Resume exitRoutine
    End Function
    
    Public Function TableExists3(strTableName As String, _
         Optional db As DAO.Database) As Boolean
    ' Based on testing, when NOT passed an existing database variable, this is the fastest
    On Error GoTo errHandler
      Dim strSQL As String
      Dim rs As DAO.Recordset
    
      If db Is Nothing Then Set db = CurrentDb()
      strSQL = "SELECT MSysObjects.Name FROM MSysObjects "
      strSQL = strSQL & "WHERE MSysObjects.Name=" & Chr(34) & strTableName & Chr(34)
      strSQL = strSQL & " AND MSysObjects.Type=6;"
      Set rs = db.OpenRecordset(strSQL)
      TableExists3 = (rs.RecordCount <> 0)
      
    exitRoutine:
      If Not (rs Is Nothing) Then
         rs.Close
         Set rs = Nothing
      End If
      Exit Function
    
    errHandler:
      MsgBox Err.Number & ": " & Err.Description, vbCritical, _
         "Error in TableExists1()"
      Resume exitRoutine
    End Function
    
    Public Sub TestTableExists(strTableName As String, intLoopCount As Integer)
      Dim dteStart As Date
      Dim i As Integer
      Dim bolResults As Boolean
      
      dteStart = Now()
      For i = 0 To intLoopCount
        bolResults = TableExists(strTableName, , CurrentDB())
      Next i
      Debug.Print "TableExists (" & intLoopCount & "): " & Format(Now() - dteStart, "nn:ss")
    
      dteStart = Now()
      For i = 0 To intLoopCount
        bolResults = TableExists2(strTableName, , CurrentDB())
      Next i
      Debug.Print "TableExists2 (" & intLoopCount & "): " & Format(Now() - dteStart, "nn:ss")
    
      dteStart = Now()
      For i = 0 To intLoopCount
        bolResults = TableExists3(strTableName, CurrentDB())
      Next i
      Debug.Print "TableExists3 (" & intLoopCount & "): " & Format(Now() - dteStart, "nn:ss")
    End Sub

Aimee

TableExists & TableExists2 work on (mysql odbc) linked tables; TableExists3 Does not however. – Interscholastic

Answer 4

I have found querying system tables or tabledefs to be unreliable and introduce unpredictable behaviour in scripts where tables get regularly created and dropped.

Based on my results, my hypothesis is that these tables aren't necessarily updated at the exact instant a CREATE or DROP is executed, or that concurrency issues are preventing me from getting an accurate result.

I've found the following method to be more reliable:

Public Function TableExists(theDatabase As Access.Application, _
    tableName As String) As Boolean

    ' Presume that table does not exist.
    TableExists = False

    ' Define iterator to query the object model.
    Dim iTable As Integer

    ' Loop through object catalogue and compare with search term.
    For iTable = 0 To theDatabase.CurrentData.AllTables.Count - 1
        If theDatabase.CurrentData.AllTables(iTable).Name = tableName Then
            TableExists = True
            Exit Function
        End If
    Next iTable

End Function

There should be no runtime issue iterating unless there is an staggeringly enormous collection of tables.

Sliding

Answer 5

This question is quite old but I found that no answer is satisfying, because:

• they do not handle the case of "bad" linked tables, where the linked table points to a non existing db or table.
• since linked tables are potentially huge, we must be able to check them with a fast query.

So here is my simple but more complete solution:

    Function isTableOk(tblName As String) As Boolean
    'works with local or linked tables
        Dim db As DAO.Database, rs As DAO.Recordset
        Dim sSql As String
        sSql = "SELECT TOP 1 ""xxx"" AS Expr1 FROM [" & tblName & "]"
        
        On Error Resume Next
        Err.Clear
        Set db = CurrentDb
        Set rs = db.OpenRecordset(sSql)
        isTableOk = (Err.Number = 0)
        rs.Close
    End Function

You can even check table in an external Access db with this version:

    Function isTableOk(tblName As String, Optional dbName As String) As Boolean
    'works with local or linked tables, or even tables in external db (if dbName is provided)
    
        Dim db As DAO.Database, rs As DAO.Recordset
        Dim sSql As String
        
        Set db = CurrentDb
        sSql = "SELECT TOP 1 'xxx' AS Expr1 FROM [" & tblName & "]"
        If Len(dbName) > 0 Then 'external db 
            sSql = sSql & " IN '" & dbName & "'"
        End If
        Err.Clear
        On Error Resume Next
        Set rs = db.OpenRecordset(sSql)
        isTableOk = (Err.Number = 0)
        rs.Close
    End Function

Tragopan

Sorry, what does the line isTableOk = (Err.Number = 0) mean? – Gerome

@Gerome - This is using the Err object (Error object) to check to see if an error occurred. If the number is more than zero, then something went wrong, and there's (technically) an issue with the table. – Addend

Answer 6

This one is the shortest solution you can have:

Public Function TableExists(ByVal Table As String) As Boolean
    On Error Resume Next
    TableExists = (DCount("*", Table) >= 0)
End Function

I've tested it regarding performance and it's way faster than looking for presence in the TableDefs or in the MySysObjects table. And it is dealing with non existing linked tables. If the table is declared but not reachable it will give back FALSE.

How it works? Well, if the table is present, the count of it's records will always be >= 0, so it will give back TRUE. If the table is not present or not reachable, the second line will trigger an error. Because of the error handling it will just jump to the next line which is End Function. Because FALSE is the default result of a boolean function if not set otherwise, the function result will be FALSE.

Hartsock

Answer 7

'OfficePaper.cc/functions/awa-cømezwe
'chernipeski*duck.com

Public Function IsTable( _
Optional ByVal LookingFor = 0, _
Optional ByVal IncludeLinked = False) _
As Boolean
    a = IIf(IncludeLinked, "1,6", "1")
    c = "Type In (" & a & ") AND Name='" & LookingFor & "'"
    r = DLookup("Name", "MSysObjects", c)
    r = Nz(r, "")
    IsTable = (LookingFor = r)
End Function

Irvingirwin

Answer 8

I realise this is a very old question, but none of the answers above really fitted my requirements.

Some utilised error intervention, which I dislike at the best of times. Some used a looping search that iterates through until the required table is either found or not found (potentially repeating for every table in TableDefs). Others used searches on the MSys tables (a fair way to do it, but using DLookup or DCount).

This version uses a binary chop, halving the amount of data that needs to be checked each time...

Public Function TableExists(tblName As String) As Boolean
    'Initialise...
    Dim cdb As DAO.Database: Set cdb = CurrentDb
    Dim s As Integer, e As Integer, o As Integer, p As Integer, x As Integer
    Dim running As Boolean, found As Boolean
    found = False: running = True
    x = 0
    'Set the start, end and pointer...
    s = 0: e = cdb.TableDefs.count - 1: o = -1: p = (e - s) / 2
    'Test whether we're still running...
    While running
        'Test the current pointer in the collection against the required...
        found = cdb.TableDefs(p).name = tblName
        'If it's not found then check whether required is before or after the pointer...
        s = (tblName >= cdb.TableDefs(p).name And p) Or _
            (tblName <  cdb.TableDefs(p).name And s)
        e = (tblName <= cdb.TableDefs(p).name And p) Or _
            (tblName >  cdb.TableDefs(p).name And e)
        'Relocate the pointer, taking into account odds/evens...
        p = (s + (e - s) / 2)
        p = p + -(s = e - 1 And o = p)
        'Determine whether we're still running...
        running = (x < cdb.TableDefs.count) And Not found And (o <> p)
        x = x + 1 'Debugging trap, just in case of infinite loops
        o = p
    Wend
    TableExists = found
End Function

I hate Access.

Addend