{"id":66353,"date":"2006-10-02T14:30:00","date_gmt":"2006-10-02T14:30:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2006\/10\/02\/how-can-i-use-windows-powershell-to-pull-records-from-a-microsoft-access-database\/"},"modified":"2006-10-02T14:30:00","modified_gmt":"2006-10-02T14:30:00","slug":"how-can-i-use-windows-powershell-to-pull-records-from-a-microsoft-access-database","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/how-can-i-use-windows-powershell-to-pull-records-from-a-microsoft-access-database\/","title":{"rendered":"How Can I Use Windows PowerShell to Pull Records From a Microsoft Access Database?"},"content":{"rendered":"

\"Hey, \n

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

— SB<\/P>\"Spacer\"\"Hey,\"Script<\/A> \n

Hey, SB. You know, you\u2019re lucky to get this question answered; after all, this looks as though it\u2019s going to be the very last Hey, Scripting Guy!<\/I> column ever.<\/P>\n

To be honest, that came as a bit of a surprise to us, too: we hadn\u2019t planned on retiring this early. (Although our manager does<\/I> 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 \u20ac 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\u2019t 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?<\/P>\n\n<\/THEAD>\n\n\n
\n

Note<\/B>. You think it\u2019s lucky that we won the contest, even without entering it? That\u2019s 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 \u201cSpainsh\u201d lottery). Amazingly enough, he\u2019s managed to do this even though the lottery is only held once every three years. You want to talk lucky? That\u2019s<\/I> lucky!<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n

<\/DIV>\n

To tell you the truth, we weren\u2019t even going to write today\u2019s<\/I> column; it\u2019s 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<\/I> note that there has been a \u201cmix up of some numbers and names\u201d, 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!<\/I> column, for old time\u2019s sake.<\/P>\n

Let\u2019s take a look at the script, then see if we can figure out how it works:<\/P>

$adOpenStatic = 3\n$adLockOptimistic = 3<\/p>\n
$objConnection = New-Object -comobject ADODB.Connection\n$objRecordset = New-Object -comobject ADODB.Recordset<\/p>\n
$objConnection.Open(“Provider = Microsoft.Jet.OLEDB.4.0; Data Source = c:\\scripts\\sample.mdb”)\n$objRecordset.Open(“Select * from TotalSales”, $objConnection,$adOpenStatic,$adLockOptimistic)<\/p>\n
$objRecordset.MoveFirst()<\/p>\n
do {$objRecordset.Fields.Item(“EmployeeName”).Value; $objRecordset.MoveNext()} until \n    ($objRecordset.EOF -eq $True)<\/p>\n
$objRecordset.Close()\n$objConnection.Close()\n<\/PRE>\n
As you can see, we start out simple enough, assigning the value 3 to two different variables: $adOpenStatic and $adLockOptimistic. (If you\u2019re new to Windows PowerShell<\/B><\/A> and are wondering about those variable names there\u2019s reason all our variable names start out with a $: that\u2019s how variable names have<\/I> to start.) We aren\u2019t going to discuss the nuts-and-bolts of doing ADODB (ActiveX Database Objects) scripting today; for more information, see this classic Scripting Guys webcast<\/B><\/A>. Instead, we\u2019ll just say that we\u2019ll use these two variables when we finally go out and retrieve data from the database.<\/P>\n
After assigning values to the two variables we then use the New-Object<\/B> Cmdlet to create a pair of object references, one for the ADODB.Connection<\/B> object, the other for the ADODB.Recordset<\/B> object:<\/P>
$objConnection = New-Object -comobject ADODB.Connection\n$objRecordset = New-Object -comobject ADODB.Recordset\n<\/PRE>\n
As you can see, New-Object serves the same purpose as VBScript\u2019s CreateObject method. In fact, this is the VBScript equivalent for creating a Connection object:<\/P>
Set objConnection = CreateObject(“ADODB.Connection”)\n<\/PRE>\n
A different syntax, obviously, but some definite similarities as well.<\/P>\n
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\u2019re selecting all the records from a table named TotalSales, something we do with this line of code:<\/P>
$objRecordset.Open(“Select * from TotalSales”, $objConnection,$adOpenStatic,$adLockOptimistic)\n<\/PRE>\n
And you\u2019re right: this is<\/I> 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\u2019t put parentheses around the parameters passed to the Open <\/B>method:<\/P>
objRecordSet.Open “Select * From TotalSales”, _\n    objConnection, adOpenStatic, adLockOptimistic\n<\/PRE>\n
But that\u2019s in VBScript. In Windows PowerShell you must always<\/I> use parentheses when calling a method, even if a method doesn\u2019t 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:<\/P>
$objRecordset.Open()\n<\/PRE>\n
With the recordset open we call the MoveFirst<\/B> 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:<\/P>
do {$objRecordset.Fields.Item(“EmployeeName”).Value; $objRecordset.MoveNext()} until \n    ($objRecordset.EOF -eq $True)\n<\/PRE>\n
What we\u2019re 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<\/B> 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\u2019s what this snippet of code is for:<\/P>
$objRecordset.Fields.Item(“EmployeeName”).Value\n<\/PRE>\n
And yes, unlike VBScript you must tack a .Value<\/B> after the field name. Value is the default property when dealing with an item in a recordset; if you don\u2019t explicitly specify a property name VBScript will automatically use the default property. That\u2019s not the case with Windows PowerShell, however. Default property or not, you must still add the .Value<\/I>.<\/P>\n
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:<\/P>
$objRecordset.MoveNext()\n<\/PRE>\n
Notice that both our commands \u2013 one for echoing back the employee name, the other for moving to the next record \u2013 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.<\/P>\n
So much for the \u201cdo\u201d portion; what about the \u201cuntil\u201d part? Well, after typing in the until<\/B> keyword we use this code (enclosed in parentheses) to instruct the script to continue until the recordset\u2019s EOF (end-of-file) property is true:<\/P>
$objRecordset.EOF -eq $True\n<\/PRE>\n
Two things to note here. First, we use the \u2013eq<\/B> comparison operator rather than the equals sign; that\u2019s another Windows PowerShell convention you\u2019ll 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.<\/P>\n
That\u2019s 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\u2019ll exit the Do loop, then use these two lines of code to close the recordset and the database connection:<\/P>
$objRecordset.Close()\n$objConnection.Close()\n<\/PRE>\n
At that point, we\u2019re done.<\/P>\n
And when we say done, we mean that: after all, this is<\/I> the last column. Well, unless it turns out that someone was \u2013 gasp! \u2013 trying to scam the Scripting Guys, and that we didn\u2019t really win the Spanish lottery after all. But what do you suppose the odds are of that? We can\u2019t imagine that anyone would ever use the Internet for anything but the most noble of purposes.<\/P><\/p>\n","protected":false},"excerpt":{"rendered":"
Hey, Scripting Guy! How can I use Windows PowerShell to pull records from a Microsoft Access database?— SB Hey, SB. You know, you\u2019re lucky to get this question answered; after all, this looks as though it\u2019s going to be the very last Hey, Scripting Guy! column ever. To be honest, that came as a bit […]<\/p>\n","protected":false},"author":595,"featured_media":87096,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[19,146,54,49,3,45],"class_list":["post-66353","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-activex-data-objects-ado","tag-databases","tag-microsoft-access","tag-office","tag-scripting-guy","tag-windows-powershell"],"acf":[],"blog_post_summary":"
Hey, Scripting Guy! How can I use Windows PowerShell to pull records from a Microsoft Access database?— SB Hey, SB. You know, you\u2019re lucky to get this question answered; after all, this looks as though it\u2019s going to be the very last Hey, Scripting Guy! column ever. To be honest, that came as a bit […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/66353","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/users\/595"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/comments?post=66353"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/66353\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media\/87096"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media?parent=66353"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=66353"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=66353"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}