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.

 

[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.

 

[Dirk Goldgar]

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

It's something missing from the connection string. I tested your code
from Excel, and got the result you reported. I fiddled around to
simplify it, and got to the point where I'm running from within Access
against the same database I'm running in. (I have to close and reopen
the database after making code changes, so as not to have an exclusive
lock on the database.)

Working against the current database, I tried code like this:

'----- start of code -----
Dim conn

Set conn = CreateObject("ADODB.Connection")

conn.Open CurrentProject.Connection.ConnectionString

On Error Resume Next

conn.Execute _
"CREATE PROCEDURE ProcTestSys AS " & _
"SELECT * FROM MSysObjects"

Debug.Print Err.Number, Err.Description

conn.Close

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

That code works fine, has no error, and creates the query.

If I change ...

conn.Open CurrentProject.Connection.ConnectionString

to ...

conn.Open _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & CurrentDb.Name & ";"

It does *not* work, and gives me the "no read permissions" error.

I suspect it has to do with being logged into the correct .mdw as a user
in the Admins group, but I haven't verified this. Note that this is a
database that has no user-level security applied, but all that really
means (in a practical sense) is that the default system.mdw is used and
the admin user has no password.