Gunnar Peipman's ASP.NET blog

ASP.NET, C#, SharePoint, SQL Server and general software development topics.

Sponsors

News

 
 
 
DZone MVB

Links

Social

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

Comments

Wayne Clements said:

What can I do about doCmd.RunSQL when it demands that I use dbSeeChanges?

# September 2, 2009 9:08 PM

David Holley said:

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.

# July 8, 2010 4:29 PM

Glint said:

Thanks. It removed a substantial headache from me.

# September 3, 2010 6:00 PM

Bob said:

Thanks, this was exactly what I needed!

# February 17, 2011 6:09 PM

Basit Baig said:

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.

# March 9, 2011 4:29 AM

Mario I said:

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!

# April 21, 2012 3:39 AM