ADODB RECORDSET

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. We can’t promise to answer all the questions we receive, but we’ll do our best.

Adding New Records to and Modifying Existing Records in a Microsoft Access Database
We’ve decided to let you in on a little secret: the Scripting Guys (unlike the rest of you, of course) have their good days and their bad days. Some days we come in excited and ready to work; other days (well, OK, most days) it’s a bit different. How are you, the readers of the Office Space column, supposed to know whether you’ve hit the Scripting Guys on a good day or a bad day? Well, here’s a hint: any time the Scripting Guys decide to “expand upon a previous column” then you know they’re taking the lazy way out and not even trying to think up something new.
So what does that have to do with this week’s column? Why, nothing, nothing at all…. Since you mentioned it, though, this week we’ve decided to expand upon a previous column, one in which we showed you how to connect to and read from a Microsoft Access database. Today we’re going to take the next logical step and show you how you can add records to an Access database. And then, just for the heck of it, we’ll take another step and show you how to modify existing records as well.
OK, we admit it: we didn’t exactly knock ourselves out trying to come up with a brand-new topic for this week’s column. But we’ll rationalize that by pointing out that, as useful as it is to be able to extract data from a database, it’s just as useful (if not more so) to be able to add data to that database. So while we are being a tad bit lazy at least we’re covering an important topic. Besides, you can simply think of this as the second part in a multi-part series: you don’t have to worry that we’re just taking the easy way out!
Seeing as how we are being a tad bit lazy, we won’t review any of basic concepts behind connecting to an Access database; if you need a brief refresher on that, take a look at last week’s column. Today we’re going to get right down to business: let’s show you how to add a new record to an existing database.
To begin with, we’re assuming you have an Access database named Inventory.mdb living in the C:\Scripts folder. We’re also assuming that this database has a table named GeneralProperties, and that the GeneralProperties table includes the following fields:
• ComputerName
• Department
• OperatingSystem
• Owner
Needless to say, if you don’t have such a database then none of these sample scripts will work. That doesn’t mean you can’t learn anything from them; you just won’t be able to run them and get them to do anything.
With that in mind, let’s take a look at a script that adds a new record to the database:
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.AddNew
objRecordSet("ComputerName") = "atl-ws-99"
objRecordSet("Department") = "Human Resources"
objRecordSet("OperatingSystem") = "Microsoft Windows XP Professional"
objRecordSet("Owner") = "Ken Myer"
objRecordSet.Update

objRecordSet.Close
objConnection.Close
Note. We should note that this is not the only way to add a new record to a database. For example, SQL aficionados can also use an Insert Into query. We’re using the AddNew method simply because it’s easier; Insert Into queries can get extremely complicated, especially when you’re dealing with many fields, values stored in variables, and fields of different data types. In the lazy spirit of the day, we’re taking the easy way out.
The script starts out by connecting to the database C:\Scripts\Inventory.mdb. Like we said, we won’t discuss this in any detail, but that’s what these lines of code are for:
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"
After making the connection we then use the Recordset object’s Open method to run a SQL query that retrieves all the records from the GeneralProperties table; that’s what we do here:
objRecordSet.Open "SELECT * FROM GeneralProperties" , _
objConnection, adOpenStatic, adLockOptimistic
What gives us is a recordset that we can add a new record to. And that’s what we do here:
objRecordSet.AddNew
objRecordSet("ComputerName") = "atl-ws-99"
objRecordSet("Department") = "Human Resources"
objRecordSet("OperatingSystem") = "Microsoft Windows XP Professional"
objRecordSet("Owner") = "Ken Myer"
objRecordSet.Update
We begin by calling the AddNew method; this sets up a blank template to work with, but doesn’t actually add anything to the database. (At the moment, all the work we’re doing is taking place solely in memory.) We then have a series of lines that assign values to the individual fields for this new record. (These fields, of course, are the fields found in the table GeneralProperties.) For example, this line sets the value of the ComputerName field to atl-ws-99:
objRecordSet("ComputerName") = "atl-ws-99"
Note that the value being assigned (atl-ws-99) is enclosed in double quote marks; that’s because ComputerName is a text field and is expecting to be assigned a string value. If a database field is numeric or Boolean (true/false) then you should not enclose the values in double quote marks:
objRecordSet("IsLaptop") = False
objRecordSet("NumberOfPrcoessors") = 2
But you knew that already, didn’t you?
Finally, we call the Update method, which actually writes the new record to the database:
objRecordSet.Update
Don’t forget that line of code: if you do, the recordset in memory will be updated to contain the new record, but the actual database will remain unchanged.
As you can see, adding a new record to an Access database is pretty easy. But what about modifying an existing record? For example, suppose we no sooner add the computer atl-ws-99 to the database then Ken Myer gets transferred from the Human Resources department to the Finance department. That means we now have to update the department field for that particular computer. How hard is that going to be?
Relax: it’s not going to be hard at all. Again, there are different ways to update records in a database, but for now we’ll focus on what we feel is the easiest way to update a record: you search for the record, update the appropriate field (or fields), and then call the Update method. In other words, a process that looks remarkably like this:
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

strCriteria = "ComputerName = 'atl-ws-99'"

objRecordSet.Find strCriteria
objRecordset.Fields.Item("Department") = "Finance"
objRecordset.Update

objRecordSet.Close
objConnection.Close
You probably already noticed that the first half of this script is identical to the script that adds a new record: we connect to the database, then use the Open method to return a recordset consisting of all the records in the GeneralProperties table. In fact, you won’t find any differences between the two scripts until we get to here:
strCriteria = "ComputerName = 'atl-ws-99'"
In this line of code we’re simply assigning the search criteria to a variable named strCriteria. We want to update the record for the computer named atl-ws-99; as you might expect, that becomes our search criteria, using the format FieldName = Value. Or, ComputerName = ‘atl-ws-99’.
After establishing the search criteria we call the Find method to locate that record in the recordset:
objRecordSet.Find strCriteria
We then use these two lines of code to update the Department field and then to save the changes:
objRecordset.Fields.Item("Department") = "Finance"
objRecordset.Update
Note the somewhat unusual syntax we use when referring to the Department field: objRecordset.Fields.Item(“Department”). Don’t worry too much about that; that’s just the way you have to do things. On the bright side, odd as it might look, at least it’s the same way we refer to fields when reading from a database (as we did last week).
Also, don’t overlook the Update method; once again a record will not be changed in the database until you call Update.
As we said, there are other ways we can modify existing records; however, those will have to wait for another day. Which day? Let’s put it this way: the next time we’re too lazy to think up a brand-new topic for this column, well, you can probably figure out for yourself what the topic will be.
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 Tuesday and 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. We can’t promise to answer all the questions we receive, but we’ll do our best.

Using ADO to Query an Excel Spreadsheet
A month or so ago CBS aired the final episode of the beloved sitcom Everybody Loves Raymond, a show that had been ranked No. 1 in viewership for hundreds of years, despite the fact that at least one of the Scripting Guys never caught even the briefest glimpse of the program. (Of course, cultural literacy isn’t exactly this Scripting Guy’s forte: he’s never seen American Idol or Survivor either, and has absolutely no idea which one is Nelly and which one is P Diddy.) With Raymond gone CBS has a void to fill in its schedule, but the rest of the world has an empty spot to fill as well: now who are we all supposed to love?
Personally, we’d like to nominate Microsoft Excel as a candidate to fill that void. After all, what’s not to love about Excel? Do you need a traditional spreadsheet program, something that can help you with budgets, accounting and other financial-type things? Excel can do that. Do you need to make charts and graphs? Excel can do that. Create an organizational chart? Calculate statistics? Excel has you covered; Excel can do anything.
Of course, some of you might not be convinced of that. “Excel can do anything?” you muse. “Well, how about this: can Excel function as a flat-file database? Can you use ADO to access the information found in an Excel spreadsheet? Can you send SQL queries to Excel and get back information?”
We’ll put it this way: the Office Space column is published on the Internet, and obviously something couldn’t be published on the Internet if it wasn’t 100% true. Of course Excel can function as a flat-file database that can be accessed using ADO and SQL queries. And in today’s column we’ll explain how to do just that.
Let’s take a look at a very simple spreadsheet and then explain how we can access the data using ADO (ActiveX Data Objects). Here’s a rudimentary spreadsheet – named C:\Scripts\Test.xls – that consists of two columns, one labeled Name, the other labeled Number. To better ensure that your database queries work against an Excel spreadsheet, make sure your spreadsheets are set up in a similar fashion; that is, make row 1 a header row, start the data itself in row 2, and don’t skip any rows or columns. And to make coding easier, don’t include blank spaces in your headers; for example, use a column header like SocialSecurityNumber and don’t use a column header like Social Security Number. It just makes life much easier. (Incidentally, that’s true when using any kind of database, not just Excel.)
Here’s what our spreadsheet looks like:


And, yes, we did put a lot of time and effort into creating this sample spreadsheet. All part of the service we offer.
But now for the big question: how do we access this data using ADO? Well, one way is to use a script that looks like this:
On Error Resume Next

Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H0001

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

objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Scripts\Test.xls;" & _
"Extended Properties=""Excel 8.0;HDR=Yes;"";"

objRecordset.Open "Select * FROM [Sheet1$]", _
objConnection, adOpenStatic, adLockOptimistic, adCmdText

Do Until objRecordset.EOF
Wscript.Echo objRecordset.Fields.Item("Name"), _
objRecordset.Fields.Item("Number")
objRecordset.MoveNext
Loop
We should probably start by pointing out that the first half of the script simply defines some constants and creates the two objects – ADODB.Connection and ADODB.Recordset – that are required to connect to and retrieve data from a data source. This is largely boilerplate code that you use as-is in any ADO script. And because this is boilerplate you can use as-is we won’t discuss the first half of the script in any detail; for more information you might take a look at the Working with Databases section of the Microsoft Windows 2000 Scripting Guide.
We’ll begin our discussion with this bit of code, which actually opens a connection to the Excel spreadsheet:
objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Scripts\Test.xls;" & _
"Extended Properties=""Excel 8.0;HDR=Yes;"";"
Again, this is largely boilerplate code. The only section you need to worry about is the Data Source, where you specify the path to your spreadsheet.
Note. What if the path to your spreadsheet includes spaces, something that can cause problems for ADO scripts working with text files (for more information, see this Hey, Scripting Guy! column)? In this case, there’s no problem whatsoever; just write out the entire file path, spaces and all:
Data Source=C:\Scripts\My Spreadsheet.xls
Incidentally, you should resist the temptation to change Excel 8.0 to whatever version of Excel you happen to be running on your machine. In this case the Excel 8.0 refers not to your version of Excel but to the ADO provider used to access Excel. Leave the provider as Excel 8.0 and everything will be fine.
As long as we’re on the subject we should also mention that the code HDR=Yes simply indicates that our spreadsheet has a header row; if our spreadsheet didn’t have a header row we’d set HDR to No. But seeing as how we said you should always have a header row and seeing as how people always do what the Scripting Guys tell them to, well, this is a moot point.
After making a connection to the data source we can use a SQL query to retrieve the information stored in that data source. Here’s the code we use to return a recordset consisting of all the rows in the spreadsheet:
objRecordset.Open "Select * FROM [Sheet1$]", _
objConnection, adOpenStatic, adLockOptimistic, adCmdText
Again, there’s a lot of ADO boilerplate in here; the only part we’re really concerned about for now is the actual SQL query:
Select * FROM [Sheet1$]
This is a standard SQL query, one that selects all the fields (columns) in the database (worksheet). Notice that we specify the name of an individual worksheet in the query, the same way that we would specify an individual table name were we connecting to a database. Notice, too, that the worksheet name is enclosed in square brackets and that the actual name – Sheet1 – has a $ appended to it. Make sure you do both those things when writing your own ADO scripts for accessing data in a spreadsheet.
The recordset returned to us is exactly the same sort of thing we’d get back were we making a connection to, say SQL Server. Because of that we can use these lines of code to simply report back the Name and Number for each record in the recordset, something that corresponds to each row in our spreadsheet:
Do Until objRecordset.EOF
Wscript.Echo objRecordset.Fields.Item("Name"), _
objRecordset.Fields.Item("Number")
objRecordset.MoveNext
Loop
When we run the script we should get back a report that looks like this:
A 1
B 1
C 2
D 2
E 1
F 1
Yes, very cool, but some of you are skeptical: after all, couldn’t we retrieve this same information using a plain-old Excel script? Yes, we could, which means that in this case, there’s really no reason to use ADO at all.
So then why did we even bother writing this column? (That, by the way, is something we get asked pretty much any time we write a column.) Well, we admit it: if all you want to do is echo back every row in the spreadsheet there’s no point in using ADO (unless you’re more comfortable writing ADO scripts than you are writing Excel scripts). However, suppose we wanted to echo back only the rows where Number was equal to 2. Using a standard Excel script we’d need to examine each row, determine whether the Number column was equal to 2, and then echo (or not echo) the row based on that value. This isn’t necessarily hard, but it can be a bit cumbersome, especially if you need to look at values in more than one column (for example, if you were looking for all users with the title Administrative Assistant who work for the Finance department).
With ADO, by contrast, we can get back this same information without having to individually examine each row in the spreadsheet. In fact, we can use pretty much the exact same script we just showed you; all we have to do is modify the SQL query. Because we want only those rows where the Number is equal to 2 we just need to use a SQL query like this:
objRecordset.Open "Select * FROM [Sheet1$] Where Number = 2", _
objConnection, adOpenStatic, adLockOptimistic, adCmdText
That’s it; the preceding query will give us back data like this:
C 2
D 2
Why use ADO? There’s your answer: because you can easily use SQL queries to return a subset of the information found in the spreadsheet. That’s why you might want to use ADO to access an Excel file.
By the way, here’s a complete script that returns just the rows where the Number column is equal to 2:
On Error Resume Next

Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H0001

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

objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Scripts\Test.xls;" & _
"Extended Properties=""Excel 8.0;HDR=Yes;"";"

objRecordset.Open "Select * FROM [Sheet1$] Where Number = 2", _
objConnection, adOpenStatic, adLockOptimistic, adCmdText

Do Until objRecordset.EOF
Wscript.Echo objRecordset.Fields.Item("Name"), _
objRecordset.Fields.Item("Number")
objRecordset.MoveNext
Loop
Before you ask, yes, there are other things you can do with ADO besides simply retrieve data. In a future column we’ll show you how you can use ADO to write data to a spreadsheet.
Hey, is it any wonder why everyone loves Excel?