通过MS Access 97更新VBA数据库，记录锁违规

Question

我们使用Access 97数据库前端连接MSSQL和MySQL服务。所讨论的记录位于MySQL数据库中。我得到以下错误:

Work Opportunity Tax Credit can't update all the records in the update query.
Work Opportunity Tax Credit didn't update 0 field(s) due to a type conversion failure, 0 record(s) due to key violations, 1 record(s) due to lock violations, and 0 record(s) due to validation rule violations.
Do you want to continue running this type of action query anyway?
To ignore the error(s) and run the query, click Yes.
For an explanation of the causes of the violations, click Help.

所讨论的代码如下;

Public Function GetReferedReportName(intRepID As Integer) As String
    Dim intRev As Integer
    GetReferedReportName = trim(str(intRepID))
    '
    'Lookup the revision, and append to report name if > 0
    '
    intRev = DLookup("Revision", "ReferredReport", "rptID=" & intRepID)
    If intRev > 0 Then
        GetReferedReportName = trim(str(intRepID)) & "." & trim(str(intRev))
    End If
End Function
Private Sub cmdEmail_Click()
    Dim strSourceFileName As String
    Dim strDestinationFileName As String
    Dim fso As New FileSystemObject
    On Error GoTo cmdEmail_ClickErr
    'Setup the source and destination file names
    strSourceFileName = strLocationReferedReports & "" & GetReferedReportName(Me.cboReport.Column(2)) & ".pdf"
    strDestinationFileName = ConstDestLocation & Format(Me.cboReport.Column(4), "YYYYMMDD") & "to" & Format(Me.cboReport.Column(1), "YYYYMMDD") & ".pdf"
    '
    'Create copy of file, email, then delete copy of file
    '
    fso.CopyFile strSourceFileName, strDestinationFileName
    Call NewEmail("Referred Report", strDestinationFileName)
    fso.DeleteFile strDestinationFileName
    Set fso = Nothing
    ' This sets the SentToClient for email automatically.
    Me.frmSentToClient.Value = 1
    Me.chkSenttoClient = True
    DoCmd.RunSQL "UPDATE ReferredReport SET SentToClient = 1 WHERE rptID = " & Me.cboReport.Column(2)
cmdEmail_ClickErr:
    Resume Next
End Sub

引起问题的行是;

DoCmd.RunSQL "UPDATE ReferredReport SET SentToClient = 1 WHERE rptID = " & Me.cboReport.Column(2)

我知道里面有一堆没有在子程序中定义的行。有人认为这将是一个好主意，把一堆变量放入数据库，而不是他们将被使用。在某种程度上，我确信这是有意义的。

我想知道是否有人知道为什么RunSQL命令不工作。

Answer 1

在解决问题后，我发现它被锁定的原因与锁定表无关。正在调用错误消息，因为它无法更改所讨论的表。

不能修改表的原因是它试图更新的表项已经是它试图更新的值。例如，如果SentToClient已经是1，那么更新将返回0条更新的记录。Access 97并没有考虑到这一点，所以它抛出了一个错误。我通过检查条目来查看是否需要更新来修复它。

编辑:

If dlookup("SentToClient","ReferredReport","rptID = " & Me.cboReport.Column(2)) <> 1 Then
    DoCmd.RunSQL "UPDATE ReferredReport SET SentToClient = 1 WHERE rptID = " & Me.cboReport.Column(2)
Endif

Answer 2

我猜带有cmdEmail的表单绑定到表ReferredReport。

现在这些行

Me.frmSentToClient.Value = 1
Me.chkSenttoClient = True

编辑当前记录，而下面的UPDATE试图编辑相同的记录，从而创建一个锁冲突。

和表单。我认为，RecordLocks必须设置为"已编辑的记录"。

最简单的解决方案是在执行UPDATE之前保存记录:

Me.Dirty = False

，但也许你可以完全摆脱UPDATE，因为它可能会编辑与Me.chkSenttoClient = True相同的字段。

注:我建议不要对一个你正在提问的工具表达你的憎恨。此外，Access 97是一个非常可靠的版本。