Thanks Graham, I will fire-up the SQL Server profiler (or ODBC trace, as suggested by John) and see what's happening.
The "True" should never make it through to the SQL Server but something like you suggext must be going on.
Andrew
--- In MS_Access_Professionals@yahoogroups.com, "Graham Mandeno" <graham@...> wrote:
>
> 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