Error 3622 - You must use the dbSeeChanges option with OpenRecordset when accessing a SQL Server table that has an IDENTITY column

Question

I'm running MS Access 2007, connecting to a MS SQL 2008 R2 server. I have a form with a multiselect box that I use to update status for several fields for the servers selected. Currently I'm using the ServerName field in the multiselect box. The problem is that there can be multiple records with the same server name. So to get around this I want to change it from ServerName to record number (ID). When I do this though VB gives the error "Error 3622 - You must use the dbSeeChanges option with OpenRecordset when accessing a SQL Server table that has an IDENTITY column". I've looked at many different posts on different websites, but can't figure out how to implement this into my code. The script works perfectly when I use ServerName, it also works perfectly if I go to the SQL server and change the field to Identity = False. The problem with that is that I can't create new records with the autonumber. It also works perfectly if I hard code the line numbers in an Update query. The problem with that is I can't hardcode the line numbers for everyone that uses the database. The issue only appears to be related to VB. Below is what I have currently. As you can see I tried adding the dbSeeChanges to the Execute line.

Private Sub btnRoarsStatus_Click()
Dim strSQL As String
Dim Criteria As String
Dim Itm As Variant

With Me.lstServerNames

    If .ItemsSelected.Count > 0 Then
          For Each Itm In .ItemsSelected
              Criteria = Criteria & "," & .ItemData(Itm)
           Next Itm

          ' remove leading comma
           Criteria = Mid(Criteria, 2)

           ' execute the SQL statement
            strSQL = "UPDATE buildsheet SET [Roars Status] = " & Chr(34) & _
            Me.cboRoarsStatus & Chr(34) & " WHERE ID IN(" & Criteria & ")"

            Debug.Print strSQL
         CurrentDb().Execute strSQL, dbSeeChanges

      Else
          MsgBox "You must select one or more items in the  list box!", _
                  vbExclamation, "No Selection Made"
                  Exit Sub
      End If
  End With
 MsgBox "Completed", vbExclamation, "Completed"
End Sub

Solution 1

I had a similar Problem with a Delete Statement that I wanted to execute and solved it by:

CurrentDb.Execute SqlStr, dbSeeChanges

note the missing () after CurrentDb

Solution 2

For me worked this:

CurrentDb.Execute strSQL, **dbFailOnError +** dbSeeChanges

dbFailOnError was the key...

Comments

[Caltor]

No the someotherConstant to which you refer is a RecordsetTypeEnum constant that indicates the type of Recordset to open and is used with OpenRecordset() but not Execute() as per this question. Compare msdn.microsoft.com/en-us/library/office/… and msdn.microsoft.com/en-us/library/office/….