1

There's an .mdb located on a shared drive in my organization that's password protected and needs to stay password protected. I have the password. I use a local copy for data analysis. The shared drive is very slow for me so I copy it daily using powershell. I need to remove the password manually because the analysis software available to me doesn't support .mdb passwords.

The code below successfully opens the .mdb so I can manually unset the password, but I'd like to automate unsetting the password.

$path = "C:\path\to\db\"
$file = "db.mdb"  
$access = New-Object -com Access.Application
$access.Visible = $True
$access.OpenCurrentDataBase($path + $file, $True, "password")

I think my problem is I don't understand PowerShell's implementation of the object model. I want to use Database.NewPassword to change the password. In VB implementations it requires a database object, however, and when I substitute $db = $access.OpenCurrentDataBase($path + $file, $True, "password") for $access.OpenCurrentDataBase($path + $file, $True, "password") the $db variable is $null.

Is there a way to refer to the database object in PowerShell?

PKB
  • 321
  • 1
  • 11

2 Answers2

1

You can manage the database password with Access DDL.

ALTER DATABASE PASSWORD newpassword oldpassword

This is how I removed the password with PowerShell:

$path = "C:\Temp\DbPwd_foo.mdb"
$newpassword = "Null"
$oldpassword = "foo"
$accessApp = new-object -com access.application
$accessApp.OpenCurrentDatabase($path, -1, $oldpassword)
$sql = "ALTER DATABASE PASSWORD " + $newpassword + " " + $oldpassword
$accessApp.CurrentProject.Connection.Execute($sql)
$accessApp.Quit()

You can restore the previous password by swapping the values of $newpassword and $oldpassword, and calling OpenCurrentDatabase without its password argument.

HansUp
  • 95,961
  • 11
  • 77
  • 135
0

You can use DAO for that, the DbEngine.Compact method. It's a pain that powershell does not allow naming parameters and skipping the optionals, though, unless you want to do complicated stuff.

$path = "C:\path\to\db\"
$file = "db.mdb"
$newfile = "decrypted.mdb"
$dbe= New-Object -com DAO.DbEngine.120
$dbe.Compact($path + $file, $path + $newfile, ";LANGID=0x0409;CP=1252;COUNTRY=0", 64, ";PWD=password")
Erik A
  • 31,639
  • 12
  • 42
  • 67
  • Thank you! When I try to create the object I get the following error: New-Object : Retrieving the COM class factory for component with CLSID {00000000-0000-0000-0000-000000000000} failed due to the following error: 80040154 Class not registered (Exception from HRESULT: 0x80040154 (REGDB_E_CLASSNOTREG)). At line:1 char:8 + $dbe = New-Object -com DAO.DbEngine + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : ResourceUnavailable: (:) [New-Object], COMException + FullyQualifiedErrorId : NoCOMClassIdentified,Microsoft.PowerShell.Commands.NewObjectCommand – PKB Jun 03 '21 at 13:36
  • Should have searched before responding. Looks like it's a 32bit driver in 64bit env problem. My root issue, actually. https://stackoverflow.com/questions/12138901/error-in-create-access-database/12139165. Any ideas how to work around it? – PKB Jun 03 '21 at 13:43
  • @PKB Try the edit. The database engine is always available if Access is. Usually, requesting the general object gets the version-specific one – Erik A Jun 03 '21 at 13:43
  • Shouldn't be a 32-bit 64-bit issue if you can use the Access application object. You can always do `$access = New-Object -com Access.Application` and `$dbe = $access.DbEngine` – Erik A Jun 03 '21 at 13:45
  • Thanks for the ongoing help. `$dbe= New-Object -com DAO.DbEngine.120` gave the same error and `$dbe = $access.DbEngine` returned a null object. – PKB Jun 03 '21 at 15:24
  • You ran `$access = New-Object -com Access.Application` before `$dbe = $access.DbEngine`, right? The Access application must have a dbengine property, so that's very odd, if it's missing the only thing I can think of is "do a reinstall" – Erik A Jun 03 '21 at 15:38
  • I did, indeed. Just checked again for my sanity, but yes. Makes no sense, right? – PKB Jun 03 '21 at 19:24