How to trust trust a document in access via PowerShell so I can trigger my database export marcos

Question

Long story short I'm working on using PowerShell to automate a nightly DB conversion from access to PostgreSQL.

In it's more basic form the script does the following.

1. Copies the .accdb file to a local folder
2. Triggers a macro in the .accdb that exports the data to Postgres

The code looks something like this. File locations and names changed. Some database cleanup macros omitted since the extra steps aren't really relevant and are subject to the same problem.

$CopyLocation = "C:\Copy.accdb"
$SourceLocation = "C:\Source.accdb"
Add-Type -AssemblyName Microsoft.Office.Interop.Access
$MsAccess = New-Object -ComObject Access.Application
$MsAccess.OpenCurrentDatabase($CopyLocation)
$MsAccess.Run("ExportToPostgreSQL")
$MsAccess.Quit()

So the issue I'm having is between the copy and the macro execution. Since the document isn't "Trusted" the execution of the macro fails. If I run the script up to the copy, then manually open access and click the enable content button. I can then trigger the macro from powershell.

Is there a way I can trust the document and enable to content from within powershell?

Answer 1

Microsoft Office documents, including Microsoft Access .accdb databases, need to reside in a "trusted" location in order for macros and VBA functions to run without requiring intervention from the user.

Add the desired target location to the list of trusted locations by making a change to the system registry:

HKEY_CURRENT_USER\Software\Microsoft\Office\15.0\Access\Security\Trusted Locations

See this StackOverflow post for further details.