Tuesday, September 9, 2008

Getting the column and row count of all tables in an MS Access database via VBA module

For Each tempTable In CurrentDb.TableDefs
'for each table in the database

If tempTable.Name <> "MSysAccessObjects" _
And tempTable.Name <> "MSysAccessXML" _
And tempTable.Name <> "MSysACEs" _
And tempTable.Name <> "MSysObjects" _
And tempTable.Name <> "MSysQueries" _
And tempTable.Name <> "MSysRelationships" _
Then 'Avoid system tables

ColumnCount = CurrentDb.TableDefs(tempTable.Name).Fields.Count
' get the column count

RowCount = CurrentDb.TableDefs(tempTable.Name).RecordCount
'get the row count

MsgBox(tempTable.Name + vbTab + Str(ColumnCount) + _
vbTab + Str(RowCount))

End If

Next
'end of each table loop

No comments: