Containers, Collections and Objects

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.