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