[AccessD] db execute on linked vs local table

[Paul Wolstenholme]

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
>