As we all know, there are often numerous approaches to accomplish them same things. I was trying to help someone on a forum regarding the number of tables in their database returned by the TableDefs Collection vs. what the visually saw listed in the Nav Pane. This got me to perform a few tests and I discovered a little something which IMHO is a bug with the TableDefs Collection. At the very least it is something to simply be aware of.

What I did.
I created a new blank 2013 Access database. That’s it! Then I compared the following 2 functions results.

'Aproach 1 using TableDefs
'*******************************************************************
Function GetTableCount1() As Long
    Dim db                    As DAO.Database

    Set db = CurrentDb
    db.TableDefs.Refresh
    GetTableCount1 = db.TableDefs.Count
    Set db = Nothing
End Function

'Approach 2 using a MSysObjects Query
'*******************************************************************
Function GetTableCount2() As Long
    Dim db                    As DAO.Database
    Dim rs                    As DAO.Recordset
    Dim sSQL                  As String

    Set db = CurrentDb
    sSQL = "SELECT Count(MSysObjects.Name) AS NumberOfTables" & _
           " FROM MSysObjects" & _
           " GROUP BY MSysObjects.Type" & _
           " HAVING (MSysObjects.Type In (1,4,6))"
    Set rs = db.OpenRecordset(sSQL, dbOpenSnapshot)
    With rs
        If .RecordCount <> 0 Then
            GetTableCount2 = ![NumberOfTables]
        Else
            GetTableCount2 = 0
        End If
    End With
    rs.Close
    Set rs = Nothing
    Set db = Nothing
End Function

Now, GetTableCount1 returned a value of 11 while GetTableCount2 returned a value of 21. So which one was accurate I wondered? And what was the difference exactly?

So I set out to figure out this little mystery out of simply personal curiosty and created the following 2 function following suit with my original 2 functions.

'Aproach 1 using TableDefs
'*******************************************************************
Function EnumerateTables1()
    Dim db                    As DAO.Database
    Dim i                     As Long


    Set db = CurrentDb
    db.TableDefs.Refresh
    For i = 0 To db.TableDefs.Count - 1
        Debug.Print i + 1, db.TableDefs(i).Name
    Next i
    Set db = Nothing
End Function

'Approach 2 using a MSysObjects Query
'*******************************************************************
Function EnumerateTables2()
    Dim db                    As DAO.Database
    Dim rs                    As DAO.Recordset
    Dim sSQL                  As String
    Dim i                     As Long

    Set db = CurrentDb
    sSQL = "SELECT MSysObjects.Name" & _
           " FROM MSysObjects" & _
           " WHERE (MSysObjects.Type In (1,4,6))" & _
           " ORDER BY MSysObjects.Name;"
    Set rs = db.OpenRecordset(sSQL, dbOpenSnapshot)
    With rs
        If .RecordCount <> 0 Then
            Do While Not .EOF
                i = i + 1
                Debug.Print i, ![Name]
                .MoveNext
            Loop
        End If
    End With
    rs.Close
    Set rs = Nothing
    Set db = Nothing
End Function

and this is where the difference became apparent.

EnumerateTables1 returned the following

 1            MSysAccessStorage
 2            MSysACEs
 3            MSysComplexColumns
 4            MSysNavPaneGroupCategories
 5            MSysNavPaneGroups
 6            MSysNavPaneGroupToObjects
 7            MSysNavPaneObjectIDs
 8            MSysObjects
 9            MSysQueries
 10           MSysRelationships
 11           MSysResources

and EnumerateTables2 returned the following

 1            f_72DC15AD7C104BFE9ABC43CF6F539822_Data
 2            MSysAccessStorage
 3            MSysACEs
 4            MSysComplexColumns
 5            MSysComplexType_Attachment
 6            MSysComplexType_Decimal
 7            MSysComplexType_GUID
 8            MSysComplexType_IEEEDouble
 9            MSysComplexType_IEEESingle
 10           MSysComplexType_Long
 11           MSysComplexType_Short
 12           MSysComplexType_Text
 13           MSysComplexType_UnsignedByte
 14           MSysNavPaneGroupCategories
 15           MSysNavPaneGroups
 16           MSysNavPaneGroupToObjects
 17           MSysNavPaneObjectIDs
 18           MSysObjects
 19           MSysQueries
 20           MSysRelationships
 21           MSysResources

comparing the 2 sets of results, we can see the the TableDefs collection omits

              f_72DC15AD7C104BFE9ABC43CF6F539822_Data
              MSysComplexType_Attachment
              MSysComplexType_Decimal
              MSysComplexType_GUID
              MSysComplexType_IEEEDouble
              MSysComplexType_IEEESingle
              MSysComplexType_Long
              MSysComplexType_Short
              MSysComplexType_Text
              MSysComplexType_UnsignedByte

Now what are these tables exactly? I’m not sure and Google doesn’t appear to have much information on them.

Now, for most people, this discrepancy is of no importance since the difference is merely in MS Access hidden system tables, stuff that no one should be messing around with in any event. On the other hand, if the TableDefs collection counts some system tables, then why not these? This is why I personally consider it to be a bug, not an important one, but one none the less.

If you have any insight into this discrepancy please leave a comment.