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.
Queries stored in the database are basically “stored procedures.” You can execute those. And this will definitely be better in SQL; I’d actually start there. Why not just use SQL Server Express instead of Access?
Thanks for the response Don. Honestly, I hadn’t considered SQL Server Express. I was doing a ‘proof of concept’ for my IT team to demonstrate the capabilities and we started with some manual steps taking with an Access database upload and report generation. I was hoping to prove some of the Powershell capabilities so they would let me move this up to the corporate SQL Server.
With further research I did find the DoCmd.OpenQuery and was able to grab the named queries. Now I am working on creating functions for each of the steps and create my own cmdlet.
thanks for the nudge in the right direction.
ps any plans for come out and speak or do any training in Hawaii?