May 23, 2008 at 12:45 pm
I would like to cycle through 25 tables and count the numbers of rows in each table. The objective is to create a table which lists columns for TABLE NAME and # OF ROWS PER TABLE for all table in then Access Database. I appreciate any help, thanks!
June 3, 2008 at 2:52 am
If your 25 table names are fixed, then create a query in SQL view and
SELECT Count(*) AS MyRowCount
FROM [tablename];
to get the total number of records. Then insert a row into your results table. Repeat for each table.
If you want to find the table names in a query so you can loop through them, ensure you have hidden and system objects displayed (its a setting in Tools-Options) then look in table MSysObjects. In pseudo code, I think you probably want to select the Name field where Type = 1 and Name not like Msys*
kind regards
Paul..
June 4, 2008 at 2:13 am
Assuming your table names are relatively constant, stick them in a table - tResults with fields tablename text, count integer or long integer, updated datetime. Create a function like
Public Function Update()
Dim rs As New ADODB.Recordset
rs.Open "tResults", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
rs.MoveFirst
Do While Not rs.EOF
rs("Count") = DCount("*", rs("Tablename"))
rs("Updated") = Now()
rs.Update
Loop
rs.Close
End Function
and you can call this from a macro.
June 4, 2008 at 3:58 am
Hello,
This should do what you want.
Run This once to create the table which will store the data
DoCmd.RunSQL ("CREATE TABLE TBL_TABLEINFO (TABLENAME Text(30) NOT NULL, TABLEROWCOUNT Int NOT NULL," _
& "APPENDDATE DateTime NOT NULL, Constraint R_PK Primary Key(TABLENAME,APPENDDATE))")
Create a button on a form call the button CMD_RowCounter.
Add this code to the onclick event of the button.
Private Sub CMD_RowCounter_Click()
Dim Dbs As Database
Dim Tdf As TableDef
Dim Tdfs As TableDefs
Dim StrQry As String
On Error GoTo ErrExit
' create database and table definitions objects
Set Dbs = CurrentDb
Set Tdfs = Dbs.TableDefs
'Loop through the tables collection
For Each Tdf In Tdfs
' Ignore system and temp tables
If Left(Tdf.Name, 4) <> "Msys" And Left(Tdf.Name, 1) <> "~" Then
' Insert table name, row count and datetime into the table
'Having a date column will allow you to track the changes in the size of the tables over time
StrQry = "INSERT INTO TBL_TABLEINFO (TABLENAME, TABLEROWCOUNT, APPENDDATE ) SELECT '" & Tdf.Name & _
"' as TABLENAME, Count(*)AS TABLEROWCOUNT ,#" & Now & "# AS APPENDDATE FROM [" & Tdf.Name & "]"
' Switch off append record warnings
DoCmd.SetWarnings False
'Execute the sql
DoCmd.RunSQL StrQry
DoCmd.SetWarnings True
End If
Next 'Goto next table
MsgBox "Processing Complete", vbInformation, "Row Counter"
'Clean up
Set Tdfs = Nothing
Set Tdf = Nothing
Dbs.Close
Set Dbs = Nothing
Exit Sub
ErrExit:
' inform the user of errors and clean up
MsgBox "Processing Failed with errors: " & Err.Number & vbCrLf & Err.Description, vbCritical, "Row Counter"
Err.Clear
DoCmd.SetWarnings True
Set Tdfs = Nothing
Set Tdf = Nothing
Dbs.Close
Set Dbs = Nothing
End Sub
Hope that helps
K.
June 5, 2008 at 1:07 pm
Create a new table named tblTables_RecordCounts with fields TableName (text,64) and NumOfRecords (long integer) and then run this:
Sub GetRecordCounts()
Dim db As DAO.Database, tdf As DAO.TableDef, rs As DAO.Recordset
'
Set db = CurrentDb
Set rs = db.OpenRecordset("tblTables_RecordCounts")
For Each tdf In db.TableDefs
If left(tdf.Name, 4) <> "MSys" Then
rs.AddNew
rs!TableName = tdf.Name
rs!NumOfRecords = tdf.RecordCount
rs.Update
End If
Next tdf
rs.Close
Set rs = Nothing
Set db = Nothing
End Sub
June 6, 2008 at 6:21 am
I append row counts to a table every night with a procedure that uses the following code (which I got from here):
SELECT o.nameas [TableName]
,i.rowsas [RowCount]
FROM sysobjects o
INNER JOIN sysindexes i
ON (o.id = i.id)
WHERE o.xtype = 'u'
AND i.indid < 2
The good news is that it runs blindingly fast (as compared to select count(*) statements.) The bad news, re sysindexes, from BOL is:
Important:
This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead. To find the equivalent system view or views, see Mapping System Tables to System Views.
I have not yet found a current SQL Server system view that contains the "rows" column, but I seem to be OK until my next Sql Server update.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply