[AccessD] db execute on linked vs local table

[James Button]

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