Using Access VBA code to count all forms, tables, queries, reports, vba modules, etc.

kdnichols

Registered User.
Local time
Today, 07:06
Joined
Aug 13, 2008
Messages
18
Hello Everyone,

I know it can be done and I have seen some code snippets before, but how can I use Access vba code to count all forms, tables, queries, reports, vba modules, objects to get a total count of each for an Access database?

Any help is greatly appreciated.

Thanks,

Kurt

:)
 
Hello,

Thanks for your fast reply. I clicked on the link and it will not redirect me.

Can you post the actual link instead so I can cut and paste it?

Thanks,


Kurt

:)
 
I'll explain (roughly) where to look in Access Help.

Access stores things in Collections. VBA code has access to the various collections as "properties" of the current database (i.e. the database returned by CurrentDB() function). The names of the collections differ sometimes depending on the version of Access you are using, so your best bet is to look at the Access Help for collections.

Table definitions are stored in the Tables collection. Each table defined in the collection has a Fields collection.

Queries are stored in the Queries collection. You can see a list of referenced fields in the query along with the source table for each field. (Which of course can be different from one field to the next in a multi-table JOIN query or UNION query).

Forms and Reports are kept in the Documents Collection in older MDB files. You have to open the object at least in design mode to see whether it is a report or a form. When there, you can see the recordsource for the document, which might be a query name, or it might be the actual query in some rare cases. Each document has a Controls collection. Each control that is bindable AND BOUND will have a controlsource that tells you the origin of the data for that control.

Modules are in two places, which makes them a bit trickier. A form or report can have a single class module. The general modules pane of the database window can also have several named modules. Each module there is part of the Modules collection. Each module contains a collection called Lines, which is nothing more than the source code for the module - if it has not been converted to an MDE file beforehand.

Enumeration of the contents of any collection is remarkably the same. You name the collection and then write a loop based on syntax

FOR EACH object IN collection....

The object variable should be the right type of object for the collection. So for the Tables collection, you loop on a TableDef object. For Fields collections (one collection per TableDef), you loop on a FieldDef object. For Queries collection, you loop on a QueryDef object. For Documents collection, you loop on a Document object. For Modules collection and for the single class module of a document, you need a Module object.

You can look up the properties of each of the object types in Access Help to see what you might see. In general, as object they will have object.Name and object.Type properties. Beyond that, you need to read up on the topic.

Macros are their own beasties that are usually better converted to Modules in the first place, but if you have them, I can't tell you what to look for. I never bothered with them in my experiments.
 
Hello DocMan,

When I try this code I get an error message:
Public Sub countTables()
Dim table As Variant
Dim tableCount As Integer
tableCount = 0
'You can iterate thru the Forms collection to see which forms are open
'Or iterate thru the AllForms collection to count forms in the database
For Each table In AccessObjectProperties.Application.AllTables
'Debug.Print Modules.Name
tableCount = appCount + 1
Next
Debug.Print "Number of Tables: " & tableCount
MsgBox "You have " & tableCount & " Tables in your database."
End Sub

It states RunTime error 424 object reqired.
I looked in the object browser so I don't understand what I am doing wrong. It shows All Tables to be part of Application.
Any and all help is greatly appreciated.

Thanks,

Kurt
 
Hello DocMan,

I forgot to mention to you in the last email that we had a baby girl Stephanie Grace Monday night. She was two weeks early weighing in at 4 pounds 7 ounces and 17 inches long.

This is my second batch. I have a 23 yeard old daughter who is graduating college in May to be a RN!

I hope to be a grandpa someday but not too soon!

:)

Kurt
 
Using DAO ... (DAO Object Model)

Tables --> TableDefs collection of a Database object, each table (even system ones) is a TableDef object. Tables are also included in the "Tables" container of the Containers collection of a Database object.

Queries --> QueryDefs collection of a Database object, each query is a TableDef object. Queries are also included in the "Tables" container of the Containers collection of a Database object.

-- Side note ---
From a core system level point of view a Table and a Query are basically the same thing (notice how a querydef and tabledef can not share a name) ... they are basically a set of records. The ANSI definition has the term "Table" which includes "Base Tables" (TableDef), "Derived Table" (SQL statement/QueryDef), and "Viewed Tables" (QueryDef)
--- End Side Note ---

Forms --> The Documents collection of the "Forms" container of the Containers collection of a Database object.

Reports --> The Documents collection of the "Reports" container of the Containers collection of a Database object.

Modules --> The Documents collection of the "Modules" container of the Containers collection of a Database object.

Macros --> The Documents collection of the "Scripts" container of the Containers collection of a Database object.

----

Also, Forms, Reports, Modules, Macros, and DAP's are collections for the Access 'Project' (CurrentProject implemented in A2000). With the collections:

.AllForms
.AllReports
.AllModules
.AllMacros
.AllDataAccessPages

Then ... of course another tracker of objects is the MSysObjects table. You can create a QueryDef with a SQL statement that looks like this ...

Code:
SELECT vTbl.ObjectType, Count(vTbl.ID) AS CountOfID
FROM (SELECT Switch(Type=-32768,"Forms"
                   ,Type=-32766,"Macros"
                   ,Type=-32764,"Reports"
                   ,Type=-32761,"Modules"
                   ,Type=-32756,"Data Access Page"
                   ,Type In (1,4,6),"Tables (Inc. System Tbls)"
                   ,Type=5,"Queries"
                   ,Type=3,"Containers"
                   ,1=1,"Other Stuff Managed by Access") AS ObjectType, ID
      FROM MSysObjects
      WHERE Left(Name,1)<>"~") AS vTbl
GROUP BY vTbl.ObjectType;

To get the counts of your objects. If you save the querydef, you will have to remember to subtract one from the query count since the query used to do the counting should probably not be included. :)

... And since I gave that querydef SQL statement, here is a sample of code that provides the object counts too ... Do take note that I loop ALL the containers, which will most certainly include some counts you don't want/need ...

Code:
Public Sub ObjectCounts()
 
    Dim qry As DAO.QueryDef
    Dim cnt As DAO.Container
 
    'Delete all TEMP queries ...
    For Each qry In CurrentDb.QueryDefs
        If Left(qry.Name, 1) = "~" Then
            CurrentDb.QueryDefs.Delete qry.Name
            CurrentDb.QueryDefs.Refresh
        End If
    Next qry
 
    'Print the values to the immediate window
    With CurrentDb
 
        Debug.Print "--- From the DAO.Database ---"
        Debug.Print "-----------------------------"
        Debug.Print "Tables (Inc. System tbls): " & .TableDefs.Count
        Debug.Print "Querys: " & .QueryDefs.Count & vbCrLf
 
        For Each cnt In .Containers
            Debug.Print cnt.Name & ":" & cnt.Documents.Count
        Next cnt
 
    End With
 
    'Use the "Project" collections to get the counts of objects
    With CurrentProject
        Debug.Print vbCrLf & "--- From the Access 'Project' ---"
        Debug.Print "---------------------------------"
        Debug.Print "Forms: " & .AllForms.Count
        Debug.Print "Reports: " & .AllReports.Count
        Debug.Print "DataAccessPages: " & .AllDataAccessPages.Count
        Debug.Print "Modules: " & .AllModules.Count
        Debug.Print "Macros (aka Scripts): " & .AllMacros.Count
    End With
 
End Sub

WHEW!! ... I think thats it .... please let of know if all this helps out! ... :)
 
Hello Kurt ...

In your code ... this:

For Each table In AccessObjectProperties.Application.AllTables

Should be ...

For Each table In CurrentProject.AllTables

...

... But there is no need to loop since .Count is a property of .AllTables

tableCount = CurrentProject.AllTables.Count

But do rememember that the .Count includes the system tables (those that start with MSys)
 
Hello Brent,

Whew thanks for that great info! Yep that was a lot!

I tried running this code:

BTW I am using Access 2007

Public Sub ObjectCounts()
Dim qry As DAO.QueryDef
Dim cnt As DAO.Container
'Delete all TEMP queries ...
For Each qry In CurrentDb.QueryDefs
If Left(qry.NAME, 1) = "~" Then
CurrentDb.QueryDefs.DELETE qry.NAME
CurrentDb.QueryDefs.Refresh
End If
Next qry
'Print the values to the immediate window
With CurrentDb
Debug.Print "--- From the DAO.Database ---"
Debug.Print "-----------------------------"
Debug.Print "Tables (Inc. System tbls): " & .TableDefs.Count
Debug.Print "Querys: " & .QueryDefs.Count & vbCrLf
For Each cnt In .Containers
Debug.Print cnt.NAME & ":" & cnt.Documents.Count
Next cnt
End With
'Use the "Project" collections to get the counts of objects
With CurrentProject
Debug.Print vbCrLf & "--- From the Access 'Project' ---"
Debug.Print "---------------------------------"
Debug.Print "Forms: " & .AllForms.Count
Debug.Print "Reports: " & .AllReports.Count
Debug.Print "DataAccessPages: " & .AllDataAccessPages.Count
Debug.Print "Modules: " & .AllModules.Count
Debug.Print "Macros (aka Scripts): " & .AllMacros.Count
End With
End Sub

It runs but I don't see any answer.

What else do I need to do or what did I forget?

Thanks,

Kurt
 
Hello,

It worked I opened the View Immediate Window!

Whew you are da man!

Do you happen to have some script handy where I can export this to an Excel spreadsheet?

Many thanks,


Kurt


:):):):):):):):):)
 
If you want it in Excel ... then give the query I posted a try, then just copy/paste the data ... or in VBA use the TransferDatabase method of the DoCmd object.
 

Users who are viewing this thread

Back
Top Bottom