To resolve this issue I created an additional database who's purpose was to house the VBA for the migration. It contains no tables, no queries, etc.
The code below takes in an ODBC DSN and a file location of the access database I actually want to use.
Sub ExportTbls(ODBCDSN As String, dbLocation As String)
Dim sTblNm As String
Dim sTypExprt As String
Dim sCnxnStr As String
Dim vStTime As Variant
Dim accObj As Access.Application
Dim tbldef As DAO.TableDef
Dim sDropTable As String
On Error GoTo ExportTbls_Error
sTypExprt = "ODBC Database"
sCnxnStr = "ODBC;DSN=" + ODBCDSN
'Create the connection string
Set accObj = New Access.Application
accObj.OpenCurrentDatabase dbLocation, True
For Each tbldef In accObj.CurrentDb.TableDefs
If Left(tbldef.Name, 4) <> "MSys" And Left(tbldef.Name, 4) <> "~TMP" Then
sTblNm = tbldef.Name
accObj.DoCmd.TransferDatabase acExport, sTypExprt, sCnxnStr, acTable, sTblNm, sTblNm
On Error GoTo 0
'MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure ExportTblsODST"
The Powershell to call this is as follows. I didn't include the declaring of some of the variables but I think it's self explanitory enough.
Write-Host "Connecting to Access"
#Connect to access
Add-Type -AssemblyName Microsoft.Office.Interop.Access
$MsAccess = New-Object -ComObject Access.Application
Write-Host "Making tables and column names lowercase"
#Groom the data to make it easier to use in postgresql
$MsAccess.run("ConvertTablesAndFieldsToLowercase", [ref] $CopyLocation)
#Copy the database
Write-Host "Export Access to Postgres. This may take a bit"
$MsAccess.run("ExportTbls", [ref] $ODBCDSN,[ref] $CopyLocation)
Write-Host "Export to postgresql is complete"