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

Is there much point in a password if you are going to include it in script? — Fionnuala
@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

Kev Kev · Accepted Answer · 2014-08-09T04:39:30

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.

Compact & Repair Access 2007 Database with Powershell

2 Answers