?
Solved

How to Count Tables in a MDB?

Posted on 2003-02-28
8
Medium Priority
?
352 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

777 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