Creating a table that holds record counts for a number of tables

thedawn

Registered User.
Local time
Today, 23:32
Joined
Mar 29, 2010
Messages
30
Hi

Is there a way of counting the records in a number of tables and then inserting the table names and counts into a table. I have a table with the names of all the tables in it as this is populated when I import from excel.

The resulting table would be

Name Count
Table1 25
Table2 35

etc

I am using Access 2010

Thanks for your help

Rich
 
Paste the following in the SQL View of a new query:
Code:
SELECT MSysObjects.Name
FROM MSysObjects
WHERE (((MSysObjects.Type)=1))
ORDER BY MSysObjects.Name;
So you can see that it doesn't need to be inserted into a new table if you can just retrieve it as a query when needed.

To count the number of records in each table, simply add a DCount() function to the above, or use a subquery.
 
Thanks for that but if possible I only wanted to do it for a specific set of company tables - is there a way of doing this

Thanks

Rich
 
I have another table called TableofTables that lists all of the tables that I need.

I have an automated import that creates a table, imports data from excel and inserts the table name into this table every time I import a new dataset.

Thanks

Rich
 
Like I mentioned earlier, you don't need to create a table just for listing table names if the schema already exist in Access. How do you identify which tables you want to list from their name? Do they all have a particular name?
 
Sorry, i thought it was in the previous message. I have a table called "Table of Tables' that has the name of each of the tables I want to count in it

Thanks

Rich
 
If you already have the tables listed there, you don't need the query I gave you earlier. Create a query based on that table and include a DCount() function to count the number of records in each form. E.g.:
Code:
DCount("*", [TableOfTables].[TableName])
 
I tried to solve this another way using VBA as I am struggling with the dcount for some reason.

I put the following together using code I had used to run multiple append queries but I keep getting the following error

'Wrong number of Arguments or invalid property assignment'

I am not sure what I have done wrong can anyone see ?

Code:
Dim db As DAO.Database
Dim rs As DAO.Recordset

Const cstrone As String = "INSERT INTO tblNumbersCheck (Tablename, [Count]) "
Const cstrtwo As String = "SELECT TableofTables.Tablename, Count("
Const cstrMidpoint As String = ".Identifier) AS "
Const cstrSuffix As String = " FROM TableofTables, "
Const cstrEnd As String = " WHERE (((TableofTables.Tablename) = '"
Const cstrEnd2 As String = "')) GROUP BY TableofTables.Tablename"
   
   Set db = CurrentDb()
   Set rs = db.OpenRecordset("TableofTables", dbOpenDynaset)

   Do Until rs.EOF
      db.Execute cstrone, cstrtwo & rs!tablename & cstrMidpoint & rs!tablename, cstrSuffix, rs!tablename, cstrEnd, rs!tablename, cstrEnd2, dbFailOnError
      rs.MoveNext
   Loop

   rs.Close: Set rs = Nothing
   Set db = Nothing

Thanks

Rich
 
You're overcomplicating things. If you're having a problem with something that we proposed, tell us what the problem is and we'll fix it.

What problems were you having with the DCount() function? And how exactly did you write it?
 

Users who are viewing this thread

Back
Top Bottom