Access通过ODBC链接SQL Server表执行Recordset.Update报错求助

解决建议 #

• 优先用追加查询替代双Recordset操作
  你的需求是复制最新记录修改日期，直接用单条SQL执行插入完全可以实现，不需要同时打开两个记录集，从根源规避会话冲突问题，示例代码如下：

Private Sub cmdNewWeek_Click()
    On Error GoTo ErrorHandler
    Dim d As Date, strSQL As String
    d = Date - (Weekday(Date) - 2)
    
    If IsNull(Me.cboSelAtty) Then
        MsgBox "Select an attorney first."
        cboSelAtty.SetFocus
        Exit Sub
    End If
    
    If IsNull(Me.employee) Then Me.employee = Me.cboSelAtty
    DoCmd.RunCommand acCmdSaveRecord
    
    ' 先判断是否已有本周记录
    Dim existWeek As Date
    On Error Resume Next
    existWeek = DLookup("week", "kt_workload", "employee=" & CSql(cboSelAtty) & " AND week >= #" & Format(d, "yyyy-mm-dd") & "#")
    On Error GoTo ErrorHandler
    
    If Not IsNull(existWeek) Then
        If MsgBox("A record for this week already exists. Do you want to enter one for a different week?", vbCritical + vbYesNo) = vbNo Then
            Exit Sub
        Else
            d = existWeek + 7
        End If
    End If
    
    ' 执行追加查询复制记录
    strSQL = "INSERT INTO kt_workload (week, employee, 字段1, 字段2, 字段N) " & _
             "SELECT #" & Format(d, "yyyy-mm-dd") & "#, employee, 字段1, 字段2, 字段N " & _
             "FROM kt_workload WHERE employee=" & CSql(cboSelAtty) & " ORDER BY week DESC"
    CurrentDb.Execute strSQL, dbSeeChanges + dbFailOnError
    
    Me.Requery
    Exit Sub
    
ErrorHandler:
    Dim i As Integer
    Dim st As String
    For i = 0 To Errors.Count - 1
        st = st & Errors(i).Description & vbCrLf
    Next i
    MsgBox st, vbCritical
End Sub

把上面的字段1、字段2、字段N替换成你实际要复制的字段名即可，性能和稳定性都比双Recordset写法高。

• 如果要保留原有Recordset写法，调整加载逻辑
  旧版SQL Server ODBC驱动即使开了MARS，也可能出现快照记录集未完全加载、占用会话的问题，调整代码顺序：先读取完r的所有数据、提前关闭r，再执行s的写入操作：

' 读取数据到临时变量/数组后立刻关闭r
Set r = CurrentDb.OpenRecordset("Select top 1 * From kt_workload Where employee=" & CSql(cboSelAtty) & " Order By week Desc", dbOpenSnapshot)
Dim fieldVals As Object
Set fieldVals = CreateObject("Scripting.Dictionary")
If Not r.EOF Then
    For Each f In r.Fields
        fieldVals(f.Name) = f.Value
    Next
End If
r.Close ' 读完立刻关闭r，释放会话
Set r = Nothing

' 再操作s写入
Set s = CurrentDb.OpenRecordset("kt_workload", dbOpenDynaset, dbSeeChanges)
s.AddNew
For Each f In fieldVals.Keys
    If f <> "week" Then s(f) = fieldVals(f)
Next
s("week") = d
s.Update
s.Close
Set s = Nothing

• 检查ODBC驱动和连接配置

1. 卸载旧的SQL Server Native Client驱动，安装最新的ODBC Driver 17 for SQL Server，替换原有驱动重新链接所有表
2. 确认所有链接表的连接字符串中明确包含MARS_Connection=Yes，可以在Access导航窗格右键点击链接表→设计视图→属性，查看「描述」字段里的连接字符串确认配置生效。

• 补全错误处理中的资源释放逻辑
  原有代码出错时不会关闭已经打开的Recordset，会导致连接残留持续占用会话，在错误处理分支增加资源释放代码：

ErrorHandler:
    ' 先释放打开的记录集
    If Not s Is Nothing Then
        If s.State = dbOpen Then s.Close
        Set s = Nothing
    End If
    If Not r Is Nothing Then
        If r.State = dbOpen Then r.Close
        Set r = Nothing
    End If
    ' 原有错误提示逻辑
    Dim i As Integer
    Dim st As String
    For i = 0 To Errors.Count - 1
        st = st & Errors(i).Description & vbCrLf
    Next i
    MsgBox st, vbCritical

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