dbSeeChanges error.

[Richie27]

Since I've converted to SQL Server, I now get the following error:

"You must use the dbSeeChanges option with OpenRecordSet when accessing a SQL Server table that has an IDENTITY column." It didn't happen before. Any help would be super. This has been driving me cracked for hours. I have no doubt it's related to the following:

mySQL = "INSERT INTO tblSuFileActions (fkFileId,fkActionId,actionAuthoriser,actionDate,actionReason, actionPerson, actionCompany)"
mySQL = mySQL & " SELECT file_id," & Me.cboAction & "," & Me.cboAuthoriser & ",#" & Me.txtDate & "#, '" & Me.txtReason & "', " & Me.cboPerson & ", " & cboCompany & " "
mySQL = mySQL & " FROM (SELECT tblSuFile.file_id"
mySQL = mySQL & " FROM tblSuBoxLocation INNER JOIN tblSuFile ON tblSuBoxLocation.box_ID = tblSuFile.box_id"
mySQL = mySQL & " WHERE tblSuBoxLocation.box_ID=" & Me.cboBoxNo & " )"


CurrentDb.Execute mySQL, dbFailOnError

 

[pbaldy]

Don't have Access in front of me, but try

CurrentDb.Execute mySQL, dbFailOnError + dbSeeChanges

 

[Richie27]

Don't have Access in front of me, but try

CurrentDb.Execute mySQL, dbFailOnError + dbSeeChanges

Ta - the '+' part was what I was missing - ta!

 

[pbaldy]

Happy to help. It's a common issue when converting a back end to SQL Server. Ran into it myself many moons ago.

 

[Pat Hartman]

Also, earlier versions of SQL server didn't require it but newer versions do so upgrading SQL Server may also generate this error.