[AccessD] db execute on linked vs local table

[Ryan Wehler]

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