An Access Database is a container. Within this container are collections of objects which include tables, queries, forms and reports.
Objects can themselves be containers. For example a table contains columns (or fields) while forms and reports contain controls that display data.
So we could iterate through our database’s Tables (TableDefs) and through each Table’s Fields like this
Public Function Field_Count() As Integer Dim tdf As DAO.TableDef, fld As DAO.Field For Each tdf In CurrentDb.TableDefs Debug.Print tdf.Name For Each fld In tdf.Fields Debug.Print fld.Name Next fld Next tdf End Function
Public Function Control_Count() As Integer Dim frm As Form, ctl As control For Each frm In Application.Forms Debug.Print frm.Name For Each ctl In frm.Controls Debug.Print ctl.Name Next ctl Next frm End Function
But here is where it starts getting tricky. The Forms Collection only contains Open Forms and then only Main Forms, not Sub Forms. So to get at all your controls you need to iterate through the CurrentProject.AllForms collection, open each form, iterate through its controls and then close it.
Public Function Control_Count() As Integer Dim frm As Form, ctl As control, obj As Object For Each obj In Application.CurrentProject.AllForms Debug.Print obj.Name DoCmd.OpenForm obj.Name Set frm = Forms(obj.Name) For Each ctl In frm.Controls Debug.Print ctl.Name Next ctl DoCmd.Close acForm, obj.Name Next obj End Function
Reports work pretty much the same as Forms.
So why would we want to do this?
Well one reason is that when I’m given an application, that someone else created, I first want to document all the objects.
So I use procedures like this to write a list of Tables and Fields to tables. UsysTable and UsysField. Now I could look inside the Msys tables but it isn’t good practice to go messing with the System Tables so User System Tables are a better option. I can add new columns and change data as I see fit.
I use UsysTable to refresh or change the table links when the application opens. So I can easily change the links to use Development, Test or Production data.
Once having populated these two tables, by inspecting the existing Tables and Fields, I will in future use then in exactly this opposite way. When I want to add a new Table, I’ll add a record for it to UsysTable and Fields to UsysField. Then I can use these tables to create new Tables and Fields in my Backend Database using code. Why? So that the same process that does this in the Development Environment will do exactly the Same in the Test Environment and then in Production. For more on The Development Cycle click here
For Forms and Reports I will want to determine whether they are still in use so that I can clean out all the Interface Objects that are no longer used. So once I have a list of the Forms and Reports I can add a line of code to each one’s Open Event to log its use.
And I can inspect other objects and code to see where each Form and Report is referenced. If there are no references then I can safely delete it. I usually find lots of disused object that no one has bothered to delete.