[AccessD] db execute on linked vs local table

Ryan W wrwehler at gmail.com
Tue Feb 1 17:50:05 CST 2022 

Talk about timing. 

Sent from my iPhone

> On Feb 1, 2022, at 5:49 PM, Stuart McLachlan <stuart at lexacorp.com.pg> wrote:
> 
> 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
> 
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> https://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com

More information about the AccessD mailing list