Senin, 14 Mei 2012

RE: [MS_AccessPros] dbFailOnError Flag Wierdness

 

Andrew-

Thanks for all the details. I've reported your problem to internal support, and
they're looking into it. I'll let you know if I hear anything back.

John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

---------------------------------

From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of acravenrohm
Sent: Friday, May 11, 2012 5:48 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] dbFailOnError Flag Wierdness

 
WOW! Just did the SQL Profiler,

First of all, to lay all geese to rest, per copy/paste the code I am running in
the Access VBE Debug Window:

currentdb.Execute "UPDATE CheckinOuttab SET LetzteBlockEinheit=True WHERE
MNR=115033"
?DLookup("LetzteBlockEinheit","CheckinOuttab","MNR=115033")
-1
currentdb.Execute "UPDATE CheckinOuttab SET LetzteBlockEinheit=True WHERE
MNR=115033", dbfailonerror
?DLookup("LetzteBlockEinheit","CheckinOuttab","MNR=115033")
Null

(In case there is any confusion, the "-1" and "Null" are the output lines from
the DLookups.)

First things first, adding dbSeeChanges had no effect on the results, as
expected.

In the first case, the Profiler shows that Access looks up the primary key:

SELECT "dbo"."CheckInOutTab"."MitgliederPK" FROM "dbo"."CheckInOutTab" WHERE
("mnr" = 115033 )

Prepares a statement:

declare @p1 int
set @p1=11
exec sp_prepexec @p1 output,N'@P1 int',N'SELECT
"MitgliederPK","LetzteBlockEinheit","mnr","upsize_ts" FROM "dbo"."CheckInOutTab"
WHERE "MitgliederPK" = @P1',4984
select @p1

and then executes it:

exec sp_executesql N'UPDATE "dbo"."CheckInOutTab" SET "LetzteBlockEinheit"=@P1
WHERE "MitgliederPK" = @P2',N'@P1 bit,@P2 int',1,4984
exec sp_execute 11,4984

and then unprepares it. We see that Access translates the True into a 1.

Now for the dbFailOnError, guess what, no prepare and execute just:

UPDATE "dbo"."CheckInOutTab" SET LetzteBlockEinheit=NULL WHERE ("mnr" = 115033 )

Amazing and worrying.

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
>

__._,_.___
Recent Activity:
.

__,_._,___

Tidak ada komentar:

Posting Komentar