sqlvbams-accesspass-through

Pass-through queries to populate SAP HANA Global Temporary Tables

I have a series of pass-through queries to SAP HANA that flush (TRUNCATE) and fill (INSERT INTO) Global Temporary Tables. When I run them individually/manually, they populate the tables accordingly.

When I automate their execution with Access VBA, the TRUNCATE (clear table) pass-throughs work as intended but only the first INSERT INTO query appears to execute successfully and populate its target temp table.
The other INSERT INTO pass throughs appear to run (or do not fail) but do not populate their intended target tables as they do when run individually/manually from Access.

Is it possible that the VBA 'DoCmd.OpenQuery"MyQuery"'method and sequence is initiating all pass-throughs at once causing each successive pass-through query in the sequence to be interrupted?
If so, is there a VBA solution that will pause each successive query from executing until the preceding one has completed?

I tried:

db.Execute "MyQuery", dbSQLPassThrough + dbFailOnError
DoEvents

I get an error.

Original VBA Script:

Option Compare Database
Option Explicit

Private Sub RunDPORScript_Click()
    'On Error GoTo RunDPORScript_Click_Err
    
'---Clear Pre-existing Tables---

    DoCmd.OpenQuery "1A_TRUNCATE_PLANNED_ORDERS_TempTbl", acViewNormal, acEdit
    DoCmd.OpenQuery "2A_TRUNCATE_PLANNED_ORDERS_2_TempTbl", acViewNormal, acEdit
    DoCmd.OpenQuery "3A_TRUNCATE_PODOC_SA_TempTbl", acViewNormal, acEdit
    DoCmd.OpenQuery "4A_TRUNCATE_PODOC_CON_TempTbl", acViewNormal, acEdit
    DoCmd.OpenQuery "5A_TRUNCATE_STO_TempTbl", acViewNormal, acEdit

'---Fill Pre-existing Tables---

    DoCmd.OpenQuery "1E_INSERT_PLANNED_ORDERS_TempTbl", acViewNormal, acEdit
    DoCmd.OpenQuery "2E_INSERT_PLANNED_ORDERS_2_TempTbl", acViewNormal, acEdit
    DoCmd.OpenQuery "3E_INSERT_PODOC_SA_TempTbl", acViewNormal, acEdit
    DoCmd.OpenQuery "4E_INSERT_PODOC_CON_TempTbl", acViewNormal, acEdit
    DoCmd.OpenQuery "5E_INSERT_STO_TempTbl", acViewNormal, acEdit

'---Display Global Temp Tables---

    DoCmd.OpenQuery "1D_DISPLAY_PLANNED_ORDERS_TempTbl", acViewNormal, acEdit
    DoCmd.OpenQuery "2D_DISPLAY_PLANNED_ORDERS_2_TempTbl", acViewNormal, acEdit
    DoCmd.OpenQuery "3D_DISPLAY_PODOC_SA_TempTbl", acViewNormal, acEdit
    DoCmd.OpenQuery "4D_DISPLAY_PODOC_CON_TempTbl", acViewNormal, acEdit
    DoCmd.OpenQuery "5D_DISPLAY_STO_TempTbl", acViewNormal, acEdit

    RunDPORScript_Click_Exit:
    Exit Sub

    RunDPORScript_Click_Err:
    MsgBox Error$
    Resume RunDPORScript_Click_Exit

End Sub
Solution

  • First, I RARE but RARE used docmd.OpenQuery. it has a boatload of issues (including that of starting a transaction - makes a mess for PT query stuff).

    Use currentdb.Execute "some sql here"

    Or in the case of a query (including PT ones), then use

    CurrentDB.QueryDefs("name of query").Execute

    or

    CurrentDB.QueryDefs("name of query").Execute, dbFailOnError

    Now, if you going to use temp tables?

    Then you want as a general rule to FORCE use of the SAME connection object.

    The most easy way is to create ONE PT query, and then re-use that one query over and over.

    So, say this code:

    This example is for T-SQL/sql server, but I think this would work for MySQL.

    So, say this code:

    Sub FunTest()

    ' execute some t-sql queries, creating a SQL temp table.
    ' such temp tables GO OUT of scope after you close the session/connection.
    
    Dim db      As DAO.Database
    Set db = CurrentDb
    
    
    'So, then, we need to execute the t-sql (server side - pass-though)
    ' using ONE connection for each of the commands.
    
    With db.QueryDefs("qryPass")
    
        
        .SQL = "DROP TABLE if exists #MyTemp "
        .ReturnsRecords = False
        .Execute
        
        
        Dim strSQL As String
        strSQL = "CREATE TABLE #MyTemp (ID int, HotelName nvarchar(50));"
        
        .SQL = strSQL
        .Execute
        
        ' select some rows from a sql server NON temp table
        
        strSQL = "INSERT INTO #MyTemp (ID, HotelName) " & _
                 "SELECT ID, HotelName FROM tblHotelsA"
                 
        .SQL = strSQL
        .Execute
        
        ' get cound of rows in temp table
        
        strSQL = "SELECT COUNT(*) AS HotelCount FROM #MyTemp"
        
        .SQL = strSQL
        
        .ReturnsRecords = True
        
        Dim rstData     As DAO.Recordset
        
        Set rstData = .OpenRecordset
        
        Debug.Print "Count of rows in #MyTemp table = " & rstData!HotelCount
        
        .SQL = "SELECT * FROM #MyTemp"
        .ReturnsRecords = True
'
'        .SQL = db.QueryDefs("Some Other PT query or command").SQL
'        .ReturnsRecords = False  ' for most "aciton query"
'        .Execute
        
        
    
    End With
    
    
    db.Close
    
    ' below report is based on qryPass - the SAME connection used above - the report will work
    ' EVEN if based on that #MyTemp table.
    
    
    
    DoCmd.OpenReport "rptHotelsOnTemp", acViewPreview
    

End Sub

    So, note how you can "change" the .returnsRecords to true, or false, depending on if this is a update or "action query", or this is a query that returns records.

    Note that you can even create a report based on that ONE PT query, and then say do this:

       currentdb.QueryDefs("qryPT").SQL = "SOME SERVER SIDE SELECT - even store procedure calls"

   docmd.OpenReport "myReport", acViewPreview

    However, in the case of "temp" tables?

    I not 100% sure how MySQL works, but for t-sql server, as long as that connection and "session" remains open, then the #Temp table will continue to persist. In the case of ms-access, that means as long as Access remains open.

    If you use the SAME pt query object as per above, then the #Temp table should remain available for as long as Access remains open.

    Note that by using the same PT object over and over , then that should persist the session information.

    In fact, in most applications, I have 2 PT query,

     qryPassPT
 qryPassPTR

    The "R" is a query setup to return records, and the other one I use for commands. However, as noted, I would suggest that you use the ONE same query object if you going to introduce the concept of Temp tables.

    You MIGHT be able to use this syntax:

    old:

    DoCmd.OpenQuery "1A_TRUNCATE_PLANNED_ORDERS_TempTbl", acViewNormal, acEdit
DoCmd.OpenQuery "2A_TRUNCATE_PLANNED_ORDERS_2_TempTbl", acViewNormal, acEdit

    new:

    dim db          as dao.Database
set db = currentdb

dim qryPT       as dao.QueryDef

set qryPT = db.QueryDefs("qryPT")
qryPT.SQL = db.QueryDefs("1A_TRUNCATE_PLANNED_ORDERS_TempTbl").SQL
qryPT.Execute

    I not tested, but perhaps this below also works, and thus would save the requirement to "always" copy the SQL into the one and same querydef object.

    So, try and see if this works:

    dim db          as dao.Database
set db = currentdb

db.QueryDefs("1A_TRUNCATE_PLANNED_ORDERS_TempTbl").Execute
db.QueryDefs("2A_TRUNCATE_PLANNED_ORDERS_2_TempTbl").Execute
.etc .etc.