Table Names in SQL Script

[henry.siemon]

I've just built a database by importing about 50 tables that have a
lot of data. In order to check that these tables were imported
correctly, I would like to write a SQL query that returns the TABLE
NAME and NUMBER OF ROWS in each table so that the query results will
look like this....

Table_Name Number_of_Rows

Table 1 4532
Table 2 24234
Table 3 234223
etc. etc.

Any idea how to accomplish this?

 

[Steve Sanford]

Hi henry,

There are many ways to do this. Here is my way.

Create a new standard module. Name it anything you want *except*
"TableRecordCount". Paste in the following code:

'-------------------------------
Sub TableRecordCount()

Dim db As DAO.Database
Dim r As DAO.Recordset
Dim t As DAO.Recordset
Dim strSQL As String

Set db = CurrentDb

Debug.Print " Table Name - Record Count in " & db.Name & ":"
Debug.Print "---------------------------------------"

strSQL = "SELECT MSysObjects.Name"
strSQL = strSQL & " FROM MsysObjects"
strSQL = strSQL & " WHERE (Left$([Name],1)<>'~') AND"
strSQL = strSQL & " (Left$([Name],4) <> 'Msys') AND"
strSQL = strSQL & " (MSysObjects.Type)=1"
strSQL = strSQL & " ORDER BY MSysObjects.Name;"

Set r = db.OpenRecordset(strSQL)
r.MoveFirst

Do While Not r.EOF
strSQL = "Select * FROM [" & r.Fields(0) & "];"
Set t = db.OpenRecordset(strSQL)
If Not t.BOF And Not t.EOF Then
t.MoveLast
Debug.Print r.Fields(0) & " - " & t.RecordCount
End If
t.Close
r.MoveNext
Loop

r.Close
Set r = Nothing
Set t = Nothing
Set db = Nothing

End Sub
'-------------------------------

Open the Immediatet window (control-G), click on any line in the sub, then
press the "F5" key to run the sub routine.


HTH

 

[Douglas J. Steele]

SELECT [Name] AS Table_Name, DCount("*", [Name]) AS Number_of_Rows
FROM MSysObjects
WHERE [Type] IN (1, 4, 6)
AND [Name] NOT LIKE "MSys*"
ORDER BY [Name]

 

[Steve Sanford]

Well, yeah, I guess that would work, Doug..... if you want to do it the
*easy* way!!!

One of these days the ol brain will kick in and I'll be able to think smart,
not hard (I hope)

Thanks for the lesson

--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

SELECT [Name] AS Table_Name, DCount("*", [Name]) AS Number_of_Rows
FROM MSysObjects
WHERE [Type] IN (1, 4, 6)
AND [Name] NOT LIKE "MSys*"
ORDER BY [Name]

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I've just built a database by importing about 50 tables that have a
lot of data. In order to check that these tables were imported
correctly, I would like to write a SQL query that returns the TABLE
NAME and NUMBER OF ROWS in each table so that the query results will
look like this....

Table_Name Number_of_Rows

Table 1 4532
Table 2 24234
Table 3 234223
etc. etc.

Any idea how to accomplish this?

 

[Albert D. Kallal]

SELECT [Name] AS Table_Name, DCount("*", [Name]) AS Number_of_Rows
FROM MSysObjects
WHERE [Type] IN (1, 4, 6)
AND [Name] NOT LIKE "MSys*"
ORDER BY [Name]

Wow...that is a simple and slick answer if you know how!!!

Thanks for sharing this one!!