Hello Andrew
This is a complete stab in the dark, but I wonder if perhaps Jet does some
extra pre-processing when dbFailOnError is specified, so that:
SET LastBlock=True
gets converted to:
SET LastBlock=-1
Perhaps True is interpreted by SQL correctly as a boolean value, but -1 is
not, since SQL uses 1 for True.
I have no idea how you could test this hypothesis, but it's just a thought.
Cheers,
Graham
> From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of acravenrohm
> Sent: Thursday, 10 May 2012 10:44
> To: MS_Access_Professionals@yahoogroups.com
> Subject: [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
Tidak ada komentar:
Posting Komentar