X

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.

Liked this post? Empower your friends by sharing it!
Categories: Data platform

View Comments (6)

  • 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.

  • 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.

  • 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!

Related Post