ODBC插入失败错误3146：Access前端更新MySQL关联表异常

我通过ODBC连接器将MS Access前端与MySQL数据库相连，系统包含tblCustomer和tblPayments两张关联表（通过外键绑定）。编写VBA代码更新客户支付详情时，执行到rsCustomers.Update行触发「ODBC - inserting failed」错误，错误代码3146，排查指向数据类型不匹配（错误13），但修改表数据类型后问题仍未解决。

相关VBA代码 #

On Error GoTo Proc_Err

' variable for return from msgbox
Dim intRetValue As Integer
If Me.PaymentAmount = 0 Then
    MsgBox "You must enter a payment amount or cancel the transaction.", vbOKOnly
    Exit Sub
End If
If Me.txtPaymentVoucher < 1 Or IsNull(Me.txtPaymentVoucher) Then
    MsgBox "You must enter a voucher number.", vbOKOnly
    Me.txtPaymentVoucher.SetFocus
    Exit Sub
End If
If Me.TransactionType = "Debit" Then
    If Me.PaymentAmount > 0 Then
        Me.PaymentAmount = Me.PaymentAmount * -1
    End If
End If
If Me.PaymentReturnedIndicator Then
    If Me.PaymentAmount > 0 Then
        MsgBox "If this is a returned check enter a negative figure.", vbOKOnly
        Me.PaymentAmount.SetFocus
    End If
End If
If Me.PaymentCustomerID = 0 Then
    Me.PaymentCustomerID = glngPaymentCustomerID
End If
If gbolNewItem Then
    If Me.cboTransactionType = "Payment" Then
        Me.txtLastPayment = Date
    End If
End If
Me.txtCustomerBalance = (Me.txtCustomerBalance + mcurPayAmount - Me.PaymentAmount)
Me.txtPalletBalance = (Me.txtPalletBalance + mintPallets - Me.txtPallets)
  
Dim dbsEastern As DAO.Database
Dim rsCustomers As DAO.Recordset
Dim lngCustomerID As Long
Dim strCustomerID As String
Set dbs = CurrentDb()
Set rsCustomers = dbs.OpenRecordset("tblCustomers")

lngCustomerID = Me.PaymentCustomerID
strCustomerID = "CustomerID = " & lngCustomerID
rsCustomers.MoveFirst
rsCustomers.FindFirst strCustomerID
rsCustomers.Edit
rsCustomers!CustomerBalance = Me.txtCustomerBalance
rsCustomers!Pallets = Me.txtPalletBalance
rsCustomers!CustomerLastPaymentDate = Now()
rsCustomers.Update
rsCustomers.Close
Set rsCustomers = Nothing

FormSaveRecord Me
gbolNewItem = False
gbolNewRec = False
Me.cboPaymentSelect.Enabled = True
Me.cboPaymentSelect.SetFocus
Me.cboPaymentSelect.Requery
Me.fsubNavigation.Enabled = True
cmdNormalMode
Proc_Exit:
    Exit Sub
Proc_Err:
    gdatErrorDate = Now()
    gintErrorNumber = Err.Number
    gstrErrorDescription = Err.Description
    gstrErrorModule = Me.Name
    gstrErrorRoutine = "Sub cmdSaveRecord_Click"
    gbolReturn = ErrorHandler()                           ' Display the error message
    Resume Proc_Exit

End Sub

排查与解决建议 #

• 核对字段类型一致性：
  确认MySQL端tblCustomers的CustomerBalance（需与Access控件txtCustomerBalance的数值/货币类型匹配）、Pallets（整数/数值类型）、CustomerLastPaymentDate（DATE/DATETIME）字段类型，和VBA赋值的变量/控件值类型完全匹配。例如若MySQL的CustomerLastPaymentDate是DATE类型，VBA需改用Date()而非Now()，避免时间部分导致类型不兼容。
• 优化记录集查询：
  避免打开全表再查找，直接用条件查询打开目标记录，同时增加记录存在判断：

  Set rsCustomers = dbs.OpenRecordset("SELECT * FROM tblCustomers WHERE CustomerID = " & lngCustomerID, dbOpenDynaset)
  If rsCustomers.EOF Then
      MsgBox "未找到对应客户记录", vbOKOnly
      GoTo Proc_Exit
  End If
  

• 调试赋值内容：
  在rsCustomers.Edit前添加调试输出，检查要赋值的内容是否合法：

  Debug.Print "CustomerBalance: " & Me.txtCustomerBalance
  Debug.Print "Pallets: " & Me.txtPalletBalance
  Debug.Print "LastPaymentDate: " & Now()
  

  打开VBA即时窗口查看输出，确认是否存在空值、带格式的字符串（如含货币符号的数值）等异常值。
• 检查ODBC驱动与权限：
  使用MySQL官方最新版ODBC Connector，避免旧驱动的兼容性问题；同时确认MySQL账号拥有tblCustomers表的UPDATE权限，权限不足也会触发ODBC更新失败错误。
• 验证表单控件：
  确认txtCustomerBalance、txtPalletBalance未被设置为文本格式，且没有绑定错误的字段，避免控件值被自动转换为字符串类型。

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