Running Multiple Acdess 2010 queries

Tagged: ,

This topic contains 3 replies, has 2 voices, and was last updated by Profile photo of Don Jones Don Jones 1 year, 9 months ago.

  • Author
    Posts
  • #23167
    Profile photo of Rich TheH
    Rich TheH
    Participant

    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.

    Thanks!

  • #23185
    Profile photo of Don Jones
    Don Jones
    Keymaster

    You had a few questions there...

    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?

  • #23213
    Profile photo of Rich TheH
    Rich TheH
    Participant

    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?

  • #23216
    Profile photo of Don Jones
    Don Jones
    Keymaster

    Haven't been invited to speak and I don't do classes anymore. But I'm on oahu in about a year I think.

You must be logged in to reply to this topic.