[AccessD] db execute on linked vs local table

Tue Feb 1 17:48:42 CST 2022

Today's Daily WTF!

https://thedailywtf.com/articles/numb-to-truth

On 1 Feb 2022 at 9:08, Ryan W wrote:

> https://i.imgur.com/WD15dsv.png
> 
> 
> 
> 
> On Tue, Feb 1, 2022 at 8:44 AM James Button via AccessD <
> accessd at databaseadvisors.com> wrote:
> 
> > NO
> > That's the trap
> >
> > Almost all coding and data processing facilities consider 0 to be a
> > numeric representation of FALSE as in a way to store and test for
> > FALSE as in the stored or tested value being zero, and ideally
> > binary value at that.
> >
> > As far as programming and using apps to check for TRUE - some  will
> > test > 0, some <0, and many <> 0
> >
> > With YOU having to be aware that a float version of decimal 0.00 may
> > not actually be zero, but have a non-zero as the least significant
> > digit of a floating point version of the number that was actually
> > entered.
> >
> > So =0 is not a good test  neither is testing one numerical value -
> > another
> >  Better to test   the abs of the difference is less than  the
> >  accuracy you
> > want - remembering the 16 decimal digit limit to numbers  on many
> > CPU's
> >  As in 12345678901234500 + 1 is still 12345678901234500
> > And
> >  As in 123456789012345 + 0.12 is still 123456789012345
> >
> > JimB.
> >
> > -----Original Message-----
> > From: AccessD <accessd-bounces+jamesbutton=
> > blueyonder.co.uk at databaseadvisors.com> On Behalf Of Ryan W
> > Sent: Tuesday, February 1, 2022 1:38 PM
> > To: Access Developers discussion and problem solving <
> > accessd at databaseadvisors.com>
> > Subject: Re: [AccessD] db execute on linked vs local table
> >
> > So I´ve got what is likely a naïve question but if access
> > considers true -1 and SQL Server considers true one why do the
> > checkboxes work when you interact with them is it just because
> > anything that´s not zero is true?
> >
> > Sent from my iPhone
> >
> > > On Feb 1, 2022, at 4:36 AM, James Button via AccessD <
> > accessd at databaseadvisors.com> wrote:
> > >
> > > Re NOT FALSE
> > >
> > > From memory that has been a long standing advice  as  various
> > 'programming' languages  and Apps have different ideas of what
> > equates to TRUE
> > >
> > > As in Office and VBA  differ
> > > Similar concerns apply to ROUND with values that are 1/2 of the
> > > rounding
> > level.
> > > And the handling of bit values stored in Bytes.
> > >
> > > JimB
> > >
> > >
> > > -----Original Message-----
> > > From: AccessD <accessd-bounces+jamesbutton=
> > blueyonder.co.uk at databaseadvisors.com> On Behalf Of Ryan Wehler
> > > Sent: Tuesday, February 1, 2022 3:04 AM
> > > To: Access Developers discussion and problem solving <
> > accessd at databaseadvisors.com>
> > > Subject: Re: [AccessD] db execute on linked vs local table
> > >
> > > The back end is SQL Server 2008.
> > >
> > > I just find it odd that true fails to a null column (with nulls
> > allowed), but false passes fine
> > >
> > > I will say I discovered this is an issue with option dbFailOnError
> > > for
> > .execute. If I leave that off the profiler shows a prepared
> > statement does as expected.
> > >
> > > I´ve found some other posts out there about dbFailOnError
> > > causing linked
> > table grief:
> > >
> > >
> > >
> > https://stackoverflow.com/questions/55272323/ms-access-with-sql-serv
> > er-back-end-update-fails-without-error
> > >
> > >
> > > https://www.utteraccess.com/topics/2013762/posts/2431750
> > >
> > >
> > >
> > >
> > >> On Jan 31, 2022, at 8:29 PM, Paul Wolstenholme <
> > Paul.W at industrialcontrol.co.nz> wrote:
> > >>
> > >> Ryan,
> > >>
> > >> There is general agreement that FALSE = 0.
> > >> In the absence of any generally accepted definition of TRUE
> > >> (other than
> > "<>
> > >> FALSE" or "NOT FALSE") I refuse to write code that contains
> > >> "TRUE".
> > Maybe
> > >> it keeps me out of unnecessary trouble.
> > >> Sometimes TRUE gets turned into 1 and sometimes it gets turned
> > >> into one
> > of
> > >> the versions of -1 (with any number of bits).  Sometimes what you
> > >> get
> > isn't
> > >> what you need.
> > >>
> > >> You haven't stated whether the back end table is Access or
> > >> something
> > else.
> > >> It can make a difference.
> > >> Some also believe in avoiding the yes/no field type in Access
> > >> tables ( http://allenbrowne.com/NoYesNo.html) so perhaps you are
> > >> using a numeric Access field?
> > >>
> > >> Paul Wolstenholme
> > >>
> > >>>> On Tue, 1 Feb 2022 at 10:14, Ryan W <wrwehler at gmail.com> wrote:
> > >>>
> > >>> More info:
> > >>>
> > >>> If I set the column to not allow nulls, db.execute tries to set
> > >>> it as
> > NULL
> > >>> but then a prepared statement comes in after the fact and does
> > >>> indeed
> > set
> > >>> it to true.
> > >>>
> > >>> https://i.imgur.com/m8TSBN6.png
> > >>>
> > >>>> On Mon, Jan 31, 2022 at 2:40 PM Ryan W <wrwehler at gmail.com>
> > >>>> wrote:
> > >>>>
> > >>>> Upon more testing it seems like FALSE (without quotes) works as
> > >>>> well.
> > It
> > >>>> just seems to be TRUE (no quotes) sets my remote bit column to
> > >>>> NULL.
> > >>>>
> > >>>> Here's a SQL Profiler:
> > >>>>
> > >>>> https://i.imgur.com/rVx3vmX.png
> > >>>>
> > >>>>
> > >>>>
> > >>>>
> > >>>>
> > >>>>> On Mon, Jan 31, 2022 at 1:48 PM Ryan W <wrwehler at gmail.com>
> > >>>>> wrote:
> > >>>>
> > >>>>> Is there a reason db.execute syntax is a bit different on a
> > >>>>> linked
> > >>> (ODBC)
> > >>>>> vs local (DAO?)
> > >>>>>
> > >>>>> db.execute "UPDATE tbl SET col_A = TRUE" works as expected
> > >>>>> locally.
> > >>>>>
> > >>>>> where run on a linked table, it NULLs out the bit field.
> > >>>>>
> > >>>>> but db.execute "UPDATE tbl set col_A = 'TRUE'"  or db.execute
> > >>>>> "UPDATE
> > >>> tbl
> > >>>>> set col_A = 1" works remotely and the numerical representation
> > >>>>> of true/false works both remote AND locally.
> > >>>>>
> > >>>>> However 'TRUE' or 'FALSE' with the single quotes on a local
> > >>>>> table
> > gives
> > >>> a
> > >>>>> data type mismatch (which makes sense, because it's treating
> > >>>>> it as a literal and not converting it to 1/0.
> > >>>>>
> > >>>>>
> > >>>>> I guess my question is, is it the difference in the DAO engine
> > >>>>> vs the ODBC layer that is turning my BIT field NULL (linked)
> > >>>>> versus local?
> > >>>>>
> > >>>>>
> > >>>>>
> > >>>>>
> > >>>>>
> > >>> --
> > >>> AccessD mailing list
> > >>> AccessD at databaseadvisors.com
> > >>> https://databaseadvisors.com/mailman/listinfo/accessd
> > >>> Website: http://www.databaseadvisors.com
> > >>>
> > >> --
> > >> AccessD mailing list
> > >> AccessD at databaseadvisors.com
> > >> https://databaseadvisors.com/mailman/listinfo/accessd
> > >> Website: http://www.databaseadvisors.com
> > > --
> > > AccessD mailing list
> > > AccessD at databaseadvisors.com
> > > https://databaseadvisors.com/mailman/listinfo/accessd
> > > Website: http://www.databaseadvisors.com
> > >
> > > --
> > > AccessD mailing list
> > > AccessD at databaseadvisors.com
> > > https://databaseadvisors.com/mailman/listinfo/accessd
> > > Website: http://www.databaseadvisors.com
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > https://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> >
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > https://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> >
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> https://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com