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?
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.