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.