tablesdefs.count is returning an incorrect value, way off!?

[xaviermobius]

I have a simple DB with 3 tables and wish to count the number of tables using VBA code.

Code as follows:

Private Sub Command0_Click()
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef

Set dbs = CurrentDb

Dim tablecount As Integer

tablecount = dbs.TableDefs.Count
MsgBox tablecount
End Sub

The msgbox is returning a value of 9!? If i add another table the value will increment by 1. Why is a value of 9 returned by the dbs.TableDefs.Count command when i only have 3 tables?

Any ideas....

 

[PeterF]

This is because you count the hidden system tables also, in a empty Access database there are 6.

You have to loop trough the table collection to find the correct count like this.

Private Sub Command0_Click()
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef

Set dbs = CurrentDb

Dim tablecount As Integer


For Each tdf In dbs.TableDefs
    If Not tdf.Name Like "ms*" Then
        Debug.Print tdf.Name
        tablecount = tablecount + 1
    End If
Next tdf

tablecount = dbs.TableDefs.Count
MsgBox tablecount
End Sub

 

[xaviermobius]

thank you so much, my progress bar works beautifully now!