Using Powershell can you insert a macro into Access?

Question

I am using PowerShell to automate an export of an access databse (.accdb) to postgresql. As a step of that I am using a VBA script to convert all the table and column names to lowercase so they play nicer with postgresql queries.

I would like to insert the macro that would perform this task after I copy the database to a local location. This is prevent the cleanup script ever being run on the production database.

My question is, using powershell is it possible to insert a macro into an access database? I can find documentation on how to do this with excel but as far as I can tell it doesn't translate to access.

Edit: Alternatively a macro that can create another macro with a given input or a macro that will execute code fed to it via an input parameter.

Answer 1

Doing this via a macro seems to be an odd and unnecesary step. In theory you should only need to create the new schema once.

I would create the new schema in postgress and use an ETL tool to map between them.

I would export the access schema and convert the names to lower case using an editor or as part of the export process. This link gives details on how that can be done with vba https://www.techrepublic.com/article/exporting-access-schema-using-xml/

Another option is to import the schema and then use an alter table statement to rename the columns using lower case. It shouldnt be too hard to write something that can be reused.

Answer 2

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"
'Export Type
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
Debug.Print tbldef.Name
sTblNm = tbldef.Name
accObj.DoCmd.TransferDatabase acExport, sTypExprt, sCnxnStr, acTable, sTblNm, sTblNm
End If
Next tbldef
On Error GoTo 0
SmoothExit_ExportTbls:


Exit Sub

ExportTbls_Error:
'MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure ExportTblsODST"
Resume SmoothExit_ExportTbls
End Sub

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
Start-Sleep 15
$MsAccess.OpenCurrentDatabase($MacroDB)
Start-Sleep 15
Write-Host "Making tables and column names lowercase"
#Groom the data to make it easier to use in postgresql
$MsAccess.run("ConvertTablesAndFieldsToLowercase", [ref] $CopyLocation)
Start-Sleep 60
#Copy the database
Write-Host "Export Access to Postgres. This may take a bit"
$MsAccess.run("ExportTbls", [ref] $ODBCDSN,[ref] $CopyLocation)
Start-Sleep 60 
Write-Host "Export to postgresql is complete"