如何基于SQL Server临时表设置Access窗体记录源

应用背景 #

我用MS Access搭配SQL Server 2019开发了一套交货处理应用，核心流程如下：

• 扫描含序列号的条码后，数据存入SQL Server本地临时表#TempUnits；
• 流程完成并验证后，临时表数据迁移至正式业务表，随后临时表被销毁。

插入临时表的VBA代码 #

pub_Conn.Execute "INSERT INTO #TempUnits " & _
                        "(ProductID, PurchaseOrderDetailID, DeliveryDetailID, SerialNumber, Config, SupWarrantyEnds) " & _
                            "VALUES (" & pub_rsDelivery("ProductID") & ", " & _
                                    pub_rsDelivery("PurchaseOrderDetailID") & ", " & _
                                    pub_rsDelivery("DeliveryDetailID") & ", " & _
                                    "'" & strSerialNumber & "', " & _
                                    strConfig & ", " & _
                                    "'" & Format(DateAdd("m", pub_rsDelivery("Warranty"), pub_rsDelivery("DateShipped")), "yyyy-mm-dd") & "')"

迁移至正式表的VBA代码 #

pub_Conn.BeginTrans

pub_Conn.Execute "INSERT INTO tbl1Units (ProductID, PurchaseOrderDetailID, DeliveryDetailID, SerialNumber, Config, SupWarrantyEnds) " & _
                    "SELECT ProductID, PurchaseOrderDetailID, DeliveryDetailID, SerialNumber, Config, SupWarrantyEnds FROM #TempUnits"
                    
pub_Conn.Execute "SELECT DeliveryDetailID, COUNT(*) AS QtyDelivered INTO #TempUnitsQty FROM #TempUnits GROUP BY DeliveryDetailID"
pub_Conn.Execute "INSERT INTO #TempUnitsQty (DeliveryDetailID, QtyDelivered) " & _
                    "SELECT DeliveryDetailID, Quantity FROM #TempProducts"

pub_Conn.Execute "UPDATE tbl1DeliveryDetails " & _
                    "SET ActualQuantity = T.QtyDelivered " & _
                    "FROM #TempUnitsQty T JOIN tbl1DeliveryDetails D ON T.DeliveryDetailID = D.DeliveryDetailID"

pub_Conn.Execute "DROP TABLE IF EXISTS #TempProducts"
pub_Conn.Execute "DROP TABLE IF EXISTS #TempUnitsQty"
pub_Conn.Execute "DROP TABLE IF EXISTS #TempUnits"

pub_Conn.Execute "UPDATE tbl1Deliveries SET DateReceived = GETDATE() WHERE DeliveryID = " & Forms!frmDeliveryDetails!DeliveryID

pub_Conn.CommitTrans
MsgBox "Dodávka byla v pořádku přijata.", vbInformation + vbOKOnly, "Úspěch"

需求与问题 #

目前流程运行正常，但我希望能实时查看扫描进度（例如：Product A (0/5 items scanned)）。用Access本地表和查询能实现这个需求，但我想知道是否可以直接将SQL Server临时表作为Access窗体的记录源，类似以下示意代码（语法有误，仅作参考）：

Me.RecordSource = pub_Conn.Execute "SELECT * FROM #TempUnits"

实现方案 #

核心原理 #

SQL Server本地临时表（#开头）属于会话级对象，仅在创建它的ADO连接会话中可见，所以必须使用同一个pub_Conn连接对象来操作和查询临时表，不能新建连接。

绑定临时表到窗体的正确代码 #

不能直接将SQL语句赋值给窗体的RecordSource，需要通过ADO记录集绑定：

Dim rsTemp As ADODB.Recordset
' 从同一个连接获取临时表的记录集
Set rsTemp = pub_Conn.Execute("SELECT * FROM #TempUnits")
' 将记录集绑定到窗体
Set Me.Recordset = rsTemp
' 释放资源（可选，窗体关闭时会自动释放）
Set rsTemp = Nothing

实时刷新扫描进度 #

每次扫描条码插入临时表后，重新执行上述代码刷新窗体记录集，即可显示最新扫描数据。如果要展示已扫描/总数量的进度，可通过以下代码实现：

Dim scannedQty As Long
Dim totalQty As Long
Dim currentProduct As String

' 获取当前交货单的总需扫描数量
totalQty = pub_rsDelivery("Quantity")
' 获取已扫描数量
scannedQty = pub_Conn.Execute("SELECT COUNT(*) FROM #TempUnits WHERE DeliveryDetailID = " & pub_rsDelivery("DeliveryDetailID"))(0)
' 获取产品名称（根据实际字段调整）
currentProduct = pub_rsDelivery("ProductName")

' 更新窗体上的进度标签
Me.lblProgress.Caption = currentProduct & " (" & scannedQty & "/" & totalQty & " items scanned)"

注意事项 #

• 确保pub_Conn在整个扫描过程中保持连接状态，一旦连接断开或重建，临时表会被自动销毁；
• 不要尝试用Access链接表绑定临时表，因为链接表会创建新的SQL Server会话，无法访问当前连接的临时表；
• 如果需要多用户同时操作，可考虑使用全局临时表（##开头），但需注意并发冲突问题，建议还是用会话级临时表。

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