Accessing MS Access from PowerShell

I was trying to modify some scripts on MS Technet Script Guy's blog for working with PowerShell and MS Access.

Kept getting an error when trying to open the database, the example was using an .mdb file extension. I left my test file with the Office 2007 default of .accdb.

The example had the filename and path in a variable thought that was the problem at first then realized the connection string seemed to be referring to version 4.


I found that the ADO string need to be "Microsoft.ACE.OLEDB.12.0" rather then "Microsoft.Jet.OLEDB.4.0"

Example of the error
***********************************
PS C:\code\examples-powershell\msaccess> $cn.Open("Provider = Microsoft.Jet.OLEDB.4.0;Data Source = C:\code\examples-pow
ershell\msaccess\msaccessdatadb1.accdb")
Exception calling "Open" with "1" argument(s): "Unrecognized database format 'C:\code\examples-powershell\msaccess\msac
cessdatadb1.accdb'."
At line:1 char:9
+ $cn.Open <<<< ("Provider = Microsoft.Jet.OLEDB.4.0;Data Source = C:\code\examples-powershell\msaccess\msaccessdatadb1
.accdb")
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : ComMethodTargetInvocation

PS C:\code\examples-powershell\msaccess>



************************************



2008 Winter Scripting Games - Event 9 - MS Access PowerShell
http://blogs.technet.com/heyscriptingguy/archive/2009/08/13/hey-scripting-guy-can-i-query-a-microsoft-access-database-with-a-windows-powershell-script.aspxhttp://blogs.technet.com/heyscriptingguy/archive/2009/08/13/hey-scripting-guy-can-i-query-a-microsoft-access-database-with-a-windows-powershell-script.aspx
The file I was working with - ScriptingGamesBeginnerEvent4.ps1

Other reference:
How to find a record using ADO and Jet OLE DB provider
http://support.microsoft.com/kb/283874