Hey, Scripting Guy! How can I list all the tables in an Access database?

— KW

Hey, KW. You know, the best part about writing a daily column on system administration scripting is that people only see the finished result; they never see all the detours, dead-ends, and other frustrations we go through in order to produce that column. For example, people who read this column will think, “Man, those Scripting Guys are so smart: they even know how to list all the tables in an Access database!” Those people will never know that we had absolutely no idea how to list all the tables in an Access database, and that we stumbled upon the answer only after a long and fruitless search of the Access object model. But as far as the world knows, we Scripting Guys really are smart!

Well, unless we admitted how hard it was for us to answer this question. But even the Scripting Guys aren’t dumb enough to own up to something like that.

Now, where we were? Oh, right: how can you list all the tables in an Access database? That’s an easy one, KW; here’s how:

Const adSchemaTables = 20

Set objConnection = CreateObject(“ADODB.Connection”)
Set objRecordSet = CreateObject(“ADODB.Recordset”)
objConnection.Open _
    “Provider = Microsoft.Jet.OLEDB.4.0; ” & _
        “Data Source = ‘C:\Scripts\Test.mdb'” 
Set objRecordSet = objConnection.OpenSchema(adSchemaTables)
Do Until objRecordset.EOF
    Wscript.Echo “Table name: ” & objRecordset.Fields.Item(“TABLE_NAME”)
    Wscript.Echo “Table type: ” & objRecordset.Fields.Item(“TABLE_TYPE”)
    Wscript.Echo
    objRecordset.MoveNext
Loop

Admittedly, people without the Scripting Guys’ experience and expertise in the world of system administration scripting might think that the way to attack this problem would be to use the Microsoft Access object model. We Scripting Guys know better, however: instead, you get at this information using ADO (ActiveX Data Objects). And that’s actually a good thing: that means you can use this same basic approach to list the tables found in other kinds of databases. (Right: as if there even are databases other than Microsoft Access!)

Let’s take a closer look at the script and how it works. To begin with, we define a constant named adSchemaTables and set the value to 20; we’ll use this constant to tell the script what kind of database schema objects we’re interested in (needless to say, in this case, all we care about are tables). Are there other kinds of schema objects we can retrieve using a script? You bet: check out the SchemaEnum documentation on MSDN for more information.

After defining the constant we then create a pair of objects: the ADODB.Connection object and the ADODB.Recordset object. If you’ve had some experience working with ADO then you should be very familiar with the Connection and Recordset objects. But what if you’re new to database scripting and have no idea what we’re talking about here? That’s OK: the Scripting Guys webcast Database Scripting for System Administrators will give you all the background information you need to understand this script and how it works.

As soon as we have our two objects in-hand we can then use the Open method to open the file C:\Scripts\Test.mdb:

objConnection.Open _
    “Provider = Microsoft.Jet.OLEDB.4.0; ” & _
        “Data Source = ‘C:\Scripts\Test.mdb'”

And once we’ve done that we can then call the OpenSchema method and retrieve information about the database itself. What kind of information are we retrieving? Well, in this case we’re passing OpenSchema the constant adSchemaTables; that means we’re going to get back a recordset consisting of information about all the tables found in the database.

Because we’re getting back a recordset we need to next set up a Do Until loop to walk through and echo back information about all the records (that is, all the tables) in the recordset. That’s what we do here:

Do Until objRecordset.EOF
    Wscript.Echo “Table name: ” & objRecordset.Fields.Item(“TABLE_NAME”)
    Wscript.Echo “Table type: ” & objRecordset.Fields.Item(“TABLE_TYPE”)
    Wscript.Echo
    objRecordset.MoveNext
Loop

As you can see, our Do Until loop is designed to run until the EOF (end of file) property is true; in other words, we want to keep looping around until we run out of records. To that end, take careful note of the last line of code within the Do Until loop: objRecordset.MoveNext. Is that important? You bet it is: that’s the line of code that tells the script, hey, after you finish with one record move on to the next. If you don’t include the MoveNext method the script will remain stuck on the first record. In turn, that means you’ll continue to echo back information about record 1 and will never, ever reach the end of the recordset. (For a more detailed explanation of this take a look at the answer to the scripting puzzle Yes, Ken, We See You Already!)

So do we do anything inside the loop besides call the MoveNext method? Of course we do; we also echo back the values of the TABLE_NAME and TABLE_TYPE properties:

Wscript.Echo “Table name: ” & objRecordset.Fields.Item(“TABLE_NAME”)
Wscript.Echo “Table type: ” & objRecordset.Fields.Item(“TABLE_TYPE”)

Is that important? You might say that; after all, that happens to be the list of tables (and their associated table types) found in the database C:\Scripts\Test.mdb:

Table name: UserInformation
Table type: TABLE

Table name: ComputerInformation
Table type: TABLE
Table name: MSysAccessObjects
Table type: ACCESS TABLE
Table name: MSysACEs
Table type: SYSTEM TABLE
Table name: MSysObjects
Table type: SYSTEM TABLE
Table name: MSysQueries
Table type: SYSTEM TABLE
Table name: MSysRelationships
Table type: SYSTEM TABLE

As you probably noticed, this script brings back information about all the tables in the database, including system tables you typically don’t interact with (or care about). Can you limit the returned data just to those tables (like UserInformation and ComputerInformation) that you typically do interact with? Sure; all you have to do is modify the script like so:

arrCriteria = Array(Empty, Empty, Empty, “Table”) 

Set objRecordSet = objConnection.OpenSchema(adSchemaTables, arrCriteria)

What we’ve done here is created an array named arrCriteria and told it to look just for tables where the TABLE_TYPE is equal to Table. (The three Empty parameters represent other criteria we could use in our filter; for more information, see the SchemaEnum documentation.) We then add the variable arrCriteria as an optional second parameter to the OpenSchema method.

What will we get back when we run this modified script? We’ll get back this:

Table name: UserInformation
Table type: TABLE

Table name: ComputerInformation
Table type: TABLE

Here’s what the entire script looks like:

Const adSchemaTables = 20

Set objConnection = CreateObject(“ADODB.Connection”)
Set objRecordSet = CreateObject(“ADODB.Recordset”)
objConnection.Open _
    “Provider = Microsoft.Jet.OLEDB.4.0; ” & _
        “Data Source = ‘C:\Scripts\Test.mdb'” 
arrCriteria = Array(Empty, Empty, Empty, “Table”) 
Set objRecordSet = objConnection.OpenSchema(adSchemaTables, arrCriteria)
Do Until objRecordset.EOF
    Wscript.Echo “Table name: ” & objRecordset.Fields.Item(“TABLE_NAME”)
    Wscript.Echo “Table type: ” & objRecordset.Fields.Item(“TABLE_TYPE”)
    Wscript.Echo
    objRecordset.MoveNext
Loop

And, yes, all that was pretty easy, wasn’t it? Well, at least for the Scripting Guys ….