Execute SQL causes IDENTITY issue with one statement but not other

[03Explorer]

I have code that updates a table with two different but similar fields to be updated. Neither updates deal directly with an IDENTITY column. However ONE of them uses the IDENTITY column in the WHERE statement. The 2nd code works like expected... the first crashes saying "You must use the dbSeeChanges option with OpenRecordset when accessing a SQL Server table that has an IDENTITY column."

Table in question is a linked table (MS SQL table)

SQL = "UPDATE tblSurveysPreGenerated SET [PreGeneratedStatus] = 3, [GeneratedDate] = #" & Date & "#, [GeneratedTime] = #" & Format(Now(), "hh:nn:ss") & "#, [Processed] = 1 WHERE PreGeneratedAutoID = " & rsPreGenerated!PreGeneratedAutoID & "; "
db.Execute SQL

SQL = "UPDATE tblSurveysPreGenerated SET [PreGeneratedStatus] = 4, [Processed] = 1 WHERE isNull([Processed]) AND isNull([GeneratedDate]) AND isNull([GeneratedTime]); "
db.Execute SQL

Recap: line of code with 3 crashes; the line of code with 4 works

Any rhyme to the reason why 3 code is giving IDENTITY error and not 4?

 

[dhookom]

I think this is just a quirk of SQL server updates from Access. You can add the ,dbSeeChanges to the Execute statement.

I typically have a generic pass-through query that I update the SQL property and then Execute.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[03Explorer]

Thanks Duane, I ended up using dbSeeChanges

The problem is not there, but it still leaves me to wonder

 

[combo]

MS states that "You should also use the dbSeeChanges constant if you open a Recordset in a Microsoft Access database engine-connected ODBC workspace against a Microsoft SQL Server 6.0 (or later) table that has an IDENTITY column, otherwise an error may result."

combo

 

[03Explorer]

MS states that "You should also use the dbSeeChanges constant if you open a Recordset in a Microsoft Access database engine-connected ODBC workspace against a Microsoft SQL Server 6.0 (or later) table that has an IDENTITY column, otherwise an error may result."

Thank you, great to know.
=)