Andrew
You have shown you are a very knowledgeable developer, but we still have
questions. If MNR is a long why are you using CStr()?
Every time I've ever used dbFailOnError with a SQL back end I was required to
add in dbSeeChanges. I really don't get how you could run your .execute without
it. It does not prevent error trapping as far as I know. It just silently fails.
You should be able to read AffectedRecords to see if the update committed.
And why are you opposed to using SET LastBlock = 1 ?
What do you get if you run that query in the Management studio?
Regards,
Bill
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of acravenrohm
Sent: Thursday, May 10, 2012 2:54 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Re: dbFailOnError Flag Wierdness
Bill,
MNR is a (Long) Integer field. If I were to include quotes the SQL parser
should, would, does, throw an error.
There is a case to say that the dbSeeChanges should also be there however, not
using it when it is necessary results in a trapable error and the whole point
about this is that I am not getting an error reported and it is only when I tell
the Engine to inform me of any errors that the whole wierdness happens.
When I get a chance I will do a test to see if the dbSeeChanges makes a
difference to the outcome but in either case, this is a bug as leaving out the
dbSeeChanges should result in a trapable error and not to erasing the data in
the field.
--- In MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com> , "Bill Mosca" <wrmosca@...>
wrote:
>
> 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
<mailto:MS_Access_Professionals%40yahoogroups.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%40yahoogroups.com>
> > [mailto:MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of acravenrohm
> > Sent: Thursday, May 10, 2012 1:57 PM
> > To: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.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
<mailto:MS_Access_Professionals%40yahoogroups.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%40yahoogroups.com>
> > > [mailto:MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of acravenrohm
> > > Sent: Wednesday, May 09, 2012 3:44 PM
> > > To: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.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]
> > >
> >
>
[Non-text portions of this message have been removed]
Tidak ada komentar:
Posting Komentar