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

Coldshoulder

Have you error in where clause, please print your strSQL debug result? lstServerNames may have string values, so Criteria would be a string, not a list of integers like 1,2,3,4 for ID's? – Harmon

The debug print out with 2 ID's picked, for the strSQL is UPDATE buildsheet SET [Roars Status] = "Submitted" WHERE ID IN(1670,1672) – Coldshoulder

Do ID's 1670,1672 exist in buildsheet table, sure. – Harmon

absolutely. I go into buildsheet to verify that the record changed or not. These record numbers are there. – Coldshoulder

ID must be set as Primary KEY in SQL Server 2008. – Harmon

ID is the primary key for this table. Also, as I mentioned in the original post, if I change ID to Identity = False, the script works. It's only when it's set to Identity = True that the script causes this error. – Coldshoulder

Identity != PM Key, have you noticed? – Harmon

yes, I understand that it HAS to be an identity field, and Primary key. I was just saying that the script is good otherwise. It's just this stupid dbSeeChanges error when the field is set correctly. – Coldshoulder

I succeeded your code with a test linked SQL Server table, please suppress parentheses from CurrentDb() => CurrentDb.Execute – Harmon

@CarlS Which line of your code is highlighted when you get that error message? – Ellette

Unforutately that has no affect on my computer. – Coldshoulder

the highlighted line is the "CurrentDb.Execute strSQL, dbSeeChanges " line – Coldshoulder

I'm not sure exactly what's different but I seem to have found a work around. To try differnet things, I created a new front end Access file, and copied my form and query to the new doc, then created a new link to the SQL database. The code worked without error. I tried deleting the link to the database in the original file and recreating it, but that still errored. I copied all the forms and queries from the original to the new, and the new still works. So it works now, just don't understand why the old doesn't if it's got all the same info as the new. – Coldshoulder

Your old Access DB has crashed... – Harmon

What error do you get with the dbSeeChanges option added? – Reliquary

Answer 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

Barrera

Answer 2

For me worked this:

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

dbFailOnError was the key...

Gwenn

Answer 3

CurrentDb().Execute strSQL, someotherConstant, dbSeeChanges

I think, someotherConstant maybe missing.

Stronghold

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/…. – Reliquary