[AccessD] db execute on linked vs local table

Tue Feb 1 09:41:34 CST 2022

Yes - 

BUT 
I was learned  to make my coding as transportable as possible:

Even to the point where rewriting from code in a language from 1 manufacturers computer system was less likely to need actual consideration of the logic by the converting coder ( frequently Me!).

Shift code from a Honeywell system with 48 bit 'words' to one using 6 bit characters with almost unlimited number of digits in a number, to a PDP to a DEC, and then an ICL, then on from an IBM 
to a variety of mini's using C and Oracle.

That sort of experience learns one to be careful about the actual tests used in code.

JimB

-----Original Message-----
From: AccessD <accessd-bounces+jamesbutton=blueyonder.co.uk at databaseadvisors.com> On Behalf Of Ryan W
Sent: Tuesday, February 1, 2022 3:08 PM
To: Access Developers discussion and problem solving <accessd at databaseadvisors.com>
Subject: Re: [AccessD] db execute on linked vs local table

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-server-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