count tables forms etc (1 Viewer)
- Thread starter razaqad
- Start date
- Local time
- Today, 08:08
- Joined
- Feb 28, 2001
- Messages
- 27,156
razaqad
You need to look up the following topics in your Help Files. From there, you can perhaps figure out how to do what you wish.
Collections; (In particular, look at examples of traversing collections.)
Tabledefs;
Querydefs;
Fielddefs;
Documents;
Modules;
In overview, tables are members of the tabledefs collection; queries are members of the querydefs collection; functions and subroutines are embodied within the Lines collection that makes up each Module in the Modules collection; reports and forms are members of the documents collection.
PLEASE NOTE FINE POINTS:
A form is a member of the Forms collection if and only if it is open; ditto, reports and the Reports collection. If the item isn't open, it is ONLY a member of the documents collection. Further, its Controls are not visible in the latter condition.
If you were planning on looking at the fields, they are members of the Fielddefs collection, one of which is an element of each Table in a Tabledef and of each Query in a Querydef.
OK, once you have the collections understood, you can use VBA to traverse and count/document each collection. If you were planning to count fields then remember that you are counting the members of a collection within a collection. You will write a small loop for each collection you wanted to traverse. You can use the "For each xxxx in yyyy / next xxxx" syntax to traverse the collections, though you need different variables in each case.
I do not recommend using a Variant for xxxx (though it will work) because the properties of a Variant don't exist until you bind it to something. So your VBA code editor can't tell you what your properties are EXCEPT in debug mode. On the other hand, if you declare variables for a table, query, document, module, etc., you can use VBA's auto-complete feature to help you with the programming.
When you are looking at the entry points in a module, remember that a Module is stored as a collection of Lines. So you have to read the lines to see if you have a Sub or Function declaration, or if you have something in the header of the module, essentially the module-global area. (Which is different from the Application-global stuff, which you have to declare as "Public" in a Module for it to be application-global.)
Since I don't know what you wanted to do besides counting structures, I cannot tell you anything more specific. Here are a few more "gotcha" factors.
Access has some SysXxxx tables you don't normally see. They exist in the Tabledefs collection. Also, if you have hidden tables, they show up here, too.
If you wanted to examine the contents of a form or report, you have to Open it; otherwise, the Controls collection inside that document is not visible. Note also that you can have headers and footers as well as the detail area inside of either. Reports can have multiple headers and footers; Forms usually only have one. Note also that a sub-form or sub-report control can itself contain collections, but you are better off just noting the name of the contained report or form and documenting it separately.
Queries exist that you won't remember creating. Any time you used one of the wizards to create a combo box or list box, particularly with a filter, you created a hidden query with a funky name. Your machine isn't lying to you when you see those queries; it is telling you something behind the scenes that Access created for you.
Each type of object has a definitional entry in the Object Browser so you can see the correct spelling of the property names. There is also a Type property for each object, and the browser can help you drill down to the definition of the types. For instance, each object has a type such as table, query, module, etc. Fields have a datatype object such as Long, Double, Boolean (YesNo), etc. Use the Object Browser to identify the data types you need to know about.
You didn't mention (and I don't want to know) which version of Access you are using, so all I can do is suggest that you will have to do most of the work from here in terms of researching names, fields, etc.
You need to look up the following topics in your Help Files. From there, you can perhaps figure out how to do what you wish.
Collections; (In particular, look at examples of traversing collections.)
Tabledefs;
Querydefs;
Fielddefs;
Documents;
Modules;
In overview, tables are members of the tabledefs collection; queries are members of the querydefs collection; functions and subroutines are embodied within the Lines collection that makes up each Module in the Modules collection; reports and forms are members of the documents collection.
PLEASE NOTE FINE POINTS:
A form is a member of the Forms collection if and only if it is open; ditto, reports and the Reports collection. If the item isn't open, it is ONLY a member of the documents collection. Further, its Controls are not visible in the latter condition.
If you were planning on looking at the fields, they are members of the Fielddefs collection, one of which is an element of each Table in a Tabledef and of each Query in a Querydef.
OK, once you have the collections understood, you can use VBA to traverse and count/document each collection. If you were planning to count fields then remember that you are counting the members of a collection within a collection. You will write a small loop for each collection you wanted to traverse. You can use the "For each xxxx in yyyy / next xxxx" syntax to traverse the collections, though you need different variables in each case.
I do not recommend using a Variant for xxxx (though it will work) because the properties of a Variant don't exist until you bind it to something. So your VBA code editor can't tell you what your properties are EXCEPT in debug mode. On the other hand, if you declare variables for a table, query, document, module, etc., you can use VBA's auto-complete feature to help you with the programming.
When you are looking at the entry points in a module, remember that a Module is stored as a collection of Lines. So you have to read the lines to see if you have a Sub or Function declaration, or if you have something in the header of the module, essentially the module-global area. (Which is different from the Application-global stuff, which you have to declare as "Public" in a Module for it to be application-global.)
Since I don't know what you wanted to do besides counting structures, I cannot tell you anything more specific. Here are a few more "gotcha" factors.
Access has some SysXxxx tables you don't normally see. They exist in the Tabledefs collection. Also, if you have hidden tables, they show up here, too.
If you wanted to examine the contents of a form or report, you have to Open it; otherwise, the Controls collection inside that document is not visible. Note also that you can have headers and footers as well as the detail area inside of either. Reports can have multiple headers and footers; Forms usually only have one. Note also that a sub-form or sub-report control can itself contain collections, but you are better off just noting the name of the contained report or form and documenting it separately.
Queries exist that you won't remember creating. Any time you used one of the wizards to create a combo box or list box, particularly with a filter, you created a hidden query with a funky name. Your machine isn't lying to you when you see those queries; it is telling you something behind the scenes that Access created for you.
Each type of object has a definitional entry in the Object Browser so you can see the correct spelling of the property names. There is also a Type property for each object, and the browser can help you drill down to the definition of the types. For instance, each object has a type such as table, query, module, etc. Fields have a datatype object such as Long, Double, Boolean (YesNo), etc. Use the Object Browser to identify the data types you need to know about.
You didn't mention (and I don't want to know) which version of Access you are using, so all I can do is suggest that you will have to do most of the work from here in terms of researching names, fields, etc.
- Local time
- Today, 09:08
- Joined
- Feb 19, 2002
- Messages
- 43,257
Here's a table of the object types that I have discovered. There may be more.
Copy the list to a table named tblObjectTypeCodes.
Then paste this SQL string into a querydef:
SELECT MSysObjects.Type, tblObjectTypeCodes.TypeDesc, Count(*) AS ObjectCount
FROM MSysObjects INNER JOIN tblObjectTypeCodes ON MSysObjects.Type = tblObjectTypeCodes.Type
GROUP BY MSysObjects.Type, tblObjectTypeCodes.TypeDesc;
Just be aware that MS does not promise that the MSys tables will remain stable from version to version so this query may break in a newer version of Access.
Code:
Type TypeDesc
-32768 Form
-32766 Macro
-32764 Reports
-32761 Module
-32758 Users
-32757 Database Document
-32756 Data Access Pages
1 Table - Local Access Tables
2 Access Object - Database
3 Access Object - Containers
4 Table - Linked ODBC Tables
5 Queries
6 Table - Linked Access Tables
8 SubDataSheetsThen paste this SQL string into a querydef:
SELECT MSysObjects.Type, tblObjectTypeCodes.TypeDesc, Count(*) AS ObjectCount
FROM MSysObjects INNER JOIN tblObjectTypeCodes ON MSysObjects.Type = tblObjectTypeCodes.Type
GROUP BY MSysObjects.Type, tblObjectTypeCodes.TypeDesc;
Just be aware that MS does not promise that the MSys tables will remain stable from version to version so this query may break in a newer version of Access.
