You must use the dbSeeChanges option with OpenRecordset when accessing a SQL server table that has an IDENTITY column

Posted on by Jordansphere

We just upgraded an internal MS Access 2003 DB to  use  MS SQL 2008  (in linked server table format) and immediately got the error listed in the title (and pictured below)

 

The debug option gave the following line as the problem:

Set rs = db.OpenRecordset(sql)

I tried

Set rs = db.OpenRecordset(sql, dbSeeChanges)

but just got a random error. However the following did the job:

Set rs = db.OpenRecordset(sql, dbOpenDynaset, dbSeeChanges)