Hey, Scripting Guy! In a previous column you showed us how to list all the tables in an Access database. How can I list all the fields in those tables, along with the data type of each field?

— MF

Hey, MF. As you may or may not know, there’s a Microsoft researcher named Gordon Bell who has devoted his professional efforts to “lifelogging,” an activity in which he uses cameras, scanners, tape recorders, and other gadgets to keep a digital record of everything he ever does. According to a recent article in The New Yorker:

“Bell’s archive now also contains a hundred and twenty-two thousand e-mails; fifty-eight thousand photographs; thousands of recordings of phone calls he has made; every Web page he has visited and instant-messaging exchange he has conducted since 2003; all the activity of his desktop (which windows, for example, he has opened); eight hundred pages of health records, including information on the life of the battery in his pacemaker; and a sprawling category he describes as “ephemera,” which contains such things as books he has written and books from his library; the labels of bottles of wine he has enjoyed; and the record of a bicycle trip through Burgundy, where he tried to eat in as many starred restaurants as he could (he averaged 2.2 stars per meal—“I do a lot of measuring,” he says).”

Before you ask, the Scripting Guys do not engage in lifelogging, mainly because none of them have lives worth logging. For example, the lifelog of the Scripting Guy who writes this column would consist of little more than an endless pile of receipts from the local doughnut shop. You can only log “two maple bars and a pair of cinnamon twists” so many times before it starts to seem a little pointless.

However, it’s also true that the Scripting Guys don’t need a lifelog, at least not as long as they have the Hey, Scripting Guy! column. For example, when the Scripting Guy who writes this column first read this question he thought, “List all the tables in an Access database, huh? That would be a useful script, wouldn’t it? But who knows how to write a script like that?” And then, as he read a little further, he realized that not only did he know how to write a script that lists all the tables in an Access database, but that he’d already done so. “Well, what do you know,” he thought to himself. “Maybe I should read this column more often.”

At any rate, thanks to the Hey, Scripting Guy! lifelog the Scripting Guy who writes this column was able to recreate that magic moment when he wrote a script that retrieved a list of all the tables in an Access database. From there it was relatively painless to take the next step and list the fields and data types for each of those tables:

Const adSchemaTables = 20
Const adSchemaColumns = 4

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
    strTableName = objRecordset(“Table_Name”)
    Set objFieldSchema = objConnection.OpenSchema(adSchemaColumns, _
        Array(Null, Null, strTableName))
    Wscript.Echo UCase(objRecordset(“Table_Name”))
    Do While Not objFieldSchema.EOF
        Wscript.Echo objFieldSchema(“Column_Name”) & “, ” & objFieldSchema(“Data_Type”)
        objFieldSchema.MoveNext
    Loop
    Wscript.Echo
    objRecordset.MoveNext
Loop

Let’s take a few moments out from our busy lives and see if we can figure out how this script works. We start out by defining a pair of constants, adSchemaTables and adSchemaColumns. Eventually we’ll use one of these constants to tell the script to retrieve table information from the database, then use the other to retrieve column (field) information from the database. See if you can guess which constant will be used for which task.

OK, apparently that wasn’t as hard as we thought it would be. Good thing we didn’t make that the basis of our recent TechEd Challenge.

After defining the constants we create instances of the ADODB.Connection and ADODB.Recordset objects, then use the following line of code to open the database C:\Scripts\Test.mdb:

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

Once we’ve made the connection to the database we can retrieve a list of tables by using a single line of code and the OpenSchema method, passing the constant adSchemaTables as the only parameter for that method:

Set objRecordSet = objConnection.OpenSchema(adSchemaTables)

As soon as we’ve done that we’re ready to really spice up the old lifelog: we’re now ready to loop through the collection of tables and, for each one, retrieve not only a list of all the fields in the table, but also the data type of each field.

To that end we start out by setting up a Do Until loop that runs until we’ve finished iterating through all the records in the recordset (that is, until the recordset’s EOF – end-of-file – property is True). The first thing we do inside the loop is take a look at record 1, grab the value of the Table_Name property, then store that value in a variable named strTableName:

strTableName = objRecordset(“Table_Name”)

Once we know the name of the first table we can then use this line of code to retrieve a second recordset, this one consisting of all the fields (columns) in the table:

Set objFieldSchema = objConnection.OpenSchema(adSchemaColumns, _
    Array(Null, Null, strTableName))

As you can see, we’re once again calling the OpenSchema method in order to access the database schema. This time, however, we need to pass a few additional parameters to OpenSchema. For starters, we pass the constant adSchemaColumns, which simply tells the script that we want to return column information. We then pass an array of “constraint columns.” This array consists of the following values:

For the time being, don’t worry too much about the table catalog and table schema. Just make sure your table name appears as the third item in the array and everything should work just fine.

After retrieving the second recordset (objFieldSchema) we can start displaying the data for the first table in the database. To kick off that process, we first echo back the name of the table, using the UCase function to make the table name appear in all uppercase characters:

Wscript.Echo UCase(objRecordset(“Table_Name”))

We then set up a second Do Until loop, this one designed to loop through the collection of fields in table 1:

Do While Not objFieldSchema.EOF

And what are we going to do inside this loop? Two things. First, we’re going to echo back the values of the Column_Name and Data_Type properties:

Wscript.Echo objFieldSchema(“Column_Name”) & “, ” & objFieldSchema(“Data_Type”)

Second, we’re going to call the MoveNext method to move us from the first record in the recordset to the next record. After we’ve echoed back information about each item in the recordset we echo a blank line to the screen and then call MoveNext a second time, this time to move to the next record (table) in our original recordset. When we’re finished we’ll have a report that looks something like this (depending on the tables and fields you have in your database):

COMPUTERS
ComputerName, 130
SerialNumber, 130

DISKDRIVES
Drive, 130
DriveSize, 2
SerialNumber, 130
Type, 130
EMPLOYEES
Bonus, 6
Department, 130
Division, 130
EmployeeID, 5
EmployeeName, 130
Salary, 6

Ah, good question: what are those numbers following the field names? Well, as it turns out, data types are stored as numeric values; for example, the field Salary has a data type of 6. If you look up the values list for the DatatypeEnum you’ll see that a 6 indicates a currency data type. And yes, we probably could have – and maybe should have – included all this information in our sample script. We didn’t do that simply because we were afraid the script would become too long and look far more complicated than it really is.

Besides, our lifelog was already beginning to fill up.

Incidentally, you don’t need to look at all the tables in the database. For example, suppose you were only interested in getting field information for the Employees table:

Const adSchemaColumns = 4

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(adSchemaColumns, _
    Array(Null, Null, “Employees”))
Do Until objRecordset.EOF
    Wscript.Echo objRecordset(“Column_Name”) & “, ” & objRecordset(“Data_Type”)
    objRecordset.MoveNext
Loop

That should do it, MF. At least we hope it does, because we have to leave: it’s time for our mid-morning doughnut break.

Well, OK: time for our second mid-morning doughnut break. Just two more after that and it’ll be lunch time.