Accessing MS Access files in Powershell

RE: Accessing MS Access files in Powershell (sanket.shetye)

Sun, 18 Feb 2007 22:31:19 GMT

Hey that works great! thanks mate

RE: Accessing MS Access files in Powershell (SAPIENScripter)

Wed, 14 Feb 2007 07:34:48 GMT

Try this:

$objConnection.Connectionstring = "Provider = Microsoft.Jet.OLEDB.4.0;Data Source = $pwd\status.mdb"

I think you have to specify the path. Using $pwd at least doesn't force you to hard code a path.

RE: Accessing MS Access files in Powershell (SAPIENScripter)

Wed, 14 Feb 2007 07:26:53 GMT

The more I play with this, the more I think the path issue is related to the COM object OLE DB provider. COM objects under PowerShell are not 100% foolproof and completely compatible. It may be that under PowerShell the provider is defaulting to a certain directory for the database unless you specify a complete path.

RE: Accessing MS Access files in Powershell (SAPIENScripter)

Wed, 14 Feb 2007 07:19:24 GMT

I always specify the full path, even in VBScript. And while a vbscript version of your script doesn't require the path, obviously PowerShell is different. Remember, it is not a scripting engine like VBScript. It is an interactive shell with batch-file like capabilities. By that I mean you can put your commands in a ps1 file and execute them much the same way you build a batch file (conceptually) in CMD.exe.

Powershell is more secure by design. For example, you have to explicity state the path of the script your want to run. I expect path access to the database file is the same way. I'm not saying it makes things easier for you, just that I suspect there's a security reason behind it.

Accessing MS Access files in Powershell (sanket.shetye)

Tue, 13 Feb 2007 09:40:12 GMT

Guys, I need help in accessing MS Access database files in Powershell, I am using the following code (Taken from scriptcenter and modified a bit)
-------------------------
$adOpenStatic = 3
$adLockOptimistic = 3
$objConnection = New-Object -comobject ADODB.Connection
$objRecordset = New-Object -comobject ADODB.Recordset
$objConnection.Connectionstring = "Provider = Microsoft.Jet.OLEDB.4.0;Data Source = status.mdb"
$objConnection.Open()
$objRecordset.Open("Select * from app1", $objConnection,$adOpenStatic,$adLockOptimistic)
$objRecordset.MoveFirst()
do {$objRecordset.Fields.Item("counter").Value; $objRecordset.MoveNext()} until
($objRecordset.EOF -eq $True)
$objRecordset.Close()
$objConnection.Close()
---------------------------
The error I am getting is as follows-
Exception calling "Open" with "0" argument(s): "Could not find file 'C:\Documents and Settings\ssanket\status.mdb
'."
---------------------------
The location of the script is 'c:\scripts', my problem is when I give full path to the mdb file it works fine, but I dont want to do that, the script should accept only the filename, the script file and database file both are in the same folder. The script should ideally look into the same folder first for the database file in absense of a fully defined path, but that is not happening here, can someone tell me what I am doing wrong?
Thanks a bunch!