Hey, Scripting Guy! How Can I Retrieve the Field Size and a Sample Record For All the Tables and Fields in an Office Access Database?

ScriptingGuy1

I recently read an article of yours that showed me how I can retrieve the names of all the tables in an Access database, along with a list of all the fields in each table and the data type of each of those fields. That’s a really useful script, but it would be even more useful if it could list the field size and also display a sample record for each table. Can you help me with that?

— SP

Hey, SP. You know, this might very well be the best day in the Scripting Guy who writes this column’s entire life. Why is this such a great day? Well, awhile back the Scripting Guys began a new ritual in which they start off every morning by getting together and eating doughnuts.

At any rate, the Scripting Guy who writes this column brought in the doughnuts this morning. (Come to think of it, the Scripting Guy who writes this column brings in the doughnuts every morning.) As it turned out, however, Scripting Guy Jean Ross needed to work from home today. What did that mean? You got it: the Scripting Guy who writes this column got to eat both doughnuts! And chocolate-covered ones, to boot!

Could it get any better than that?

Now, because he’s way behind on his commitments for the duo’s upcoming trip to Barcelona, the Scripting Guy who writes this column originally toyed with the idea of not writing this column, at least not today. (His plan was to tell everyone that he did write it, but the Scripting Editor deleted it.) Two doughnuts later, however, he’s in such a good mood that he decided, what the heck, let’s do a column anyway. With that in mind, here’s a script that can grab all the tables in an Access database; report back the name, data type, and size for each and every field in those tables; and then display a sample record for each table.

Here’s the script:

Const adSchemaTables = 20
Const adSchemaColumns = 4

Set objConnection = CreateObject(“ADODB.Connection”) 
Set objRecordSet2 = 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
    strSample = “”
    strTableName = objRecordset(“Table_Name”)
    If UCase(objRecordset(“Table_Type”)) = “TABLE” Then
    Set objFieldSchema = objConnection.OpenSchema(adSchemaColumns, _
        Array(Null, Null, strTableName))
        Wscript.Echo “============================================================”
        Wscript.Echo UCase(objRecordset(“Table_Name”))
        Wscript.Echo “============================================================”
        Wscript.Echo
        objRecordset2.Open “SELECT * FROM ” & strTableName, objConnection
        objRecordset2.MoveFirst
        Do While Not objFieldSchema.EOF
            strColumn = objFieldSchema(“Column_Name”)
            Wscript.Echo “Field Name: ” & objFieldSchema(“Column_Name”) 
            Wscript.Echo “Data Type: ” & objFieldSchema(“Data_Type”)
            Wscript.Echo “Field Size: ” & objFieldSchema(“Character_Maximum_Length”)
            Wscript.Echo
            strSample = strSample & strColumn & ” — ” & _
                objRecordset2.Fields.Item(strColumn) & vbCrLf
            objFieldSchema.MoveNext
        Loop
        Wscript.Echo
        Wscript.Echo “SAMPLE RECORD”
        Wscript.Echo strSample
        Wscript.Echo
        Wscript.Echo
        objRecordset2.Close
    End If
    objRecordset.MoveNext
Loop

objConnection.Open _
    “Provider = Microsoft.ACE.OLEDB.12.0; ” & _
        “Data Source = C:\Scripts\Test.accdb”

We’re not going to talk about the basic fundamentals behind retrieving table/field information from an Access database; that – along with a fascinating discussion of “lifelogging” – is covered in our first article on this subject. Besides, the Scripting Guy who writes this column is feeling a little sluggish all of a sudden; that in itself is reason enough to skip the fundamentals and, instead, concentrate on the code that distinguishes this script from our original script.

As we did in our original script, we start this one out by defining a pair of constants (adSchemaTables and adSchemaColumns), constants that enable us to access the properties of the database’s tables and fields, respectively. We then create an instance of the ADODB.Connection object, the object used to connect to a database, as well as an instance of the ADODB.Recordset object (an instance we named objRecordset2). What are we going to use that object for? We’ll explain that in just a minute.

From there we call the Connection object’s Open method to open the database C:\Scripts\Test.mdb, then use the following line of code to return a recordset (objRecordset) containing information about all the tables found in Test.mdb:

Set objRecordset = objConnection.OpenSchema(adSchemaTables)

At this point, we get serious.

Well, right after we eat another doughnut, that is.

OK, now we get serious. Our first step is to set up a Do Until loop that runs until objRecordset’s EOF (end-of-file) property is True. (In other words, a loop that runs until we’ve finished looking at all the items in the recordset.) Inside this loop, we set the value of a variable named strSample to nothing, then assign the value of the Table_Name property (which, needless to say, is nothing more than the name of the table) to the variable strTableName.

That brings us to this line of code:

If UCase(objRecordset(“Table_Type”)) = “TABLE” Then

To be honest, we threw this in just for the heck of it. By limiting our reported data to those tables that have a Table_Type equal to TABLE we end up only displaying information for the database tables that we created; any other tables (such as the various sundry Access system tables) are ignored. That seemed like a good idea to us; after all, 99% of the time (at least) these are the only tables you’re interested in anyway.

Assuming we do have a user-created table we then call the OpenSchema method in order to return field-related information for the first table in the recordset:

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

We then use this block of code to write a “header” to the screen:

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

As you can see, this is no big deal: it’s just the table name (TABLE_NAME) bracketed by some equals sign, the better to make the name stand out and to separate one table from another. In other words, this gives us a header that looks like this:

============================================================
COMPUTERS
============================================================

And then we have these two lines of code:

objRecordset2.Open “SELECT * FROM ” & strTableName, objConnection
objRecordset2.MoveFirst

What are these lines doing here? Well, we now know the name of each field in the table; that information is safely tucked away in the recordset objFieldSchema. However, the information in objFieldSchema only includes “metadata” about the fields: it lists the field name, type, and size, but not the field value. We use the first line of code to create a regular old ADO recordset, one consisting of all data in the table strTableName. We then use the second line of code to move to the first record in the returned recordset. This first record is going to serve as the source of the sample record we want to display onscreen.

From there we set up a second Do Until loop to loop through the field data stored in objFieldSchema. Inside that loop we echo back some basic information about each field, including the value of the field’s Character_Maximum_Length property. And yes, as usual, you’re way ahead of us; that does tell us the size of the field:

Wscript.Echo “Field Size: ” & objFieldSchema(“Character_Maximum_Length”)

After that we use this line of code to store the field name, as well as the actual field value for the first record in the table, in the variable strSample:

strSample = strSample & strColumn & ” — ” & _
    objRecordset2.Fields.Item(strColumn) & vbCrLf

See what we’re doing? Suppose the first field in the first table is named ComputerName, and the first record in the table has the value atl-ws-01. That means that, at the moment anyway, strSample will be equal to this:

ComputerName — atl-ws-01

And then we loop around and repeat the process with the next field in the table, adding the field name (and sample value) to strSample. After we’ve looped through all the fields in the table we echo back the value of strSample, close objRecordset2, and then go back and do this all over again with the second table in the database.

When all is said and done, we’ll have a report similar to this:

============================================================
COMPUTERS
============================================================

Field Name: ComputerName
Data Type: 130
Field Size: 50
Field Name: SerialNumber
Data Type: 130
Field Size: 50
SAMPLE RECORD
ComputerName — atl-ws-01
SerialNumber — 1
============================================================
DISKDRIVES
============================================================
Field Name: Drive
Data Type: 130
Field Size: 50
Field Name: DriveSize
Data Type: 2
Field Size:
Field Name: SerialNumber
Data Type: 130
Field Size: 50
Field Name: Type
Data Type: 130
Field Size: 50
SAMPLE RECORD
Drive — C:
DriveSize — 50
SerialNumber — 1
Type —

Yes, very exciting.

We hope that answers your question, SP. And Scripting Guy Jean Ross, if you happen to be reading this, well, don’t worry about the rest of the team: if you want to work at home tomorrow that’s fine with us. In fact, feel free to work at home as often as you wish; whatever’s easiest – and best – for, uh, you.

Now, where did that last maple bar wander off to ….

ScriptingGuy1

Follow