Kamis, 10 Mei 2012

[MS_AccessPros] Re: dbFailOnError Flag Wierdness

 

This thread is wierder than the original problem ;-)

I went to the trouble of changing the code to remove some Germanisms and then testing it to make sure it still behaved wierdly. Yes, I could have explained that MNR is a "Member NumbeR", i.e. a Number, and I could have explained that the l in front of the lMNR is my short hand for a long, and yes I could have left it in the original form which was: & Me!Mitgliedsnummer but as this was completely undefined as to it's type I decided to show it as a cStr(lMNr) which I naively thought would be clear to anyone who has argued over naming conventions.

I come from the old school of "use as few cpu cylces as possible" so for me concatenating strings with + is natural, I learnt that using & makes the runtime check the types of the variables to see if it needs to implicitly convert them. However, even if this is now an "urban legend", it has nothign to do with the problem.

If for some reason there was a NULL in the values I am using on the line of code then I have error handling, in fact I have the fantastically clever Error Handling from Wayne Phillips' EverythingAccess.com, so in the unlikely, but possible, event that the Me!MNR is empty/NULL I will catch it. I added the dbFailOnerror when I realised that the code was inadequate without it as an error at the database end would otherwise go unnoticed. Telling Access that I wanted to be informed if an error occured seems to confuse Access/SQL into causing an error and then not telling me about it... this is why it is so *strange*, at least for me.

I am also aware of what (1=1) means, this is the reason why I chose to change the syntax to be valid no matter what the database (Access has always got it right before, without me having to tell it how to deal with bit/boolean differences.)

--- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@...> wrote:
>
> Andrew-
>
> I think Bill was taking a stab in the dark. It's true that using + with two
> strings will result in concatenation unless one of the fields is Null. CStr
> would bomb if you passed it a Null, so the + should work. But Bill is correct.
> Use & when you mean to concatenate two strings unless you plan to do Null
> elimination.
>
> Your "works in all cases" line works because you're providing a true Boolean
> value (1 = 1) for the assignment. Although Access *should* convert True to -1,
> it might not. And in any case, -1 is incorrect for SQL Server. This should
> also work:
>
> CurrentDb.Execute "UPDATE CheckInOutTab SET LastBlock=1 WHERE MNR=" &
> CStr(lMNr), dbfailonerror
>
> I'm wondering why you're using CStr. What's the data type of lMNr?
>
> As to why adding dbFailOnError caused the "= True" to fail, I have no clue.
> There's no information hinting at that in the Help topic:
>
> http://msdn.microsoft.com/en-us/library/bb243015(v=office.12).aspx
>
>
> 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: Thursday, May 10, 2012 1:57 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: [MS_AccessPros] Re: dbFailOnError Flag Wierdness
>
>  
> Bill, thanks for taking your time but...
>
> The actual code looks slightly different and does indeed use the concatenation
> operator but you will also see that the first part is a string and that the
> second part has a CStr() and that if the lMNR (which is a long) was a NULL and
> *was* the problem then I would be getting an error, either with the plus
> operator or from the SQL parser.
>
> Secondly, this is legacy code and originally the Table was linked in from an
> Access DB so the "True" was OK and in all other cases Access translates the
> "True" into the appropriate Value for the Fieldtype being addressed. My Patch
> replaced the "True" with "(1=1)" and without the dbFailOnError it had been
> working perfectly.
>
> Thirdly, Access deals quite nicely with NULL and I use it extensively (although,
> I like to think, responsibly ;-) )
>
> Furthermore, the field *does* have a Default Value attached to it, the default
> value is 0. However, the fact that it has a default value is only relevant when
> inserting a record.
>
> There is indeed a lot that I need to understand but, I think I can safely say,
> these things I do understand.
>
> (Yes, the field *should* be defined "NOT NULL" but in this case changing it's
> not an option in this environment.)
>
> yours, Andrew
>
> --- In MS_Access_Professionals@yahoogroups.com, "Bill Mosca" <wrmosca@>
> wrote:
> >
> > Andrew
> >
> >
> >
> > I see something right away. You may not be aware that VBA treats a + operator
> in
> > more than one way. If it is a string and the string is null you get Null. If
> it
> > is not null you get a contatenation. If it is a number you get a math
> > expression.
> >
> >
> >
> > All around a very unreliable way to code. Use a + operator with strings only
> if
> > you understand how they act. It is best to use an ampersand (&). And by all
> > means slap some quotes around strings.
> >
> >
> >
> > Lastly, SQL treats bit fields as 1, 0 or null. See another way where your code
> > is sloppy?
> >
> >
> >
> > CurrentDb.Execute "UPDATE CheckInOutTab SET LastBlock=1 WHERE MNR='" &
> > CStr(lMNr) & "'", dbfailonerror
> >
> >
> >
> > As I discussed in my article, "Working with SQL Server Linked Tables: The
> Hidden
> > Gotchas" on using SQL back ends at
> > http://www.thatlldoit.com/Pages/howtosarticles.aspx , You have to specify a
> > default value so you don't end up with any nulls in that field. SQL can handle
> > them...JET cannot.
> >
> >
> >
> > I know I dumped a lot on you for one post but there is a lot involved that you
> > need to understand.
> >
> >
> >
> >
> >
> > Regards,
> > Bill Mosca,
> > Founder, MS_Access_Professionals
> > That'll do IT <http://thatlldoit.com/> http://thatlldoit.com
> > MS Access MVP
> > <https://mvp.support.microsoft.com/profile/Bill.Mosca>
> > https://mvp.support.microsoft.com/profile/Bill.Mosca
> >
> >
> >
> > From: MS_Access_Professionals@yahoogroups.com
> > [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of acravenrohm
> > Sent: Wednesday, May 09, 2012 3:44 PM
> > 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
> >
> >
> >
> >
> >
> > [Non-text portions of this message have been removed]
> >
>

__._,_.___
Recent Activity:
.

__,_._,___

Tidak ada komentar:

Posting Komentar