Hack 15. Steer Clear of System Tables
Avoid incorrect results by leaving system tables out of your table count and definition routines.
How many tables are in your database? You might think finding this out is as easy as counting how many tables are listed on the Tables tab of your database window. To that I respond, "Try again!"
Access uses a number of system tables to control its own internal workings. Usually, these additional tables are hidden, but they are there nonetheless. Figure 2-8 shows a database with some tables.
Figure 2-8. Tallying the tables
It looks like this database contains eight tables, doesn't it? Let's try getting a count in a different way. In the VB Editor, activate the Immediate window (Ctrl-G). Then, enter the following code snippet and press the Enter key:
Figure 2-9 shows the code and its results in the Immediate window. For the database in Figure 2-8, the result is 15, so Access is telling us the database actually contains 15 tables, although only eight are visible on the Tables tab.
Figure 2-9. Counting all the tables
Back in the database proper (not the VB Editor), use the Tools Options menu to display the Options dialog box. Select the View tab. As shown in Figure 2-10, one of the options in the Show area is to display system objects. Select this checkbox to make the system tables visible.
Figure 2-10. Selecting to show system objects
Now, looking at the Tables tab in Figure 2-11, you can see the system tables.
Note that all the system table names start with MSys. This is actually a useful attribute about these tables because it makes it easy to remove them from a table count.
Figure 2-11. Displaying all tables, including system tables
2.4.1. The Code
But why does any of this matter? One reason is that an application might need to iterate through all the tables in a databaseperhaps to add a property, to look for a field or data, to alter the table structure in some way, and so on. In such circumstances, the system tables must be avoided. Fortunately, a simple code routine easily handles this by purposely avoiding all tables that have names beginning with MSys, as follows:
Sub count_tables() 'list tables in database Dim table_num As Integer Dim tbl_count As Integer With Application.CurrentData For tbl_count = 1 To .AllTables.Count If Left(.AllTables(tbl_count - 1).Name, 4) <> "MSys" Then Debug.Print .AllTables(tbl_count - 1).Name End If Next tbl_count End With End Sub
This code routine cycles through all the tables in the database and writes the name of each table to the debug (Immediate) window, as long as the table's name doesn't start with MSys. To use this routine, replace the table names with any particular per-table processing you need.
2.4.2. Running the Code
Figure 2-12 shows the output of this routine. The Immediate window is filled with just the pertinent application data tables, and that's exactly what we need.
Figure 2-12. Listing just the data tables