Using CStr() is a habit I developed sometime in the dim-and-distant past. And, again, the actual code does not use CStr() but does infact use: & Me!Mitgliedsnummer
dbSeeChanges is mandatory when opening recordsets. I Suspect it tells the underlying layers to look for a timestamp field in the remote tables and silently include it in its internal workings, if none is found it silently includes *all* fields. This is a .Execute
I am not opposed to setting "=1", I just have bad experiences with doing so. Which would be another long, on going, story about fields having 1 and -1 values meaning the same thing.
RecordsAffected must have been 1 as it did actually change the data in the field in one record, just not how it should have done and did without the FailOnError.
--- In MS_Access_Professionals@yahoogroups.com, "Bill Mosca" <wrmosca@...> wrote:
>
> 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