Selasa, 15 Mei 2012

Re: [MS_AccessPros] dbFailOnError Flag Wierdness

 

Super,

thanks John.

In the meanwhile, I shall have to bite the bullet and check all CurrentDB.Execute calls to see if they include anything which might require conversion from Access to SQL Server Syntax.

(I shall at some point do another test to see if the refactoring DoCmd.RunSQL to CurrentDB.Execute really does have its advantages... In the case of using dbFailOnError, it really would be much quicker as there's only one Network round-trip, but, quicker and wrong is worse than slower but right.)

--- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@...> wrote:
>
> 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