Problem with DoCMD.runsql

This topic contains 4 replies, has 2 voices, and was last updated by Profile photo of Rich TheH Rich TheH 1 year, 9 months ago.

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

    I'm still new to Powershell and am working on the following script. I have an Access database with multiple tables. I have multiple Excel workbooks, using PowerQuery to retrieve new data via and API, that will be loaded into the correct Access tables. Spreadsheets and Tables have the same name. So far I want the script to delete the data in the tables then update with data from the Excel spreadsheet. I have been able to work out how to import multiple files into the corresponding tables. When I tried to add the code to delete the data first, I ran into issues. Currently the 'delete' sql will only delete 1 table, not all 3 in my test code. If I hard code the table names the code will run correctly and delete from all 3 tables, but with a variable only 1 of the tables is emptied.

    Hope this formatted correctly...

    Thanks in advance for any guidance.
    Rich

    $acImport = 0 
    $acSpreadsheetTypeExcel12 = 9 
    # list of excel workbooks with eb data
    # add script "get-childitem" to replace hardcoded values
    $table = 'ScheduleTasks', 'Budget', 'Process_CBVR'
    
    $sql = "DELETE * FROM $($t);"
     
    $a = New-Object -Comobject Access.Application 
    $a.OpenCurrentDatabase("\\kpfs1\Public\shares\cfo\fds\CFM\CFM Shared Files\Reports\database\eBAPI_be.accdb") 
    
    #loop table/spreadhsheet list through import step 
    
    ForEach ($t in $table)
    {
    $a.DoCmd.runsql($sql,$False)
    #$a.DoCmd.TransferSpreadsheet($acImport, $acSpreadsheetTypeExcel12, $t, "\\kpfs1\Public\shares\cfo\fds\CFM\CFM Shared Files\Reports\sql source files\tobeimported\$($t).xlsx", $True) 
    }
    
    $a.Quit()
    
  • #22824
    Profile photo of Jack Neff
    Jack Neff
    Participant

    What does the output of $table look like when you load it with Get-ChildItem?

  • #22843
    Profile photo of Rich TheH
    Rich TheH
    Participant

    Jack,

    Get-childitem is still commented out. That is my next task, to get the file/table names that will eventually pipe into the $table variable. (I am learning how to parse the name from the extension.) I will write the names to a text file and get the content for the $table variable from that text file. For now I was just building and testing the script step by step.

    The $table variable works fine with the "$a.DoCom.TransferSpreadsheet..." statement, just not the "$a.DoCmd.runsql" statement.

  • #22846
    Profile photo of Jack Neff
    Jack Neff
    Participant

    Ah...I misunderstood then my bad. Okay then the problem is where you placed your "$sql = " statement. Since it depends on the value of $t you have to place it inside the foreach loop.

    Example:

    $table = 'ScheduleTasks', 'Budget', 'Process_CBVR'
    $sql = "DELETE * FROM $($t);"
    
    ForEach ($t in $table) {
        Write-Output "T:  $t"
        Write-Output "SQL: $sql"
    }
    

    Gives you:

    T:  ScheduleTasks
    SQL: DELETE * FROM Process_CBVR;
    T:  Budget
    SQL: DELETE * FROM Process_CBVR;
    T:  Process_CBVR
    SQL: DELETE * FROM Process_CBVR;
    

    But this:

    $table = 'ScheduleTasks', 'Budget', 'Process_CBVR'
    
    ForEach ($t in $table) {
        $sql = "DELETE * FROM $($t);"
        Write-Output "T:  $t"
        Write-Output "SQL: $sql"
    }
    

    Gives you this:

    T:  ScheduleTasks
    SQL: DELETE * FROM ScheduleTasks;
    T:  Budget
    SQL: DELETE * FROM Budget;
    T:  Process_CBVR
    SQL: DELETE * FROM Process_CBVR;
    
  • #22847
    Profile photo of Rich TheH
    Rich TheH
    Participant

    Thanks Jeff. That solves [i]that[/i] question. I didn't realize I had to put the query variable in the ForEach loop, but it makes sense.

    Also, thanks for the Write-output step. That's a great way for me to trouble shoot and check my script.

    Thanks again and Aloha!

You must be logged in to reply to this topic.