Problems connecting to an Access MDB file through PowerShell

Question

Hello,

I am attempting to connect to an Access 2000 database file (*.mdb), but I am having just a tad few issues. Here is the screenplay thus far,

1) Googled how to connect to a database using powershell which resulted in the following as a source code baseline.

$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()

2) I substituted the Data Source for the fully qualified path of my database then was presented with the following.

Exception calling "Open" with "5" argument(s): "Record(s) cannot be read; no read permission on 'RqRequirements'."
At :line:23 char:18
+ $objRecordset.Open <<<< ("Select * from RqRequirements", $objConnectionCsdscDB,$adOpenStatic,$adLockOptimistic)

3) Since this is a Rational RequisitePro database I almost never need to edit the database directly, but come to find out if we need to edit the database direct we need to issue the following command as a link on the Windows Desktop:

"C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE" /wrkgrp C:\Program Files\Rational\RequisitePro\bin\rqprodb.mda" /user "xxxxxxx" /pwd "yyyyy"

4) Taking the script listed above and changing it slightly I have the following:

$adOpenStatic     = 3
$adLockOptimistic = 3

$objConnectionRqProDB = New-Object -comobject ADODB.Connection
$objConnectionCsdscDB = New-Object -comobject ADODB.Connection
$objRecordset         = New-Object -comobject ADODB.Recordset

$cnnStringRqProDB = "Provider = Microsoft.Jet.OLEDB.4.0;" + 
                    "Data Source = C:\\Program Files\\Rational\\RequisitePro\\bin\\rqprodb.mda;" +
                    "UID=requisite admin;" +
                    "PWD=multiuser"

$cnnStringCsdscDB = "Provider = Microsoft.Jet.OLEDB.4.0;" + 
                    "Data Source = J:\\TestPowerShell\\Rational.MDB"

$objConnectionRqProDB.Connectionstring = $cnnStringRqProDB
$objConnectionRqProDB.Open()

$objConnectionCsdscDB.Connectionstring = $cnnStringCsdscDB
$objConnectionCsdscDB.Open()

$objRecordset.Open("Select * from RqRequirements", $objConnectionCsdscDB,$adOpenStatic,$adLockOptimistic)

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

5) When I run this script I get the following error:

Exception calling "Open" with "4" argument(s): "Could not find installable ISAM."
At :line:17 char:26
+ $objConnectionRqProDB.Open <<<< ()

6) I did some searching and found the following link, http://support.microsoft.com/kb/209805, and I checked the registry and the entry is present for

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Paradox win32=C:\WINDOWS\system32\mspbde40.dll

this file is located in %SYSTEM32%\

Note, I do not have Access installed on my system (could this be an underlying problem? I am not sure, but I wouldn't think so since I am using the ADO)

Questions:

1) How do I include the "/wrkgrp" option in the connection string in the script?

2) Assuming the lack of the "/wrkgrp" option in the connection string is not my problem what might be issue?

3) Does Access need to be installed on the system in order for this to work?

Thanks, Mark

Answer 1

You shouldn't need Access installed.

You are trying to open the workgroup database (mdw) separately - don't do that.

You need to specify the workgroup database in the connect string

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\mydatabase.mdb;
Jet OLEDB:System Database=system.mdw;User ID=myUsername;Password=myPassword;

So in your case

$cnnStringRqProDB = "Provider = Microsoft.Jet.OLEDB.4.0;" + 
                    "Data Source = J:\\TestPowerShell\\Rational.MDB;"
                    "Jet OLEDB:System Database = C:\\Program Files\\Rational\\RequisitePro\\bin\\rqprodb.mda;" +
                    "User ID=requisite admin;" +
                    "Password=multiuser"

$objConnectionCsdscDB.Connectionstring = $cnnStringCsdscDB
$objConnectionCsdscDB.Open()