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]
>
Tidak ada komentar:
Posting Komentar