Here’s an interesting one. I was helping on Experts-Exchange with a question relating to copying a Microsoft Access database and then compacting it using PowerShell.
Personally, I’d use a bat or VBScript file to do this normally, but I still thought I’d share how it can be done via PowerShell for those of you that might need to do so.
The following examples utilize a $files variable which you can specify multiple comma separated files to copy/compact in one call.
There are a couple ways this can be done.
Option 1 – Using Com Object
$files = "contacts", "clients"
$extension = ".accdb"
$srcPath = "C:\Users\Dev\Documents"
$destPath = "E:\Backups"
Foreach ($f in $files)
{
$srcFile = "$SrcPath\$f$extension"
$destFile = "$DestPath\$f$extension"
$fileCompacted = $f + "_compacted"
$compactedName = "$DestPath\$fileCompacted$extension"
Copy-Item $srcFile -Destination $destPath
Add-Type -AssemblyName Microsoft.Office.Interop.Access
$access = New-Object -ComObject access.application
$access.Visible = $false
$access.AutomationSecurity = 1
$access.CompactRepair($destFile,$compactedName,$false)
$access.Quit()
Remove-Item $destFile
Rename-Item -Path $compactedName -NewName $destFile
}
Option 2 – Using the Command Prompt
$files = "contacts", "clients"
$extension = ".accdb"
$srcPath = "C:\Users\Dev\Documents"
$destPath = "E:\Backups"
Foreach ($f in $files)
{
$srcFile = "$SrcPath\$f$extension"
$destFile = "$DestPath\$f$extension"
Copy-Item $srcFile -Destination $destPath
"""C:\Program Files (x86)\Microsoft Office\Office15\msaccess.exe"" ""$destFile"" /compact" | cmd
}
Improved Version
The version above requires hard coding the path to the msaccess executable, but we can improve on this. If we review my article
we know that we can get this information by simply reading the
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\App Paths\MSACCESS.EXE\
registry key and that is easily done with PowerShell. So we can transform the above into:
$files = "contacts", "clients"
$extension = ".accdb"
$srcPath = "C:\Users\Dev\Documents"
$destPath = "E:\Backups"
Foreach ($f in $files)
{
$srcFile = "$SrcPath\$f$extension"
$destFile = "$DestPath\$f$extension"
$accessExe = Get-ItemProperty -Path "HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\App Paths\MSACCESS.EXE" | Select-Object -ExpandProperty Path
$accessExe = $accessExe + "msaccess.exe"
Copy-Item $srcFile -Destination $destPath
"""$accessExe"" ""$destFile"" /compact" | cmd
}
Conclusion
As you can see, there are several ways to copy and then compact an Access database. Obviously, to be able to compact the database, you must first have Access installed as Access does the compaction.
A Few Resources on the Subject
