There is a big problem with ODBC JET Linked tables when it comes to boolean
fields in Access that get turned into BIT fields in SQL Server.
Basically, JET thinks of boolean TRUE as (-1) where SQL thinks of it as (1).
The ODBC driver switches the values for you in Access, so that when you look at
the table it shows (-1)...but if you look at the table in SQL Server it says
(1).
The bottom line is that the ODBC driver does NOT understand what TRUE or FALSE
means, so the short answer is YES, you have to change all of those refrences to
(-1) or (0) respectively.
The standard, quick answer to this problem is to use a find/replace tool (in
NOTEPAD) to change all
= TRUE
to
= -1
and = FALSE
to
= 0
WARNING: This is only true for JET-based ODBC queries. If you create a view on
SQL Server, you'll have to use = 1 for TRUE!!!!
For that reason, I normally use =0 for FALSE, and <>0 for TRUE!!! That way, it
always works.
Another option is to change the BIT datatype on SQL Server to INT
(integer)...that way a (-1) WILL BE STORED by the ODBC driver, and it'll be
consistent.
Hope this helps! |