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.
Best Answer
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.