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

 

NB! This blog is moved to gunnarpeipman.com

Click here to go to article

6 Comments

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

Comments have been disabled for this content.