MS Access VBA Function – Count Numbers of Records in Tables and list.

Not quite finished yet but place here for later correction.

Public Function CountAllTablesRows()

Dim rs As New ADODB.Recordset
Dim rsRC As New ADODB.Recordset
Dim strTbName As String
Dim lngRowCount As Long
Dim tbl As TableDef
CurrentProject.Connection.Execute "Delete from TABLE_INFO"
rs.Open "TABLE_INFO", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
For Each tbl In CurrentDb.TableDefs
Select Case Left(tbl.Name, 4)
Case "mSys"
Case Else
rs.AddNew
rsRC.Open "Select count(*) as The_Count from [" & tbl.Name & "]", CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
rs.Fields("TBL_NAME") = tbl.Name
rs.Fields("TBL_ROWCOUNT") = rsRC.Fields("The_Count")
rs.Update
rsRC.Close
Set rsRC = Nothing
'Debug.Print tbl.Name
End Select
Next
rs.Close
Set rs = Nothing

MsgBox "Counted Numbers in Table"

End Function

About Mark

Mark Brooks a forty something individual working and living in and around Edinburgh
This entry was posted in All, MS Access, VBA Code MS Access. Bookmark the permalink.