You must use the dbSeeChanges option with OpenRecordSet when accessing a SQL Server table that has an IDENTITY column

One error you may get when querying SQL Server databases through MS Access is "You must use the dbSeeChanges option with OpenRecordSet when accessing a SQL Server table that has an IDENTITY column". This error appears when you open recordset that contains IDENTITY column.

Usually you have to do something like this to get this error (FindTrainer query gets data from SQL Server table).

Public Function GetTrainerId(strTrainerName As String) As Integer
Dim query As QueryDef
Dim rs As Recordset

    Set query = CurrentDb.QueryDefs("FindTrainer")
    query.Parameters("pName") = strTrainerName
    
    Set rs = query.OpenRecordset()
    
    If rs.RecordCount = 0 Then
        rs.AddNew
        rs("Name") = strTrainerName
        rs.Update
        rs.MoveLast
        rs.MoveFirst
    End If
    
    GetTrainerId = rs("ID")
    
    Set rs = Nothing
    Set query = Nothing
    
End Function

To avoid this error you should use dbSeeChanges option when opening the recordset. You can see that I am using two parameters when I open recordset. First one, dbOpenDynaset, sais to Access that I need dynamic recordset and second one, dbSeeChanges, sais that there may be changes that are made in server and we need to retrieve row again after inserting or updating it.

Public Function GetTrainerId(strTrainerName As String) As Integer
Dim query As QueryDef
Dim rs As Recordset

    Set query = CurrentDb.QueryDefs("FindTrainer")
    query.Parameters("pName") = strTrainerName
    
    Set rs = query.OpenRecordset(dbOpenDynaset, dbSeeChanges)
    
    If rs.RecordCount = 0 Then
        rs.AddNew
        rs("Name") = strTrainerName
        rs.Update
        rs.MoveLast
        rs.MoveFirst
    End If
    
    GetTrainerId = rs("ID")
    
    Set rs = Nothing
    Set query = Nothing
    
End Function

After adding these two parameters to recorset opening calls the error disappeared and everything started to work normally.

Gunnar Peipman

Gunnar Peipman is ASP.NET, Azure and SharePoint fan, Estonian Microsoft user group leader, blogger, conference speaker, teacher, and tech maniac. Since 2008 he is Microsoft MVP specialized on ASP.NET.

    7 thoughts on “You must use the dbSeeChanges option with OpenRecordSet when accessing a SQL Server table that has an IDENTITY column

    • September 3, 2009 at 1:08 am
      Permalink

      What can I do about doCmd.RunSQL when it demands that I use dbSeeChanges?

    • July 8, 2010 at 8:29 pm
      Permalink

      rs.MoveFirst and rs.MoveLast is not a reliable means of obtaining the ID of the newly created record unless you have an exclusive lock on the underlying table.

    • September 3, 2010 at 10:00 pm
      Permalink

      Thanks. It removed a substantial headache from me.

    • February 17, 2011 at 11:09 pm
      Permalink

      Thanks, this was exactly what I needed!

    • March 9, 2011 at 9:29 am
      Permalink

      Thank you very much, its really help for me and things are very much smooth.
      Please can you help me for this function as well, i want to execyte a Update query through this method, its runs but the result is not as per requirement.

      CurrentDb.Execute(myqry)
      what is best option to use this method if we have SQL Server at backend.

    • April 21, 2012 at 7:39 am
      Permalink

      Thank you, I wasn’t sure where to add dbSeeChanges until I saw your posting. This worked for me (finds the contact selected and deletes it from the current project):

      Dim dba As DAO.Database
      Dim rst As DAO.Recordset
      Dim strFind As String

      Set dba = CurrentDb
      Set rst = dba.OpenRecordset(“ContactAssoc”, dbOpenDynaset, dbSeeChanges)

      strFind = “[AssocID]=” & Me!AssocID

      rst.FindFirst strFind

      rst.Delete

      Set rst = Nothing
      Set dba = Nothing

      Thanks again!

    • Pingback:Dbseechanges Option With Openrecordset When Accessing A Sql Server | yuyu

    Leave a Reply

    Your email address will not be published. Required fields are marked *