Not quite finished yet but place here for later correction.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 | 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 |