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?

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?

  • Comments 1
  • Likes

Hey, Scripting Guy! Question

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

SpacerHey, Scripting Guy! AnswerScript Center

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.

Note. Does this help them build team morale? Is this a way for the Scripting Guys to find time to sit down and discuss future plans for the Script Center? To tell you the truth, it’s mostly just an excuse to sit down and eat 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.

Note. Yes, that is a lot of work. Obviously the Scripting Guy who writes this column needs two doughnuts in order to keep up his strength.

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

Access 2007: If you’re running this against an Access 2007 database, your Open statement will look a little different than the Open statement in this example. Not only is the file extension on the database file different (.accdb rather than .mdb) but the provider is a little different too:

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.

Note. Is the Scripting Guy who writes this column feeling a little sluggish because he ate too many doughnuts? Too many doughnuts?!? We don’t even know what that question means!

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.

Note. What about that other 1% of the time? No problem; just delete the If-Then statement and the End If statement and you’ll get back information for all the tables in the database.

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")

Note. We should point out that fields will only have a size if the underlying data type has a size. A field that can contain a maximum of 100 characters will report back a Character_Maximum_Length value of 100. A numeric field, by contrast, doesn’t have a field size. Thus you’ll get back, well, nothing.

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

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment