October 2nd, 2006

How Can I Use Windows PowerShell to Pull Records From a Microsoft Access Database?

Hey, Scripting Guy! Question

Hey, Scripting Guy! How can I use Windows PowerShell to pull records from a Microsoft Access database?

— SB

SpacerHey, Scripting Guy! AnswerScript Center

Hey, SB. You know, you’re lucky to get this question answered; after all, this looks as though it’s going to be the very last Hey, Scripting Guy! column ever.

To be honest, that came as a bit of a surprise to us, too: we hadn’t planned on retiring this early. (Although our manager does suggest that to each of the Scripting Guys on a daily basis.) But that was before we received an email this morning informing us that we had won a prize of € 1 million (one million euros) in the Spanish Lottery Winners International E-mail Program. And as much as we like working here, well, it just doesn’t seem right for a millionaire to be getting up at 6:00 every morning in order to be to work by 7:00, does it?

Note. You think it’s lucky that we won the contest, even without entering it? That’s nothing. After all, this is the fourth or fifth time this year that the Scripting Guy who writes this column has won the Spanish lottery (or, to quote from the email, the “Spainsh” lottery). Amazingly enough, he’s managed to do this even though the lottery is only held once every three years. You want to talk lucky? That’s lucky!

To tell you the truth, we weren’t even going to write today’s column; it’s hard to focus on scripting when you now have to worry about where to buy your caviar and how many forks you need for a formal dinner. On the other hand, the email did note that there has been a “mix up of some numbers and names”, which means it will take a little bit of time to get everything sorted out. In turn, that means we have a little time to kill. With that in mind, we might as well tell you how to use Windows PowerShell to pull records from a Microsoft Access database. One last Hey, Scripting Guy! column, for old time’s sake.

Let’s take a look at the script, then see if we can figure out how it works:

$adOpenStatic = 3
$adLockOptimistic = 3

$objConnection = New-Object -comobject ADODB.Connection $objRecordset = New-Object -comobject ADODB.Recordset

$objConnection.Open(“Provider = Microsoft.Jet.OLEDB.4.0; Data Source = c:\scripts\sample.mdb”) $objRecordset.Open(“Select * from TotalSales”, $objConnection,$adOpenStatic,$adLockOptimistic)

$objRecordset.MoveFirst()

do {$objRecordset.Fields.Item(“EmployeeName”).Value; $objRecordset.MoveNext()} until ($objRecordset.EOF -eq $True)

$objRecordset.Close() $objConnection.Close()

As you can see, we start out simple enough, assigning the value 3 to two different variables: $adOpenStatic and $adLockOptimistic. (If you’re new to Windows PowerShell and are wondering about those variable names there’s reason all our variable names start out with a $: that’s how variable names have to start.) We aren’t going to discuss the nuts-and-bolts of doing ADODB (ActiveX Database Objects) scripting today; for more information, see this classic Scripting Guys webcast. Instead, we’ll just say that we’ll use these two variables when we finally go out and retrieve data from the database.

After assigning values to the two variables we then use the New-Object Cmdlet to create a pair of object references, one for the ADODB.Connection object, the other for the ADODB.Recordset object:

$objConnection = New-Object -comobject ADODB.Connection
$objRecordset = New-Object -comobject ADODB.Recordset

As you can see, New-Object serves the same purpose as VBScript’s CreateObject method. In fact, this is the VBScript equivalent for creating a Connection object:

Set objConnection = CreateObject(“ADODB.Connection”)

A different syntax, obviously, but some definite similarities as well.

Once we have our two objects in hand we can connect to the database (C:\Scripts\Sample.mdb) and then query a table in that database. In our sample script, we’re selecting all the records from a table named TotalSales, something we do with this line of code:

$objRecordset.Open(“Select * from TotalSales”, $objConnection,$adOpenStatic,$adLockOptimistic)

And you’re right: this is very much like opening a recordset using VBScript. About the only real difference (aside from the variable names) is the fact that, in VBScript, we don’t put parentheses around the parameters passed to the Open method:

objRecordSet.Open “Select * From TotalSales”, _
    objConnection, adOpenStatic, adLockOptimistic

But that’s in VBScript. In Windows PowerShell you must always use parentheses when calling a method, even if a method doesn’t require a parameter. For example, suppose we could just call the Open method without including query information. In that case our code would look like this, with the method followed by an empty set of parentheses:

$objRecordset.Open()

With the recordset open we call the MoveFirst method (what did we tell you: note the empty parentheses) to position the cursor at the beginning of the recordset. That brings us to this block of code:

do {$objRecordset.Fields.Item(“EmployeeName”).Value; $objRecordset.MoveNext()} until 
    ($objRecordset.EOF -eq $True)

What we’re doing here is creating a Do Until loop that loops until we reach the end of our recordset. To explain how this works, note that we first employ the keyword do followed by the actions we want to perform, actions that need to be enclosed in curly braces. We want to do two things within our loop. First, we want to echo back the value of the EmployeeName field; that’s what this snippet of code is for:

$objRecordset.Fields.Item(“EmployeeName”).Value

And yes, unlike VBScript you must tack a .Value after the field name. Value is the default property when dealing with an item in a recordset; if you don’t explicitly specify a property name VBScript will automatically use the default property. That’s not the case with Windows PowerShell, however. Default property or not, you must still add the .Value.

After displaying the value for EmployeeName for the first record in the recordset, we then want to move on to the next record. Therefore, we call the MoveNext method using this snippet of code:

$objRecordset.MoveNext()

Notice that both our commands – one for echoing back the employee name, the other for moving to the next record – are enclosed within the curly braces. In addition, the two are separated by a semicolon. Needless to say, the semicolon is one way to separate commands in Windows PowerShell.

So much for the “do” portion; what about the “until” part? Well, after typing in the until keyword we use this code (enclosed in parentheses) to instruct the script to continue until the recordset’s EOF (end-of-file) property is true:

$objRecordset.EOF -eq $True

Two things to note here. First, we use the –eq comparison operator rather than the equals sign; that’s another Windows PowerShell convention you’ll need to get used to. Second, note the $ in front of the value True. Is that required, do we need to type Boolean values as $True and $False? You better believe it.

That’s really all we need to do. The script will loop through the recordset, displaying each employee name found in the table Total Sales. When the EOF property is true (that is, when we reach the end of the recordset) we’ll exit the Do loop, then use these two lines of code to close the recordset and the database connection:

$objRecordset.Close()
$objConnection.Close()

At that point, we’re done.

And when we say done, we mean that: after all, this is the last column. Well, unless it turns out that someone was – gasp! – trying to scam the Scripting Guys, and that we didn’t really win the Spanish lottery after all. But what do you suppose the odds are of that? We can’t imagine that anyone would ever use the Internet for anything but the most noble of purposes.

Author

0 comments

Discussion are closed.