Update Sql Query Fails

[Vincent Ball]

We have a script that has run correctly for a long time which does Select and Update queries against an SQL Server database.

 

Now getting an error, although not aware of any changes made to script or environment.

 

114908.31 6 rv replace end: [uPDATE tblRand SET StudyID = 1000022, URNumber='0502449', DateRand = Format(#27/06/2012#, "mm/dd/yyyy"), TimeRand = #11:48:18 AM#, GArandwk = 30,GAranddy = 4,MDOB = Format(#30/08/74#, "mm/dd/yyyy") WHERE RandID=7]

114908.31 6 db About to execute a Modify type operation [uPDATE tblRand SET StudyID = 1000022, URNumber='0502449', DateRand = Format(#27/06/2012#, "mm/dd/yyyy"), TimeRand = #11:48:18 AM#, GArandwk = 30,GAranddy = 4,MDOB = Format(#30/08/74#, "mm/dd/yyyy") WHERE RandID=7]

114908.31 6 ERROR 5.2.5049 DB Execute: 3622,You must use the dbSeeChanges option with OpenRecordset when accessing a SQL Server table that has an IDENTITY column.

114908.33 6 rv lg add [MagentaUpdateTblRand_RowCount]{0}

114908.33 6 db DBEngine.Error nbr:[3622] desc:[You must use the dbSeeChanges option with OpenRecordset when accessing a SQL Server table that has an IDENTITY column.] src:[DAO.Database]

114908.33 6 path {0} not found

114908.33 6 path {} not found

114908.33 6 timer clear

114908.33 6 state [MagentaRandIDProblem] Send Email

 

We are familiar with the need to use the dbSeeChanges option when executing this query in VBA, but can not understand why VG needs it or how we would specify it.

 

The error is factually correct, the table tblRAND does have an Identity column. But this has been working without errors for months!

 

Any ideas?

[SupportTeam]

The error is returned by your database in response to the SQL query, and VoiceGuide is just passing it on.

 

You would need to determine why your database is returning this error.

[Vincent Ball]

Thanks for your quick feedback.

 

Luckily we were able to remove the IDENTITY from the table and it works again.

 

Very mysterious as table always had IDENTITY and VG was successfully passing through the Update queries until recently. I can not determine what changed.

 

The error says that dbSeeChanges needed when doing OpenRecordset, so I presume that VG does an OpenRecordset before issuing the SQL Query. In which case it should be using the dbSeeChanges option if the query is to work on an SQL Server database.

 

Anyway.....

 

Thanks........ Vincent