I am writing a Powershell script to automate the process of uploading multiple Excel sheets to an Access database, then run various queries against this new data and generate some reports. I have used 'DoCmd.RunSQL ($sql) and ‘DoCmd.TransferSpreadsheet()’ successfully but am wondering if there is an easier way to run queries. Based on some searching I think the answer is ‘no’ but wanted to see if anyone here had a better solution. Do I always need to create the query string = $sql or is there a way to call a named query that already exists in the database?
This job will run nightly so using PowerShell has already saved me incredible amounts of time (improving my lifetime earning potential I hope! thanks Don) but as I am learning PowerShell I want to improve my code and be as efficient as possible.
Eventually this will move to SQL Server so I will have access to triggers and stored procedures but for now, just trying to work out my options in Access.