Compact & Repair Access 2007 Database with Powershell

Question

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

Answer 1

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.

Answer 2

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