Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

How to Count Tables in a MDB?

Posted on 2003-02-28
8
Medium Priority
?
362 Views
Last Modified: 2012-06-27
I would like access an external database and get a count of the tables and display the number of tables in a text box on a form.  Like this...
 
Total Pets in Pets Database:  4 <-- (in textbox)

I have a code that I am trying to use but it is not working.  It displays the name of the tables and the count of each record in the table. The ugly part is that it also captures the Access SYS Tables too.

The SYS tables throw off my count. How do it hide it from the count?  

All I want is a total count of the tables created by people and NOT all the hidden SYS tables or names or number of records.

Present code...

Sub Alltables()

    Dim SourceFile
    Dim tdf As TableDef
    SourceFile = "C:\Pets\Pets.mdb"
    Dim dbPETSdb As DAO.Database
    Set dbPETSdb = DBEngine.Workspaces(0).Databases(0)

Set dbPETSdb = OpenDatabase(SourceFile)
   
     For Each tdf In dbPETSdb.TableDefs
     MsgBox tdf.Name & "-" & tdf.RecordCount    //This is my problem area.
     Debug.Print
     editbox1 = tdf.count ?????
  Next tdf
End Sub



Maybe SQL Table count is better?

joey
0
Comment
Question by:joeyz
  • 3
  • 3
  • 2
8 Comments
 
LVL 41

Accepted Solution

by:
shanesuebsahakarn earned 75 total points
ID: 8046095
   For Each tdf In dbPETSdb.TableDefs
       If Left$(tdf.Name,4)<>"MSys" Then
          MsgBox tdf.Name & "-" & tdf.RecordCount    //This is my problem area.
       End If
    Next
0
 

Author Comment

by:joeyz
ID: 8046128
Wow!

Thanks shane

Fast reponse, so cool!

Okay, I see the line of code to hide the SYS files.

But how do I get the count of tables to display into an edit box on a form?

That msg line only gives me a popup and displays names of tables one by one and record count.

I i want is a total count of the tables and to display that one value in a edit or text box :)
0
 

Author Comment

by:joeyz
ID: 8046151
Could I possible use this....

 Editbox1 = tdf.NameCount

in order the program to count the names of the tables and display the total of tables?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:joeyz
ID: 8046162
Could I possible use this....

 Editbox1 = tdf.NameCount

in order the program to count the names of the tables and display the total of tables?
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 8047341
To retrieve the total count of tables, you could do something like this:
   I=0
   For Each tdf In dbPETSdb.TableDefs
      If Left$(tdf.Name,4)<>"MSys" Then
         I=I+1
      End If
   Next
   Me!Editbox1 = I

That should give you the result you need.
0
 
LVL 9

Expert Comment

by:mcallarse
ID: 8049407
Can use the following in your VB code or as the ControlSource of a field in your form or report:

=DCount("ID","MSysObjects","Type=1 And Name Not Like 'MSys*')

In a query, would be:

SELECT Count(ID)
FROM MSysObjects
WHERE Type=1 And [Name] Not Like 'MSys*'
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 8049686
External database, mcallarse! That'll give you the count in the current db :)
0
 
LVL 9

Expert Comment

by:mcallarse
ID: 8049944
Must be tired...

sqltext = "SELECT Count(ID) AS tblCount FROM MSysObjects WHERE Type=1 And [Name] Not Like 'MSys*'"
set rs = dbPETSdb.openrecordset(sqltext)
Me!Editbox1 = rs!tblCount

To avoid having code in your Form Class module, could place the code to get the table count in a public function (GetTableCount), then reference that function in the field control source (=GetTableCount).
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
Beware when using the ListIndex and the Column() properties of a listbox in Access 2007.  A bug has been identified in the Access 2007 listbox code which can cause the .ListIndex property to return a -1, and the .Columns(#) property to return a NULL…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question