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:

	?Application.CurrentData.AllTables.Count

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

The code snippet tells the truth, however: this database does indeed contain 15 tables. The ones you couldn't see before are the system tables. Let's display them!

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

By isolating the data tables from the system tables in this way, you can work with the data tables how ever you want, without worrying about crashing your application.