You'll have to either run a SQL query, or use DCount.
For Each tblMDB In catMDB.Tables
If tblMDB.Type = "TABLE" Then
List0.AddItem tblMDB.Name & ";" & DCount("*", tblMDB.Name)
End If
Next
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)
"Wrinkles of the Grey Matter" <developer.programmer[ at ]gmail.com> wrote in
message news:ultks%2383GHA.1588[ at ]TK2MSFTNGP02.phx.gbl...
[Quoted Text] > 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
>
>
>
|