[AccessD] db execute on linked vs local table

[Ryan W]

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