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.
View Comments (6)
What can I do about doCmd.RunSQL when it demands that I use dbSeeChanges?
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.
Thanks. It removed a substantial headache from me.
Thanks, this was exactly what I needed!
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!