Bill-
That's why asked the purpose of using CStr. Doesn't make sense. If it's
working as it is, then lMNr and MNR must both be numbers.
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 Bill Mosca
Sent: Thursday, May 10, 2012 5:31 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Re: dbFailOnError Flag Wierdness
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