Hey, Scripting Guy! How Can I Add a Record to a Database Using Windows PowerShell?

ScriptingGuy1

Hey, Scripting Guy! Awhile back you showed us how we could use Windows PowerShell to retrieve data from a Microsoft Access database. I’m now trying to add data to a database using PowerShell, but I can’t get the script to work. Any chance you could show me how to add a record to a database using Windows PowerShell?

— AB

Hey, AB. Before we start we’d like to show everyone the nice little note we received last Friday (January 11^th) from Joel Thoreson:

“Just wanted to wish you all a Happy Scripting Guy’s day this fine 11^th of January!”

Thanks, Joel. Needless to say, we truly appreciate the fact that someone remembered that January 11^th was Scripting Guys Day; even the Scripting Mothers failed to call and wish us a happy Scripting Guys Day. Did that hurt our feelings? You bet it did. And still does.

Now, we realize that some of you are thinking, “What are they talking about? Scripting Guys Day? No way. Scripting Guys Day is not January 11^th.” As it turns out, though, Scripting Guys Day is January 11^th; after all, take a look at this note from our December 20, 2005 column:

Hey, if it’s in Hey, Scripting Guy! then it must be true. Case closed.

Anyway, thanks again, Joel; a Happy Scripting Guys Day to you, too. As for the rest of you, well, don’t feel guilty; after all, there’s still plenty of time to send your favorite Scripting Guy a present. Just send the package to:

The Scripting Guy Who Writes That Column
Microsoft Corporation
One Microsoft Way
Building 42/4039
Redmond, WA 99352

Remember what they say: better late than never.

In the spirit of the holidays, we thought we’d take time out from our Scripting Guys Day party to show you how to add a new record to an Access database using Windows PowerShell. (Or, for that matter, how you can add a record to any other database that uses ADO [ActiveX Data Objects].) We know that many of you have grabbed a VBScript script that uses ADO and tried to convert it to a Windows PowerShell script that performs the same task. That can be done; in fact, we’re just about to do it. It’s just that there are a couple of “gotchas” that you need to watch out for.

And yes, as a matter of fact we are going to explain what those gotchas are.

Even to those of you who forgot that it was Scripting Guys Day.

Here’s the script:

$adOpenStatic = 3
$adLockOptimistic = 3

$objConnection = New-Object -com "ADODB.Connection"
$objRecordSet = New-Object -com "ADODB.Recordset"

$objConnection.Open("Provider = Microsoft.Jet.OLEDB.4.0; Data Source = C:\Scripts\Test.mdb")

$objRecordset.Open("Select * From Computers", $objConnection,$adOpenStatic,$adLockOptimistic)

$objRecordSet.AddNew()
$objRecordSet.Fields.Item("ComputerName").Value = "atl-ws-001"
$objRecordSet.Fields.Item("SerialNumber").Value = "192ATG43R"
$objRecordSet.Update()

$objRecordSet.Close()
$objConnection.Close()

So how does this script work? Well, let’s see what we have here. To begin with, we assign values to a pair of variables $adOpenStatic and $adLockOptimistic:

$adOpenStatic = 3
$adLockOptimistic = 3

We won’t talk about these variables in any detail today; suffice to say that they help determine the type of cursor and the type of record locking employed when we open our database table. (If you’d like a little more information than that, take a peek at the Scripting Guys webcast Database Scripting For System Administrators.) After initializing our two variables, we then create instances of the ADODB.Connection and ADODB.Recordset objects:

$objConnection = New-Object -com "ADODB.Connection"
$objRecordSet = New-Object -com "ADODB.Recordset"

In case you’re wondering, the Connection object is used to manage the connection to the actual database file (in this case, C:\Scripts\Test.mdb). After we’ve made the connection the Recordset object is then used to help us work with a specified set of records in that database; for today’s script, that’s going to be all the records found in a table named Computers.

Once we have these two objects in hand we can then use this line of code to connect to Test.mdb:

$objConnection.Open("Provider = Microsoft.Jet.OLEDB.4.0; Data Source = C:\Scripts\Test.mdb")

Actually, we need to clarify that a little. The preceding command connects us to Test.mdb provided that Test.mdb is an Access 2003 (or Access XP) database. If Test.mdb happens to be an Access 2007 database, well, that command isn’t going to help us much; that’s because Access 2007 uses a different Provider string and a different file extension. Working with an Access 2007 database? Then use this line of code instead:

$objConnection.Open("Provider = Microsoft.ACE.OLEDB.12.0; Data Source = C:\Scripts\Test.accdb")

And no, we don’t know why they changed the Provider string and the file extension. For some reason the Office team simply makes major changes like these without ever asking the Scripting Guys for their opinion.

Go figure.

After we make the connection to the database we then use the following line of code to return a recordset consisting of all the data found in the Computers table:

$objRecordset.Open("Select * From Computers", $objConnection,$adOpenStatic,$adLockOptimistic)

Believe it or not, we’re now ready to add a record to our database; in fact, that’s what this block of code is for:

$objRecordSet.AddNew()
$objRecordSet.Fields.Item("ComputerName").Value = "atl-ws-001"
$objRecordSet.Fields.Item("SerialNumber").Value = "192ATG43R"
$objRecordSet.Update()

What’s going on here? Well, for starters, we use the AddNew method to create a new, blank record. (Again, note the parentheses that follow the method name.) That brings us to this line of code:

$objRecordSet.Fields.Item("ComputerName").Value = "atl-ws-001"

We’re willing to bet, AB, that this is the line that’s been causing you the most trouble. In this line of code we’re assigning a value (atl-ws-001) to the ComputerName field. This isn’t a particularly troublesome line of code unless you’re trying to port a VBScript script. That’s because, in VBScript, this same line of code looks like this:

objRecordSet("ComputerName") = "atl-ws-001"

In VBScript all we have to do is specify the object reference (objRecordset) followed by a pair of parentheses containing the name of the field (ComputerName). But remember, that’s in VBScript. In Windows PowerShell that simplified command won’t work; if we try something similar we merely get an error message like this one:

Unexpected token '(' in expression or statement.
At C:\scripts\test.ps1:11 char:15
+ $objRecordSet(" <<<< ComputerName") = "atl-ws-001"

To tell you the truth, we don’t know exactly why this problem occurs. We do know that, unless you specify otherwise, VBScript automatically gives you the default property for any object; most other languages (including Windows PowerShell) make you explicitly spell out that property, default property or not. We’re guessing that Fields.Item.(Field name).Value is the default property for our recordset; that would explain why VBScript only needs the field name while PowerShell requires the complete property name.

Of course, like we said, that’s just a guess, and there’s a good chance we’re completely wrong. But that’s OK. After all, it doesn’t matter why you have to “spell out” the complete property name; the only thing that matters is that you follow this same approach. With that in mind, how would we assign a value to the SerialNumber field? You got it:

$objRecordSet.Fields.Item("SerialNumber").Value = "192ATG43R"

From there, of course, we could continue to assign properties to as many fields as there are in the Computers table. Once we’re done assigning properties we then call the Update method to actually write the new record to the database:

$objRecordSet.Update()

After that all we have to do is close the Connection and Recordset objects, and then return to our Scripting Guys Day festivities.

That should do the trick, AB. We hope you find this script useful, and we hope you enjoy what’s left of the Scripting Guys Day holiday season. Speaking of which, we should also note that we aren’t upset or disappointed that everyone except Joel forgot about Scripting Guys Day this year. That’s no big deal, and there are no hard feelings on our end.

In all fairness, though, we must admit that Scripting Guys Day does come at a bad time of year; after all, having a major holiday like Scripting Guys Day follow so closely on the heels of a major holiday like Christmas can be difficult. Then when you throw in the fact that January 11^th is also the birthday of singer/songwriter Mary J. Blige; one-time Major League shortstop Rey Ordonez; and former Canadian Prime Minister Jean Chretien, well, it’s no wonder people get confused. Tell you what: go ahead and send your presents in now (even though you’re a little late), and we’ll see what we can do about moving Scripting Guys Day to a new date for 2009.

ScriptingGuy1

Follow