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.


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

Leave a Reply

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