[AccessD] db execute on linked vs local table

Tue Feb 1 03:32:57 CST 2022

Yes I did see that Allen Browne link you posted suggested int columns if you want to deal with nulls. 

I tried the stock “SQL Driver” and version 17. Same result, so it doesn’t appear to be the driver (which states 2008 is still supported by it, if I remember correctly). 

I discovered this when trying to convert from docmd.runsql to .execute …. 

> On Jan 31, 2022, at 9:40 PM, Paul Wolstenholme <Paul.W at industrialcontrol.co.nz> wrote:
> 
> Ryan,
> 
> If I recall correctly, the advice for linking Access to SQL Server is:
> a. If you use a SQL Server BIT field, set it NOT NULL and apply a default
> constraint to the field in SQL Server.
> b. If you want to support null, use a numeric field instead.
> That is what I use.  Can't remember the source of the wisdom.
> 
> Experience might differ with different SQL Server drivers - but perhaps not
> in this case.
> Never use a SQL Server driver that had been superseded when your SQL Server
> version came out (2008 in your case).
> 
> Regards,
> Paul Wolstenholme
> -- 
> Industrial Control Engineering Ltd.
> PO Box 35-022,  Naenae
> Lower Hutt 5041, New Zealand
> Ph: +64 21 150 7222
> www.IndustrialControl.co.nz <http://www.industrialcontrol.co.nz/>
> 
> e-mail: Paul.W at IndustrialControl.co.nz
> 
> 
>> On Tue, 1 Feb 2022 at 16:04, Ryan Wehler <wrwehler at gmail.com> wrote:
>> 
>> 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