Unable to update table through CurrentDb.Execute

问题:

Ok, before anyone tells me that it's a duplicate of another question, I don't think it's the same thing. I'm running a CurrentDb.Execute in VBA in MS Access 2007 and it's after I put the option dbFailOnError that I received this error message:

Error 3218 Could not update; currently locked

Before this, nothing was being done on the table I'm trying to update. Here's the current code in the form module:

Option Compare Database

Private Sub cmdvalidate_Click()
    Dim user As String
    Dim rs As Recordset
    Dim strsql As String
    Dim pass As String
    Dim f1 As Form_frmMenu

    strsql = "SELECT * FROM account WHERE username = '" & Me.txtusername & "'"

    'Execute SQL and store in recordset (cannot be stored in a string or integer)
    Set rs = CurrentDb.OpenRecordset(strsql)

    'Go through recordset and extract first value
    If rs.RecordCount > 0 Then

        rs.MoveFirst
        rs.Edit
        pass = rs!Password

        ' I know it's not the best way to validate password here (since it is
        ' case insensitive, but it's only for internal use with 3 people in
        ' the same department. This will considered if there are more people
        ' who get involved, but for the time being, this is not important.
        If pass = Me.txtpassword Then
            user = rs!UserName

' MS Access debugger says the error is here
CurrentDb.Execute "UPDATE [Account] SET [Account].[Active] = 'Y' WHERE [Account].[Username] = '" & user & "'", dbFailOnError

            MsgBox "Login Successful!"
            'DoCmd.OpenForm "frmMenu", , , , , acDialog
        Else
            MsgBox "Incorrect Username Or Password. Please try again."
        End If
    Else
        MsgBox "Something has gone wrong. Please contact your administrator."
    End If       
End Sub

---

My goal at the end of the day is to be able to get the username who logged in. I first tried to use a global variable but it seems that this is not possible with different form modules. So instead, I created the Active field so that I could get the username in other form modules through a SELECT statement.

Some checks I did:

• The file is not opened by anyone besides me, I'm 100% sure of it.
• The query was initially like below but I added the square brackets just in case there were reserved words:

"UPDATE Account SET Active = 'Y' WHERE Username = '" & user & "'"

• Other form modules in the same file do not have this UPDATE issue and there are no other options other than Option Compare Database (the other stuff are Private Subs).
• I discovered that I could use Environ("Username") to get the Windows login username in the other modules instead. This is will solve the problem, but I'm still wondering why this query cannot execute the update.

---

Questions:

1. If the table is locked, is there a way I can unlock it?
2. Why is this query getting an error in the first place? What has locked it?

I don't know much, if at all about vb and I'm not even sure how I got on this project...

回答1:

1.) If the table is locked, is there a way I can unlock it?

I'm not so sure the entire table is locked. Perhaps only the current recordset row is locked. See #2.

2.) Why is this query getting an error in the first place? What has locked it?

The code does rs.Edit. Later, when attempting to UPDATE the table directly, the targeted row is the same row which is currently locked in the recordset. However, there is a lot more code there, which makes it difficult to pin down which statements contribute to the error.

This simplified code sample should clarify what's going on.

strSql = "SELECT * FROM account WHERE username = 'hans'"
Set rs = CurrentDb.OpenRecordset(strSql)
rs.MoveFirst
rs.Edit
CurrentDb.Execute "UPDATE Account SET Active = 'Y' WHERE Username = 'hans'", dbFailOnError

As written, that code triggers error 3218, "Could not update; currently locked", on my system.

This change disables the Edit statement and allows the UPDATE to execute successfully.

'rs.Edit

One way you found to avoid the error was by adding rs.Close before executing the UPDATE. That worked because closing the recordset released that edit lock; if you had actually changed values in that row, you would have needed rs.Update to save them before rs.Close.

However, in the original version, you were only reading values from the recordset, not changing any of them. So rs.Edit was not needed.

回答2:

Huh, I think I just found out what was causing the error.

Apparently, when a RecordSet is using a table (here the line Set rs = CurrentDb.OpenRecordset(strsql)), the table can be accessed only through the RecordSet and not through CurrentDb.Execute anymore (at least, to update).

Two workarounds I found for my specific situation:

1. Replace the CurrentDb.Execute by something to update the RecordSet itself (Probably easier):

   rs!Active = "Y"     ' Change the value of Active
   rs.Update           ' Update the changes to the RecordSet and thus, table
   rs.Close            ' Close recordset
   Set rs = Nothing    ' Unset recordset
   

2. Close the RecordSet first, then use CurrentDb.Execute:

   rs.Close
   Set rs = Nothing
   CurrentDb.Execute "UPDATE Account SET Active = 'Y' WHERE Username = '" & user & "'", dbFailOnError