VBA结合MSSQL事务：如何获取事务内生成的ShipmentID

在SQL Server+Access VBA场景下，用DMAX拿事务内的自增ID确实不靠谱——一来事务未提交时，这条新记录在当前会话外不可见，DMAX查不到；二来多用户并发时，极有可能拿到别人刚生成的ID，直接搞乱数据。下面给你两种靠谱的实现方式，全程用ADODB事务保障数据完整性：

方法一：用SCOPE_IDENTITY()获取自增ID #

SCOPE_IDENTITY()是SQL Server官方推荐的获取当前会话、当前作用域内自增ID的方法，只会返回你当前连接刚插入的那条记录的ID，完全不受其他用户操作影响。

VBA代码示例 #

Private Sub btnShip_Click()
    Dim conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim shipmentID As Long
    Dim strSQL As String
    
    ' 先执行订单验证逻辑（放在事务外，避免占用资源）
    If Not ValidateOrder(Me.txtOrderID) Then
        MsgBox "订单验证不通过，无法发货", vbExclamation
        Exit Sub
    End If
    
    ' 初始化ADODB连接（替换成你的SQL Server连接字符串）
    Set conn = New ADODB.Connection
    conn.ConnectionString = "Provider=SQLOLEDB;Data Source=你的SQL服务器;Initial Catalog=你的数据库;User ID=账号;Password=密码;"
    conn.Open
    
    On Error GoTo ErrorHandler
    
    ' 开启事务
    conn.BeginTrans
    
    ' 1. 插入发货主记录
    strSQL = "INSERT INTO tblShipments (OrderID, ShipDate, ShipMethod) " & _
             "VALUES (" & Me.txtOrderID & ", GETDATE(), '" & Me.cboShipMethod & "')"
    conn.Execute strSQL
    
    ' 2. 获取刚生成的ShipmentID
    strSQL = "SELECT SCOPE_IDENTITY() AS ShipmentID"
    Set rs = conn.Execute(strSQL)
    shipmentID = rs("ShipmentID").Value
    rs.Close
    
    ' 3. 插入发货明细（用上面拿到的shipmentID关联）
    strSQL = "INSERT INTO tblShipmentDetails (ShipmentID, ProductID, Qty) " & _
             "SELECT " & shipmentID & ", ProductID, Qty FROM tblOrderDetails WHERE OrderID = " & Me.txtOrderID
    conn.Execute strSQL
    
    ' 4. 提交事务
    conn.CommitTrans
    MsgBox "发货成功", vbInformation
    
Cleanup:
    If Not rs Is Nothing Then rs.Close
    If Not conn Is Nothing Then
        If conn.State = adStateOpen Then conn.Close
    End If
    Set rs = Nothing
    Set conn = Nothing
    Exit Sub
    
ErrorHandler:
    ' 出错回滚事务
    If conn.State = adStateOpen Then conn.RollbackTrans
    MsgBox "发货失败：" & Err.Description, vbCritical
    Resume Cleanup
End Sub

' 示例验证函数，根据你的实际需求修改
Private Function ValidateOrder(orderID As Long) As Boolean
    ValidateOrder = True
    ' 这里写你的验证逻辑：比如订单是否已付款、库存是否足够等
End Function

方法二：用OUTPUT子句直接返回插入的ID #

如果需要在插入主记录时一次性获取多个字段（比如ShipmentID+ShipDate），可以用OUTPUT子句，插入时直接返回生成的记录数据。

VBA代码片段（替换方法一中的插入和获取ID部分） #

' 插入发货主记录并返回ShipmentID
strSQL = "INSERT INTO tblShipments (OrderID, ShipDate, ShipMethod) " & _
         "OUTPUT inserted.ShipmentID " & _
         "VALUES (" & Me.txtOrderID & ", GETDATE(), '" & Me.cboShipMethod & "')"
Set rs = conn.Execute(strSQL)
shipmentID = rs(0).Value
rs.Close

关键注意事项 #

• 必须用ADODB连接处理事务，不要用Access的DAO事务：DAO对SQL Server的事务隔离级别和会话一致性支持有限，SCOPE_IDENTITY()和OUTPUT都需要在同一个连接会话内执行才有效。
• 验证逻辑一定要放在事务开启之前：避免事务长时间占用数据库资源，减少锁冲突概率。
• 错误处理必须包含事务回滚：任何一步出错都要回滚，否则会导致数据库锁表或数据不一致。

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