Office Space: Tips and Tricks for Scripting Microsoft Office Applications

Welcome to Office Space, the column that offers tips and tricks for scripting Microsoft® Office applications. We’ll post new tips every Thursday; to see an archive of previous tips, visit the Office Space Archive. And if you have particular questions about Microsoft Office scripting, feel free to send them to scripter@microsoft.com (in English, if possible). We can’t promise to answer all the questions we receive, but we’ll do our best.

Reading Data from a Microsoft Access Database

You know, every family has one, the family member who’s just a little bit different. Not necessarily good or bad, mind you; just different. Maybe it’s the redhead in a family of brunettes or the vegetarian in the family that runs the barbecue restaurant. Maybe it’s the stellar athlete in the family of bookworms, or the Scripting Guy who actually works hard. Regardless, there’s always one family member people look at and say, “That’s your sister? I never would have guessed that.”

In the Microsoft Office family that family member would have to be Microsoft Access. With other Office applications – particularly Word and Excel – you can use scripts to programmatically add data to documents; the only drawback is that you have to use a proprietary object model and proprietary methods to do so. That doesn’t mean it’s hard, it just means that – with Word and Excel – you have to learn two object models: knowing how to add data to a Word document doesn’t really help you that much when it comes time to add data to an Excel spreadsheet.

Microsoft Access, however, is different. Access has its own object model, but when it comes to basic database tasks like reading from and writing to tables you typically don’t even use that object model. Instead, you rely on ADO: ActiveX Data Objects. And that’s actually a huge plus. Why? Well, because ADO is a standard programming model that can be used with any number of database types. Got a SQL Server database? No problem: the code you use with Access will work with SQL Server, most likely without any tweaking required. Got an Oracle database? Same thing: standard ADO code can be used to access an Oracle database. Visual FoxPro? DB2? AS 400? MySQL? Ah, you guessed it: ADO can also be used to work with those databases, to name just a few.

With that in mind, we thought we’d spend some time introducing you to ADO and, in particular, showing you how to use ADO to work with Access databases. We’ll start with the basics today – connecting to an Access database and retrieving information – and then move on to other tasks, such as adding new records, modifying existing records, creating and deleting tables, and, well, whatever else we can think of.

Let’s start out by looking at a script that reads data from a Microsoft Access database. This particular script connects to the file C:\Scripts\Inventory.mdb, grabs information from a table named GeneralProperties, and then reports back the value of the ComputerName field for each record in the table:

On Error Resume Next

Const adOpenStatic = 3
Const adLockOptimistic = 3

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

objConnection.Open _
    "Provider=Microsoft.Jet.OLEDB.4.0; " & _
        "Data Source=c:\scripts\inventory.mdb" 

objRecordSet.Open "SELECT * FROM GeneralProperties" , _
    objConnection, adOpenStatic, adLockOptimistic

objRecordSet.MoveFirst

Do Until objRecordSet.EOF
    Wscript.Echo objRecordSet.Fields.Item("ComputerName")
    objRecordSet.MoveNext
Loop

As you can see, the script itself is actually pretty small (and most of the code that is there is boilerplate code that never has to change). And while some of the lines of code might be a little cryptic-looking, don’t worry about it: we’re going to explain exactly how this works, and why.

No problem: that’s what they pay us for.

The script begins by defining a pair of constants – adOpenStatic and adLockOptimistic – and setting the value of each of these constants to 3.

The constant adOpenStatic is used to determine the “cursor type” for the recordset. For now, we’ll just say that the cursor type determines how you can maneuver through the recordset, and whether or not the recordset dynamically responds to changes in the database. (That is, suppose you retrieve information from the database and then someone else adds a record to that database. Will that record dynamically show up in your recordset, or will you have to re-query the database in order to get updated information?)

The adOpenStatic cursor allows you to maneuver forwards and backwards in the recordset, but does not automatically refresh itself any time the database changes. That helps cut down on processing time and network traffic, because once the recordset is retrieved the script can just “relax” without having to constantly monitor the database for changes.

Cursor types, their constants, and their values are listed in the following table:

Our other constant – adLockOptimistic – is used to configure the record “locking type.” The locking type determines how (or whether) the database locks the record currently being viewed, thus giving you the exclusive right to modify that record. Locking types, their constants, and their values are shown in the following table:

After defining our constants we next create a pair of objects: the ADODB.Connection object and the ADODB.Recordset object. Both of these objects are aptly-named: the Connection object is responsible for managing and maintaining the connection to the database, and the Recordset object is used as a container for the data returned by our query. Creating these two objects is as simple as running the following lines of code:

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

Once we have a Connection object we can then make a connection to the database. In today’s article we’ll bind to the database using a file path; in a future article, we’ll show you how to bind to a database using a DSN (Data Source Name). For now, here’s the code that connects you to the C:\Scripts\Inventory.mdb database:

objConnection.Open _
    "Provider=Microsoft.Jet.OLEDB.4.0; " & _
        "Data Source=c:\scripts\inventory.mdb"

Pretty easy, huh? All we do is call the Open method and pass it a pair of parameters: the name of the Provider (Microsoft.Jet.OLEDB.4.0) and the Data Source (or, in this case, the file path). Run that code, and you’ll be connected to the Inventory.mdb database.

Once you make the connection you’ll interact with the database by issuing SQL queries. We’ll start with a very basic query: we want to select all the properties from a table named GeneralProperties. That means our SQL query will look like this:

SELECT * FROM GeneralProperties

We then use that query in conjunction with the Recordset object and the Open method:

objRecordSet.Open "SELECT * FROM GeneralProperties" , _
    objConnection, adOpenStatic, adLockOptimistic

As you can see, we specify four parameters here:

• The SQL query. (And, yes, you can use Update queries, Insert Into queries, and other query types besides Select queries.)

• The object reference for the database connection (objConnection).

• The type of cursor we want to use (a static cursor, indicated by the constant adOpenStatic).

• The type of locking we want to use (optimistic, indicated by the constant adLockOptimistic).

After issuing this command the data we get back will be stored, in memory, in the Recordset object. To walk through the returned collection we need to do two things: call the MoveFirst method, and set up a Do Until loop to actually loop through all the records in the recordset.

We use the MoveFirst method to position the cursor at the beginning of the recordset. To be honest, we aren’t convinced that you have to do this; as near as we can tell the cursor is automatically positioned at the beginning of the recordset for you. But, better safe than sorry.

Incidentally, “positioning the cursor at the beginning of the recordset” simply means pointing to the very first record in the collection: the cursor is used to keep rack of your position in the recordset. All we’re doing here is making sure that, when we start walking through the collection, we’re beginning with record 1. If for some reason we wanted to access the very last record in the recordset we could call the MoveLast method. And, of course, there is also a Find method which lets you quickly search the recordset for any record. But, we’re getting way ahead of ourselves here.

SELECT * FROM GeneralProperties ORDER BY Manufacturer ASC

SELECT * FROM GeneralProperties ORDER BY Manufacturer ASC, ComputerName ASC

Next we set up a Do Until loop that enables us to walk through the collection record-by-record:

Do Until objRecordSet.EOF
    Wscript.Echo objRecordSet.Fields.Item("ComputerName")
    objRecordSet.MoveNext
Loop

This is where budding script writers often run into problems, so take note of a few key points here.

That’s OK; we’ll wait while you get some paper. Sorry, no: we don’t have enough pencils for everyone. That guy over there has some extras.

OK, ready to go? First, notice that the Do Until loop runs until objRecordset.EOF is true. The EOF is short for “End Of File,” and simply means that we should start with the first record in the recordset and keep going until we run out of records. At that point the script will automatically exit the loop.

One thing to keep in mind here is that ADO generates an error if you attempt to walk through a recordset that contains zero items. You can use the On Error Resume Next statement to simply shrug off that error. Alternatively, you can check the value of the RecordCount property (which indicates the number of records in a recordset) before entering the Do Until loop. If RecordCount is equal to 0 then the recordset contains no records, and there’s no point trying to run through the Do Until loop. To take action based on the value of the RecordCount you might want to modify your script to look like this:

If objRecordset.RecordCount <> 0 Then
    Do Until objRecordSet.EOF
        Wscript.Echo objRecordSet.Fields.Item("ComputerName")
        objRecordSet.MoveNext
    Loop
End If

Here’s another important point. If you’ve written a lot of WMI or ADSI scripts then you’ve probably used your fair share of For Each loops in order to walk through collections. For Each loops are really nice because they’re smart: a For Each loop “knows” that it needs to look at each and every item in the collection. For example, this is all the code you need to walk through the colServices collection and echo back the name of each and every service:

For Each objService in colServices
    Wscript.Echo objService.Name
Next

The Do Until loop isn’t quite as smart; we’re stuck with it, however, because the For Each loop won’t work with a returned recordset. Just remember this: the Do Until loop will not automatically walk through each record in the recordset. Instead, you have to include code that specifically tells the script to move on to the next record. That’s what the MoveNext method is for:

objRecordSet.MoveNext

What happens if you leave out this line of code? Well, the script will start with record 1. It will reach the Loop statement, loop around, and work with record 1 again. It will reach the Loop statement, loop around, and work with record 1 yet again. This will continue forever and ever. In other words, it’s crucial that you call the MoveNext method before you loop back around. It’s the only way to get at every record in the recordset.

Finally, we need to understand how to reference a database field within our Do Until loop:

Wscript.Echo objRecordSet.Fields.Item("ComputerName")

As you can see, we can’t just echo the field name; this won’t work:

Wscript.Echo ComputerName

Instead, we need to provide the complete object path: objRecordSet.Fields.Item("ComputerName"). It looks a little complicated, but, remember, most of this is boilerplate. The only part you need to change is the field name itself: Wscript.Echo objRecordSet.Fields.Item("ComputerName"). It’s a little extra typing, but no more difficult than echoing back just the field name.

Now, run your script from the command prompt and, depending on the records stored in your database, you’ll get back a list of computer names:

atl-ws-01
atl-ws-02
atl-ws-03
atl-ws-04
atl-ws-05

It’s a thing of beauty, isn’t it?

We know what you’re thinking: all that just to read a few computer names out of a database? Admittedly, we didn’t do anything earth-shattering today. But that’s primarily because we wanted to explain a lot of the basics: what the cursor and lock type constants are for, how to move through a recordset, how to reference a field in the recordset. Now that those things are out of the way we’re ready to have some real fun.

Uh, no, sorry: we meant real database fun. But that’s almost as good as what you were thinking.