Rabu, 09 Mei 2012

[MS_AccessPros] dbFailOnError Flag Wierdness

 

Anyone seen this before?

I made a change to an Application, tested it and just before releasing it to testing (I know, bad Idea) I changed:

CurrentDb.Execute "UPDATE CheckInOutTab SET LastBlock=True WHERE MNR=" +CStr(lMNr)

to

CurrentDb.Execute "UPDATE CheckInOutTab SET LastBlock=True WHERE MNR=" +CStr(lMNr), dbfailonerror

I should explain that LastBlock is a bit field on an SQL Server where the CheckInOutTab is linked in (and we're running Access 2007).

The testers failed the new feature and tracing the problem led to this line of code.

That simple addition of the dbFailOnError flag makes Access set the bit Field to NULL rather than 1. Removing the dbFailOnErrror makes it correctly set it to 1. Setting to False sets the bit value to 0, with or without the dbFailOnError.

There is a trigger on the Table but it's an insert trigger, not an Update trigger and even if the trigger was throwing an error, I should have been getting the error reported when using the dbFailOnError flag and the field should not have been changed in either case. But no, with error checking switched off, the update is successful, with error checking turned on, the update fails and not only fails but it writes NULL into the table. (Please, don't tell me that the field should be defined with NOT NULL...)

The "works in all cases" line now reads:

CurrentDb.Execute "UPDATE CheckInOutTab SET LastBlock=(1=1) WHERE MNR=" +CStr(lMNr), dbfailonerror

I am now, of course, somewhat shocked by this and wondering if something similar is going on with similar lines of code elsewhere. Especially as I started to refactor some old DoCmd.RunSQL lines to currentDB.Execute due to alleged better performance and error catching...

Ideas? Or even links to more knowledge/Hotfixes?

Yours,
Andrew

__._,_.___
Recent Activity:
.

__,_._,___

Tidak ada komentar:

Posting Komentar