Create List Of All Linked Tables With Their Record Counts
On a recent project, I had to do an "inventory" of all tables in a Microsoft SQL database which was linked to my Access application. This inventory included the table name and the number of records in each table. The code below shows how to create this list (as a table). Note that the RecordCount property doesn't work for linked tables. Below this code example is an example of a query that also counts records.
Program Code
' ***********************************************************************
' List All Tables Including Record Counts And Write To A Table
' ***********************************************************************
Public Function SelectAllLinkedCounts()
Dim db As DAO.Database
Dim recOut, rst As DAO.Recordset
Dim tbl As TableDef, fld As Field
Dim lngRecordCount As Long
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryDeleteTableNamesAll", acViewNormal, acEdit
DoCmd.SetWarnings True
' ***********************************************************************
' Open the Output File For Collecting Table Statistics
' ***********************************************************************
Set db = CurrentDb()
Set recOut = db.OpenRecordset("tblTableNamesAll")
' ***********************************************************************
' Loop Through All Tables Linked
' ***********************************************************************
For Each tbl In db.TableDefs
' ***********************************************************************
' Exclude System and Temporary Tables
' ***********************************************************************
If Left$(tbl.Name, 4) <> "MSys" And Left$(tbl.Name, 1) <> "~" Then
' ***********************************************************************
' Get The Record Count
' ***********************************************************************
On Error Resume Next
Set rst = CurrentDb.OpenRecordset("SELECT Count(*) AS Total FROM " & tbl.Name & ";")
' ***********************************************************************
' Log Tables With Connect Issues And Skip
' ***********************************************************************
If Err.Number <> 0 Then
Debug.Print tbl.Name
Err.Clear
GoTo ContinueTableScan
End If
' ***********************************************************************
' Write The Table Name and the Record Count
' ***********************************************************************
recOut.AddNew
recOut!TableName = tbl.Name
recOut!TableRecordCount = rst!Total
recOut.Update
' ***********************************************************************
' Clean Up and Close All Files Used To Access Table Information
' ***********************************************************************
rst.Close
Set rst = Nothing
End If
ContinueTableScan:
Next tbl
recOut.Close
Set recOut = Nothing
Set db = Nothing
End Function
Query Example Code
SELECT Count(*) AS AllRecordCount FROM dbo_GL30000 WHERE (((dbo_GL30000.TRXDATE)>=#1/1/1994# And (dbo_GL30000.TRXDATE)<#1/1/1995#));
Query Example Query Builder
