Access VBA执行SAP HANA全局临时表Pass-through查询异常求助

我有一系列针对SAP HANA的Pass-through查询，用来清空（TRUNCATE）和填充（INSERT INTO）全局临时表。手动单独执行这些查询时，表都能正常填充；但用Access VBA自动化执行时，TRUNCATE类查询能正常生效，只有第一个INSERT INTO查询能成功填充目标临时表，其余INSERT INTO查询看似运行（无报错），但没有像手动执行那样填充对应表。

疑问：是不是VBA的DoCmd.OpenQuery方法会同时启动所有查询，导致后续查询被中断？有没有办法让后续查询等前一个执行完再启动？

我试过这段代码但报错：

db.Execute "MyQuery", dbSQLPassThrough + dbFailOnError
DoEvents

原始VBA脚本：

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

解决方案 #

核心问题原因 #

DoCmd.OpenQuery是异步执行的，触发查询运行后会直接继续下一行代码，导致多个Pass-through查询同时向SAP HANA发起请求。而全局临时表的会话隔离特性，或者SAP HANA端的并发资源竞争，会让后续INSERT查询无法正确生效。

正确的VBA实现方式 #

改用同步执行的方法确保每个查询完成后再执行下一个，推荐两种方案：

方案1：执行已保存的Pass-through查询（直接复用现有查询）

通过DAO.QueryDef.Execute方法执行，它是同步执行的，会等待查询完成再继续：

Option Compare Database
Option Explicit

Private Sub RunDPORScript_Click()
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    
    On Error GoTo RunDPORScript_Click_Err
    
    Set db = CurrentDb()
    
'---Clear Pre-existing Tables---
    Set qdf = db.QueryDefs("1A_TRUNCATE_PLANNED_ORDERS_TempTbl")
    qdf.Execute dbFailOnError
    Set qdf = db.QueryDefs("2A_TRUNCATE_PLANNED_ORDERS_2_TempTbl")
    qdf.Execute dbFailOnError
    Set qdf = db.QueryDefs("3A_TRUNCATE_PODOC_SA_TempTbl")
    qdf.Execute dbFailOnError
    Set qdf = db.QueryDefs("4A_TRUNCATE_PODOC_CON_TempTbl")
    qdf.Execute dbFailOnError
    Set qdf = db.QueryDefs("5A_TRUNCATE_STO_TempTbl")
    qdf.Execute dbFailOnError

'---Fill Pre-existing Tables---
    Set qdf = db.QueryDefs("1E_INSERT_PLANNED_ORDERS_TempTbl")
    qdf.Execute dbFailOnError
    Set qdf = db.QueryDefs("2E_INSERT_PLANNED_ORDERS_2_TempTbl")
    qdf.Execute dbFailOnError
    Set qdf = db.QueryDefs("3E_INSERT_PODOC_SA_TempTbl")
    qdf.Execute dbFailOnError
    Set qdf = db.QueryDefs("4E_INSERT_PODOC_CON_TempTbl")
    qdf.Execute dbFailOnError
    Set qdf = db.QueryDefs("5E_INSERT_STO_TempTbl")
    qdf.Execute dbFailOnError

'---Display Global Temp Tables---
    DoCmd.OpenQuery "1D_DISPLAY_PLANNED_ORDERS_TempTbl", acViewNormal, acEdit
    Application.Wait Now() + TimeValue("00:00:01")
    DoCmd.OpenQuery "2D_DISPLAY_PLANNED_ORDERS_2_TempTbl", acViewNormal, acEdit
    Application.Wait Now() + TimeValue("00:00:01")
    DoCmd.OpenQuery "3D_DISPLAY_PODOC_SA_TempTbl", acViewNormal, acEdit
    Application.Wait Now() + TimeValue("00:00:01")
    DoCmd.OpenQuery "4D_DISPLAY_PODOC_CON_TempTbl", acViewNormal, acEdit
    Application.Wait Now() + TimeValue("00:00:01")
    DoCmd.OpenQuery "5D_DISPLAY_STO_TempTbl", acViewNormal, acEdit

RunDPORScript_Click_Exit:
    Set qdf = Nothing
    Set db = Nothing
    Exit Sub

RunDPORScript_Click_Err:
    MsgBox "错误代码：" & Err.Number & vbCrLf & "错误信息：" & Err.Description, vbCritical
    Resume RunDPORScript_Click_Exit
End Sub

方案2：直接执行SQL语句（更灵活）

获取SAP HANA的连接后直接执行SQL，避免依赖保存的查询：

Option Compare Database
Option Explicit

Private Sub RunDPORScript_Click()
    Dim db As DAO.Database
    Dim hanaConn As DAO.Connection
    Dim qdf As DAO.QueryDef
    
    On Error GoTo RunDPORScript_Click_Err
    
    Set db = CurrentDb()
    ' 从已有的Pass-through查询中获取SAP HANA连接
    Set qdf = db.QueryDefs("1A_TRUNCATE_PLANNED_ORDERS_TempTbl")
    Set hanaConn = qdf.Connection
    
'---Clear Pre-existing Tables---
    hanaConn.Execute "TRUNCATE TABLE YOUR_SCHEMA.PLANNED_ORDERS_TempTbl", dbFailOnError
    hanaConn.Execute "TRUNCATE TABLE YOUR_SCHEMA.PLANNED_ORDERS_2_TempTbl", dbFailOnError
    hanaConn.Execute "TRUNCATE TABLE YOUR_SCHEMA.PODOC_SA_TempTbl", dbFailOnError
    hanaConn.Execute "TRUNCATE TABLE YOUR_SCHEMA.PODOC_CON_TempTbl", dbFailOnError
    hanaConn.Execute "TRUNCATE TABLE YOUR_SCHEMA.STO_TempTbl", dbFailOnError

'---Fill Pre-existing Tables---
    hanaConn.Execute "INSERT INTO YOUR_SCHEMA.PLANNED_ORDERS_TempTbl SELECT ... FROM YOUR_SOURCE_TABLE", dbFailOnError
    hanaConn.Execute "INSERT INTO YOUR_SCHEMA.PLANNED_ORDERS_2_TempTbl SELECT ... FROM YOUR_SOURCE_TABLE", dbFailOnError
    hanaConn.Execute "INSERT INTO YOUR_SCHEMA.PODOC_SA_TempTbl SELECT ... FROM YOUR_SOURCE_TABLE", dbFailOnError
    hanaConn.Execute "INSERT INTO YOUR_SCHEMA.PODOC_CON_TempTbl SELECT ... FROM YOUR_SOURCE_TABLE", dbFailOnError
    hanaConn.Execute "INSERT INTO YOUR_SCHEMA.STO_TempTbl SELECT ... FROM YOUR_SOURCE_TABLE", dbFailOnError

'---Display Global Temp Tables---
    DoCmd.OpenQuery "1D_DISPLAY_PLANNED_ORDERS_TempTbl", acViewNormal, acEdit
    Application.Wait Now() + TimeValue("00:00:01")
    DoCmd.OpenQuery "2D_DISPLAY_PLANNED_ORDERS_2_TempTbl", acViewNormal, acEdit
    Application.Wait Now() + TimeValue("00:00:01")
    DoCmd.OpenQuery "3D_DISPLAY_PODOC_SA_TempTbl", acViewNormal, acEdit
    Application.Wait Now() + TimeValue("00:00:01")
    DoCmd.OpenQuery "4D_DISPLAY_PODOC_CON_TempTbl", acViewNormal, acEdit
    Application.Wait Now() + TimeValue("00:00:01")
    DoCmd.OpenQuery "5D_DISPLAY_STO_TempTbl", acViewNormal, acEdit

RunDPORScript_Click_Exit:
    Set hanaConn = Nothing
    Set qdf = Nothing
    Set db = Nothing
    Exit Sub

RunDPORScript_Click_Err:
    MsgBox "错误代码：" & Err.Number & vbCrLf & "错误信息：" & Err.Description, vbCritical
    Resume RunDPORScript_Click_Exit
End Sub

关键说明 #

• QueryDef.Execute和Connection.Execute都是同步执行，会等待查询完成后再执行下一行，彻底解决异步并发问题。
• dbFailOnError参数会让执行出错时立即抛出错误，避免静默失败，方便排查问题。
• 显示查询前加Application.Wait是为了确保数据完全写入临时表，避免显示空表。
• 你之前用db.Execute报错的原因是：db.Execute默认针对本地Access表，无法直接执行Pass-through查询，必须通过QueryDef或外部连接执行。

内容的提问来源于stack exchange，提问作者CareFree