Kamis, 10 Mei 2012

[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