Access Hacks

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 ...