John
Why is there no need for quotes around the variable?
-Shouldn't-
WHERE MNR=" & CStr(lMNr)
-be?-
WHERE MNR='" & CStr(lMNr) & "'"
As to why dbFailonError isn't working, when you use it with a SQL back end you have to also use dbSeeChanges as in:
db.Execute strSQL, dbFailOnError + dbSeeChanges
Bill
--- 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]
> >
>
Tidak ada komentar:
Posting Komentar