Listing the number of records in each table of a database.

[Wrinkles of the Grey Matter]

Dear All,
Can someone guide me as to how I can write a piece of code which would
enable me to do the following:
1. Display the name of each table in the MDB.
2. Count the number of records in each table of the MDB.
3. Display the name and its corresponding number of records in two
different columns of a list box on a form.

I have been thinking on the lines of ADODX.catalog and
ADODX.table.Apparently, none of their properties allow me to use any form of
record count function. Following is the code which I have written to achieve
success with limited results:

========================================================
Option Compare Database
Option Explicit
------------------------------------------------------------------

Private Sub Form_Open(Cancel As Integer)
'Code written by Satadru Sengupta as on 23/09/2006.

'Declaring values to creating a new connection.
Dim cnxnMDB As ADODB.Connection

'Declaring the path string of the database.
Dim strMDB As String
strMDB = "D:\My Documents\Work\test.mdb"

'Declaring a catalog.
Dim catMDB As ADOX.Catalog

'Declaring table definition.
Dim tblMDB As ADOX.Table

'Setting up the database.
Set cnxnMDB = New ADODB.Connection

'Connecting to database.
cnxnMDB.Provider = "Microsoft.Jet.OLEDB.4.0"
cnxnMDB.Open strMDB

'Setting up the catalog
Set catMDB = New ADOX.Catalog
Set catMDB.ActiveConnection = cnxnMDB

'Loop through each of the tables in the active connection _
'to display its name and the number of records in a list box _
'on the form, named as "List0". Only user-defined tables _
'are considered. Not the Access system tables.

For Each tblMDB In catMDB.Tables
If tblMDB.Type = "TABLE" Then
List0.AddItem tblMDB.Name
End If
Next

'Close catalog.
Set catMDB.ActiveConnection = Nothing
Set catMDB = Nothing

'Close database connection.
cnxnMDB.Close
Set cnxnMDB = Nothing
End Sub
========================================================

In the instant case, I have made an attempt to populate the list box (List0)
in the form named "TestADO" with the names of each of the user-defined
tables in the database. Unfortunately, that is the best that I could do.

I could not populate the second column of the list box (List0) on the form
(TestADO) with the corresponding number of records in each table.

Can someone please help.

Thanks in advance,
Satadru

P.S.: I apologize if I have violated any prevalent practices by posting the
same question in two different Access newsgroups. But I did so in an attempt
to reach out to a wider knowledgebase.

 

[RoyVidar]

Dear All,
Can someone guide me as to how I can write a piece of code which
would
enable me to do the following:
1. Display the name of each table in the MDB.
2. Count the number of records in each table of the MDB.
3. Display the name and its corresponding number of records in two
different columns of a list box on a form.

I have been thinking on the lines of ADODX.catalog and
ADODX.table.Apparently, none of their properties allow me to use any
form of
record count function. Following is the code which I have written to
achieve
success with limited results:

========================================================
Option Compare Database
Option Explicit
------------------------------------------------------------------

Private Sub Form_Open(Cancel As Integer)
'Code written by Satadru Sengupta as on 23/09/2006.

'Declaring values to creating a new connection.
Dim cnxnMDB As ADODB.Connection

'Declaring the path string of the database.
Dim strMDB As String
strMDB = "D:\My Documents\Work\test.mdb"

'Declaring a catalog.
Dim catMDB As ADOX.Catalog

'Declaring table definition.
Dim tblMDB As ADOX.Table

'Setting up the database.
Set cnxnMDB = New ADODB.Connection

'Connecting to database.
cnxnMDB.Provider = "Microsoft.Jet.OLEDB.4.0"
cnxnMDB.Open strMDB

'Setting up the catalog
Set catMDB = New ADOX.Catalog
Set catMDB.ActiveConnection = cnxnMDB

'Loop through each of the tables in the active connection _
'to display its name and the number of records in a list box _
'on the form, named as "List0". Only user-defined tables _
'are considered. Not the Access system tables.

For Each tblMDB In catMDB.Tables
If tblMDB.Type = "TABLE" Then
List0.AddItem tblMDB.Name
End If
Next

'Close catalog.
Set catMDB.ActiveConnection = Nothing
Set catMDB = Nothing

'Close database connection.
cnxnMDB.Close
Set cnxnMDB = Nothing
End Sub
========================================================

In the instant case, I have made an attempt to populate the list box
(List0)
in the form named "TestADO" with the names of each of the
user-defined
tables in the database. Unfortunately, that is the best that I could
do.

I could not populate the second column of the list box (List0) on the
form
(TestADO) with the corresponding number of records in each table.

Can someone please help.

Thanks in advance,
Satadru

P.S.: I apologize if I have violated any prevalent practices by
posting the same question in two different Access newsgroups. But I
did so in an attempt to reach out to a wider knowledgebase.

I've used something like the below for something similar

Public Sub GetRecordCountADO(ByVal v_strDB As String)

Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection

Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & v_strDB

Set rs = cn.OpenSchema(adSchemaStatistics)
Do While Not rs.EOF
If StrComp(Left$(rs.Fields("TABLE_NAME").Value, 4), _
"msys", vbTextCompare) <> 0 Then
Debug.Print rs.Fields("TABLE_NAME").Value, _
rs.Fields("CARDINALITY").Value
End If
rs.MoveNext
Loop
cn.Close

End Sub

There are several ways of populating listboxes with such data, using
the .AddItem method, you could try concatenating the values, i e

Me!lstMyList.AddItem rs.Fields("TABLE_NAME").Value & ";" & _
rs.Fields("CARDINALITY").Value

The openshcema method is quite fast, but if you need completely
accurate count in a multiuser system where records are deleted and
added continuously, I'm not sure how often these statistics are
refreshed/updated, but it suited my purposes.

 

[Wrinkles of the Grey Matter]

Thanks Roy. I went carefully through your code and the MSDN help. It appears
that while one can obtain the tables, their types, views, fields/columns,
etc., there is no way to determine the number of records in each table.

Can you think of any other way to do that kind of action.

Sincerely,
Satadru

 

[Dirk Goldgar]

Dear All,
Can someone guide me as to how I can write a piece of code which would
enable me to do the following:
1. Display the name of each table in the MDB.
2. Count the number of records in each table of the MDB.
3. Display the name and its corresponding number of records in two
different columns of a list box on a form.

I have been thinking on the lines of ADODX.catalog and
ADODX.table.Apparently, none of their properties allow me to use any
form of record count function. Following is the code which I have
written to achieve success with limited results:

========================================================
Option Compare Database
Option Explicit
------------------------------------------------------------------

Private Sub Form_Open(Cancel As Integer)
'Code written by Satadru Sengupta as on 23/09/2006.

'Declaring values to creating a new connection.
Dim cnxnMDB As ADODB.Connection

'Declaring the path string of the database.
Dim strMDB As String
strMDB = "D:\My Documents\Work\test.mdb"

'Declaring a catalog.
Dim catMDB As ADOX.Catalog

'Declaring table definition.
Dim tblMDB As ADOX.Table

'Setting up the database.
Set cnxnMDB = New ADODB.Connection

'Connecting to database.
cnxnMDB.Provider = "Microsoft.Jet.OLEDB.4.0"
cnxnMDB.Open strMDB

'Setting up the catalog
Set catMDB = New ADOX.Catalog
Set catMDB.ActiveConnection = cnxnMDB

'Loop through each of the tables in the active connection _
'to display its name and the number of records in a list box _
'on the form, named as "List0". Only user-defined tables _
'are considered. Not the Access system tables.

For Each tblMDB In catMDB.Tables
If tblMDB.Type = "TABLE" Then
List0.AddItem tblMDB.Name
End If
Next

'Close catalog.
Set catMDB.ActiveConnection = Nothing
Set catMDB = Nothing

'Close database connection.
cnxnMDB.Close
Set cnxnMDB = Nothing
End Sub
========================================================

In the instant case, I have made an attempt to populate the list box
(List0) in the form named "TestADO" with the names of each of the
user-defined tables in the database. Unfortunately, that is the best
that I could do.

I could not populate the second column of the list box (List0) on the
form (TestADO) with the corresponding number of records in each table.

Can someone please help.

Thanks in advance,
Satadru

Here's DAO code to do it (requires that you have a reference set to the
DAO object library). Also, because you're using the list box's AddItem
method, you must be using one of the more recent versions of Access that
supports that method.

'------ start of code ------
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim lngRc As Long

Set db = CurrentDb
For Each tdf In db.TableDefs
If Left(tdf.Name, 4) <> "MSys" Then
If Len(tdf.Connect) = 0 Then
lngRc = tdf.RecordCount
Else
lngRc = DCount("*", tdf.Name)
End If
Me.List0.AddItem tdf.Name & ";" & lngRc
End If
Next tdf

Set db = Nothing

'------ end of code ------

P.S.: I apologize if I have violated any prevalent practices by
posting the same question in two different Access newsgroups. But I
did so in an attempt to reach out to a wider knowledgebase.

A better choice would have been to cross-post your message -- post the
same message two newsgroups at once -- rather than multipost -- post
multiple copies of the same message, each to a different newsgroup.
Most newsreaders allow you to cross-post by listing multiple groups on
the "Newsgroups:" or "To:" line. Microsoft's web-based newsreader lets
you do this also, but you have to click the "Advanced" button to do it.

 

[RoyVidar]

Thanks Roy. I went carefully through your code and the MSDN help. It
appears that while one can obtain the tables, their types, views,
fields/columns, etc., there is no way to determine the number of
records in each table.

Can you think of any other way to do that kind of action.

Sincerely,
Satadru

I'm sorry, you lost me there, didn't it work on your setup?

If it isn't accurate enough for you with regards to usage in
a multiuser environment, then in stead of using the CARDINALITY
(which is the recordcount), use a select count(*) on each of the
returned tables (content of returned TABLE_NAME field).

 

[Wrinkles of the Grey Matter]

Thanks Dirk. I actually had the part on cross-posting, figured out, only
after I had posted the messages.
While your DAO code is a welcome addition to the rather limited knowledge
that I have, I was looking to have a solution on the ADO lines.

Sincerely,
Satadru

 

[Wrinkles of the Grey Matter]

Thanks Roy. I shall have to try that out and get back to you against this
post. I am indeed working on a multi-user environment and this gets rather
tricky for me.

 

[Dirk Goldgar]

Thanks Dirk. I actually had the part on cross-posting, figured out,
only after I had posted the messages.
While your DAO code is a welcome addition to the rather limited
knowledge that I have, I was looking to have a solution on the ADO
lines.

What constraints are you operating under? If your code is running
against an .mdb in Access, as opposed to a VB or VBScript or ASP
environment, DAO will be more efficient than ADO. If you're running in
an ADP, you would probably use ADO, but you could also use an
"all-Access" solution (no reference to ADO *or* DAO required):

Dim ao As AccessObject

For Each ao In CurrentData.AllTables
If Left(ao.Name, 4) <> "MSys" Then
Me.List0.AddItem ao.Name & ";" & DCount("*", ao.Name)
End If
Next ao

If you're working outside the Access environment, but with an .mdb data
source, you can use DAO quite efficiently, using code similar to what I
originally posted, but without involving the DCount function:

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim rs As DAO.Recordset
Dim lngRc As Long

Set db = DBEngine.OpenDatabase("C:\Your Path\YourDatabase.mdb")

For Each tdf In db.TableDefs
If Left(tdf.Name, 4) <> "MSys" Then
If Len(tdf.Connect) = 0 Then
lngRc = tdf.RecordCount
Else
Set rs = db.OpenRecordset( _
"SELECT COUNT(*) FROM [" & tdf.Name & "]", _
dbOpenSnapshot)
lngRc = rs.Fields(0)
rs.Close
End If
Me.List0.AddItem tdf.Name & ";" & lngRc
End If
Next tdf

Actually, a variant of that would probably be more efficient tham using
the DCount function to get the count, even in an Access environment.

If you really need an all-ADO method, then the code Roy Vidar posted
works for me, with one catch: as far as I can tell, it only returns the
schema for the local tables actually stored in the .mdb file. It
doesn't return information for linked tables. So if you're running this
in a front-end application containing all or mostly linked tables,
you'll need to open a separate connection to the back-end in order to
read its schema.

 

[Granny Spitz via AccessMonster.com]

Can someone guide me as to how I can write a piece of code which would
enable me to do the following:
1. Display the name of each table in the MDB.
2. Count the number of records in each table of the MDB.
3. Display the name and its corresponding number of records in two
different columns of a list box on a form.

The easiest way to do this is not to write a VBA procedure, but to create a
query and use that as the Row Source for your list box. That way, whenever
the form is opened or the list box is scrolled, the record count is requeried
for each of the table names visible in the list box, so you can always see
the current record count if it's constantly changing due to other users'
database operations.

1) Create a new query and paste this into it:

SELECT [Name], DCount("*", [Name]) AS NumRecs
FROM MSysObjects
WHERE (([TYPE] IN (1, 6)) AND Left([Name], 4) <> "MSys")
ORDER BY [Name];

Save it as qryTableCounts.

2) Create a list box on your form. Set the Column Count at 2 and the Column
Width at 2.0";0.5" and make the list box wide enough to accommodate both
columns to avoid a horizontal scroll bar. Make the list box short enough so
that there's a vertical scroll bar. Set the Row Source as qryTableCounts.

3) If you want to periodically recheck the current record count for the
tables and don't want to scroll the list box manually, you can use the form's
timer or you can create a button that will requery the list box whenever it's
clicked:

Me!List0.Requery

P.S.: I apologize if I have violated any prevalent practices

Don't worry, hon. You'll catch on quickly to what's acceptable.

Besides what's already been mentioned, I'd add that if you plan to use these
newsgroups as the great resource they are, you should spend some time
reviewing the newsgroup archives in Google Groups:
http://groups.google.com/groups/dir?&sel=33606877&expand=1

There are a good number of experts who have posted many good answers, but
there's a small group who consistently gives great answers. Of that small
group, most of them post DAO code as the solution for working with Jet tables,
unless ADO code is specifically requested. So you can attribute that to the
fact that DAO is usually their first choice for Jet. Microsoft also
recommends that DAO be used on Jet tables, because DAO was specifically
designed for data access to Jet, while ADO was designed as a generic data
access library to many different data sources. And the two latest versions
of Access, Access 2003 and 2007 beta, use the DAO library as the default data
access library, not the ADODB library.

So deciding that your solution must only use the ADO and ADOX libraries
*before* you've received advice from experienced Access experts may not be in
your best interest.

That small group of experts I mentioned earlier that gives consistently great
answers? Dirk Goldgar is in it. If you see a thread with multiple replies
and Dirk's is one of them, chances are, Dirk's is the best answer. You can't
go wrong by following his advice.

Another word of advice, though it may seem off-topic. My family used to
travel a great distance to celebrate Christmas together nearly every year.
One year my youngest grand niece asked for a Barbie, which my family bought
for her. We went with the "snow ski" theme: skis, ski jacket, ski boots,
ski outfit, and snowmobile for Barbie. When my grand niece opened her gifts,
she was very disappointed that it didn't come with the Hawaiian bikini, skirt,
surfboard, etc., that her friends had. Since it would have been a great
inconvenience for the out-of-towners to exchange these gifts for a Hawaiian
Barbie, we told her, "Barbie can go to Hawaii next year! This year she's
going on a fabulous ski trip to the Swiss Alps to compete in the Winter
Olympics!" My grand niece was clearly unhappy with that idea. The next day
the boys built her a ski ramp. The children raced the Barbie and Ken dolls
and competed for the best and longest ski jumps, and then went sledding
themselves when their cousins arrived. The children had a ball. But you
know what happened the following year? My young grand niece's name got
crossed off the Christmas List for toys. She was the only child who didn't
get any toys for Christmas, just clothes and other things she needed.

So the next time someone gives you a free Barbie with skis, before you turn
up your nose because it wasn't *exactly* what you were expecting, please take
a closer look to see if it's as good as, or better than, your alternatives.

 

[Jamie Collins]

deciding that your solution must only use the ADO and ADOX
libraries *before* you've received advice from experienced Access
experts may not be in your best interest.

Do you also do relationship advice, granny? If I reject someone with a
bad reputation relating to a negative facet of their personality that
is (literally) a turn off for me (e.g. DAO demanding its objects be
explicitly destroyed, and in the correct order too, or it will do bad
things to me), how many relationship experts need I consult in order to
gain the respect of your everyday agony aunt to not retort, 'Why don't
you give DAO another chance...?'

Here's my advice: if someone specifies an explicit constraint (e.g.
must be ADO) then consider it a challenge on you to come up with a
proposed solution that works within the given constraint, rather than
try to convert them to another religion

I did like you 'no toys for ungrateful children' nugget, though <g>.

Jamie.

--

 

[Jamie Collins]

Create a new query and paste this into it:

SELECT [Name], DCount("*", [Name]) AS NumRecs
FROM MSysObjects
WHERE (([TYPE] IN (1, 6)) AND Left([Name], 4) <> "MSys")
ORDER BY [Name];

Can you please advise what permissions I require to read this table? I
can see the table (but not the columns) in the system catalog (via ADO)
but I cannot read it e.g.

CREATE PROCEDURE ProcTest AS
SELECT *
FROM MSysObjects
WITH OWNERACCESS OPTION;

fails with the error,

Record(s) cannot be read; no read permission on 'MSysObjects'

TIA,
Jamie.

--

 

[Granny Spitz via AccessMonster.com]

Can you please advise what permissions I require to read this table? I
can see the table (but not the columns) in the system catalog (via ADO)
but I cannot read it

Hon, thanks for providing an example of why ADO isn't always a good first
choice for data access on Jet tables. : ) Your code is creating two
connections to a database object in the default workspace, meaning you don't
have exclusive access to the database object containing that system table in
order to lock it.

You can use a query (eg, DAO QueryDef object) in the current database (as I
suggested), or you can use the DAO library in VBA code and create a separate
workspace object, then open the database object in that workspace and read
this system table.

This assumes you're using the default workgroup. By default, the Admin user
has read permissions on this system table within the current database object,
as long as this user has an exclusive lock. If you're using a secure
workgroup, then have your database administrator give your user account
appropriate permissions.

 

[Granny Spitz via AccessMonster.com]

Do you also do relationship advice, granny?

Why, yes, I do! Pick someone with a narcissistic personality. She'll never
notice your faults if you have any! : )

how many relationship experts need I consult in order to
gain the respect of ...?

Sorry, hon. Regardless of which data access library is your preference, if
you work with Access, you'll never gain the respect of others who think they
work with *real* databases.

Here's my advice: if someone specifies an explicit constraint (e.g.
must be ADO) then consider it a challenge on you to come up with a
proposed solution that works within the given constraint, rather than
try to convert them to another religion

Here's the reality: when answering questions, most people are under time
constraints. People will paste in existing code if their example might help
the questioner. If they have to write code, then the question is more likely
to be skipped. The more specific the requirements the questioner asks for,
the fewer responses he'll get. This thread is an example of this phenomenon:

How many working examples are ADO-specific? 1
How many working examples are DAO-specific? 2
How many working examples are non-library-specific? 2

So if we all followed your advice, only one working example would have been
provided and, as Dirk pointed out, extra work is required when linked tables
are involved, something that most of us are dealing with in a multiuser
environment. Those who are reading this thread wouldn't have been shown some
alternatives to the ADO solution and consider which one might be best for
*their* situation, be it faster to build, faster to execute, least work,
least complex, etc.

My point wasn't intended to convert his religion (to DAO), but to be open to
others' suggestions he hadn't considered at first, and to consider *why* some
of those suggestions might carry more weight. Not to mention the avoidance
of being crossed off of people's Christmas List because whatever they have to
offer would be deemed unacceptable, but I was trying to be subtle.

 

[Jamie Collins]

Regardless of which data access library is your preference, if
you work with Access, you'll never gain the respect of others who think they
work with *real* databases.

Of course, if you get into the habit of using ADO the eventual move to

Here's the reality: when answering questions, most people are under time
constraints. People will paste in existing code if their example might help
the questioner.
If they have to write code, then the question is more likely
to be skipped.

To the detriment of the poster, IMO. If they took the time to take up
the challenge they may learn something themselves. A win:win situation,
you could say.

The more specific the requirements the questioner asks for,
the fewer responses he'll get.

I'm more of a 'quality, not quantity' person myself.

Not to mention the avoidance
of being crossed off of people's Christmas List because whatever they have to
offer would be deemed unacceptable, but I was trying to be subtle.

My yuletide motivations differ from yours, it seems. I've found that a
homemade present, bespoke for the recipient, to be the most cherished.
On the other hand, santa in the department store has the gifts ready
wrapped in advance...

Jamie.

--

 

[Jamie Collins]

Your code is creating two
connections to a database object in the default workspace, meaning you don't
have exclusive access to the database object containing that system table in
order to lock it.

Thanks but I don't think that is a plausible explanation e.g. can you
execute a CREATE PROCEDURE in a workspace?

Anyhow, no worries: I simply fudged the connection string

Jamie.

--

 

[Granny Spitz via AccessMonster.com]

Thanks but I don't think that is a plausible explanation e.g. can you
execute a CREATE PROCEDURE in a workspace?

Hon, if you have Access open, the default workspace is open, even if the DAO
library isn't referenced. You can check for the two connections with the LDB
viewer while you're stepping through the code.

Anyhow, no worries: I simply fudged the connection string

Good for you! I'm glad you worked it out.

 

[Dirk Goldgar]

Thanks but I don't think that is a plausible explanation e.g. can you
execute a CREATE PROCEDURE in a workspace?

Anyhow, no worries: I simply fudged the connection string

Jamie, I'm a little curious as to what exactly you're doing that is
causing the error. I just tested the following code, and it worked
fine:

Dim sSQL As String

sSQL = _
"CREATE PROCEDURE ProcTest AS " & _
"SELECT * FROM MSysObjects " & _
"WITH OWNERACCESS OPTION;"

CurrentProject.Connection.Execute sSQL

I had thought, based on the preceding discussion, that I might need to
use CurrentProject.AccessConnection, but that seems not to be the case.

 

[Granny Spitz via AccessMonster.com]

Of course, if you get into the habit of using ADO the eventual move to
a *real* SQL DMBS will be a happier one <g>.

And if you get into the habit of using SQL instead of using VBA code to
cursor through ADO or DAO recordsets, the eventual move to a *real* RDBMS
will be even happier.

I'm more of a 'quality, not quantity' person myself.

With Hobson's choice, you have to hope it's quality.

My yuletide motivations differ from yours, it seems. I've found that a
homemade present, bespoke for the recipient, to be the most cherished.

Oh, I agree with you. If I could de-commercialize Christmas, I would. But
the kids are young enough that presents are extremely important to them and
their peers. The adults in my family would much rather enjoy each other's
company in fun activities than to spend money on gifts that will quickly be
forgotten.

 

[Jamie Collins]

Hon, if you have Access open, the default workspace is open

Who said I have Access open...?

Jamie.

--

 

[Jamie Collins]

Jamie, I'm a little curious as to what exactly you're doing that is
causing the error. I just tested the following code, and it worked
fine:

Dim sSQL As String

sSQL = _
"CREATE PROCEDURE ProcTest AS " & _
"SELECT * FROM MSysObjects " & _
"WITH OWNERACCESS OPTION;"

CurrentProject.Connection.Execute sSQL

No biggie: I wasn't using Access or ULS e.g. try this in Excel:

Sub test()
Dim cat
Set cat = CreateObject("ADOX.Catalog")
With cat
.Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\DropMe.mdb;"

Dim sSQL As String
sSQL = _
"CREATE PROCEDURE ProcTest AS " & _
"SELECT * FROM MSysObjects"

On Error Resume Next
.ActiveConnection.Execute sSQL
MsgBox Err.Description
On Error GoTo 0

End With
End Sub

Thanks,
Jamie.

--