0

I'm trying to use Powershell V2.0 to programatically compact and repair MS Access 2007 (.accdb) databases. The code I've created below works as part of the final code (several backup procedures occur prior to this function running).

I'm running into trouble though as all the databases are password protected and I need to run the script without the user having to enter the passwords manually. Here's my code so far:

Param([string]$strDBPath,[string]$strBUPath,[string]$strPwd)

$ErrorActionPreference = "Stop"

function CompactAndRepairDB {

    regsvr32 "C:\Program Files\Common Files\Microsoft Shared\DAO\dao360.dll"
    regsvr32 "C:\Program Files\Microsoft Office\Office12\Acedao.dll"
    regsvr32 "C:\WINNT\assembly\Microsoft.Office.Interop.Access.Dao\12.0.0.__71e9bce111e9429c\Microsoft.Office.Interop.Access.Dao.dll"

    $Database = New-Object -ComObject Microsoft.Office.Interop.Access.Dao.DBEngine
    $Database.CompactRepair($strDBPath,$strBUPath,"","",";pwd=" + $strPwd)

    Remove-Item $strDBPath
    Rename-Item $strBUPath $strDBPath
}

CompactAndRepairDB

The code throws an error though as below:

Cannot load COM type Microsoft.Office.Interop.Access.Dao.DBEngine. At U:\Scripts\CompactRepairDatabase.ps1:11 char:27 + $Database = New-Object <<<< -ComObject Microsoft.Office.Interop.Access.Dao.DBEngine + CategoryInfo : InvalidType: (:) [New-Object], PSArgumentException + FullyQualifiedErrorId : CannotLoadComObjectType,Microsoft.PowerShell.Commands.NewObjectCommand

How do I load the correct library / COM object to complete the operation or is there an alternative method to achieve the .CompactRepair method using the password? Thanks

Gareth
  • 5,140
  • 5
  • 42
  • 73
  • Is there much point in a password if you are going to include it in script? – Fionnuala Aug 08 '14 at 14:15
  • @Fionnuala The script is held in a secure location which can only be accessed by one user account so I'm not concerned about access to the script. – Gareth Aug 08 '14 at 14:43

2 Answers2

0

Remove the -ComObject from the line:

$Database = New-Object -ComObject Microsoft.Office.Interop.Access.Dao.DBEngine

Microsoft.Office.Interop.Access.Dao.DBEngine will be a managed interop wrapper around DAO.DBEngine so you don't need the -ComObject switch.

Kev
  • 118,037
  • 53
  • 300
  • 385
  • Thanks for you answer, I'm still receiving an error `Cannot find type [Microsoft.Office.Interop.Access.Dao.DBEngine]: make sure the assembly containing this type is loaded.` Any ideas? – Gareth Aug 11 '14 at 09:00
  • 1
    Hey Kev; your display name is causing some issues... http://meta.stackoverflow.com/questions/269262/username-obfuscates-content; I'm going to change the name to Kev for now - can you consider a less disruptive visual? For reference - the old value: K̨̩̭͚̘̗̻̞͈͖̙͙e̗̦̼̳̣̦͜͡v̢̝̟̗̱̯͉ – Marc Gravell Aug 20 '14 at 10:02
0

Try this:

Add-Type -AssemblyName Microsoft.Office.Interop.Access
$File =  "Your.accdb"
$DbEng = new-object Microsoft.Office.Interop.Access.Dao.DBEngineClass # no square bracket []
$Db = $DbEng.OpenDatabase($File)
$Db
Johnson Cheung
  • 87
  • 7