Access 97 and MS Sql Server 2000

  • Thread starter Thread starter Greg Druian
  • Start date Start date
G

Greg Druian

When I ran my Access 97 app against MS Sql Server 7.0, I could successfully
execute a query based on an attached table in which I specified the value -1
in the Where clause to indicate a boolean TRUE. When I run the query
against the same database migrated to MS Sql Server 2000, I get no rows in
the result. I can get the correct results by substituting 1 or Not 0 for -1
in my query, but I would like to know why this sudden change in behavior.
My app has numerous queries based on boolean values , and I'd like not to
have to change each query, so if anyone can help me I'd be grateful.
(Incidentally preliminary testiing appears to show the same behavior when I
convert my Access 97 app to Access 2000.)
 
When I run the query
against the same database migrated to MS Sql Server 2000, I get no rows in
the result. I can get the correct results by substituting 1 or Not 0 for -1
in my query, but I would like to know why this sudden change in behavior.
Click to expand...

Because JET (the Access database engine) uses -1 in a tiny integer for
True, whereas SQL Server uses 1 in a Bit field.

The only safe option is to treat 0 as FALSE, nonzero as TRUE.
 
I agree with you that the best option is to treat 0 as False, and <> 0 as
True. But SQL Server has always used 1 in a bit field to represent a
logical True(if I'm not mistaken), and I think it must be a change in the
ODBC driver that is responsible for the new behavior. I wonder whether
there are other subtle differences between the 8.0 driver and earlier ones.
 
I would agree that it *sounds* like a driver issue. I
would not expect what you've encountered.

To truly understand this -1=true, 1=true stuff, we first
must understand the signed/unsigned integer concept.

True *is* Not False. False is 0. However, take a look at
its underlying representation.

False, when the datatype is an integer (a byte long in my
example), looks like this:
0000 0000

True (rather, Not False) looks like this:
1111 1111 (not 0)

In signed-integer format, 1111 1111 = -1. Sign is
indicated in the high bit (the first 1).

This is why true = -1 ... we're dealing with signed
integers. In unsigned conventions, it's 1 pretty much
only if you're dealing with a bit, otherwise you really do
use <>0 as the test condition.

So I would suspect that something is going awry in the
ODBC driver as it converts SQL Server's "unsigned" bit
field into a signed integer and not properly making the
conversion (i.e., it's going from 1 to 0000 0001 [i.e., 1]
instead of 1111 1111 [i.e., -1]). SQL Server's bit field
was designed as a true/false flag, and should be
implemented as such by ODBC drivers.

Anyway, enough rambling. I still don't like what you see;
it's WRONG for the driver to work the way you described.

David Atkins, MCP
 
If I am not mistaken, the majority of RDBMSs and programming languages use 1
for True. JET and VB (hence Access) are the odd ones and I don't expect the
ODBC Driver for MS-SQL2K would convert the 1 in the Bit Field to -1 just to
fit in with JET and VB.

However, I experienced a display inconsistency with this conversion: a
Access / JET Table X was upsized to MS-SQL2K and the Boolean Field was
converted correctly to Bit Field in MS-SQL2K and True value showing as 1 (in
MS-SQK2K). Linking the upsized Table back to an Access Front-End, Access
recognises that the Bit Field in MS-SQL2K is equivalent to a JET Boolean
Field (showing in the DesignView of the Table in Access as Boolean) and the
True value showing as -1 in the DatasheetView of the Table in Access FE (no
format specified). When I used the Shortcut Menu to filter for -1, I got no
Records selected even though there are several showing -1 value. When I
filtered for 1, these Records are selected correctly but the display still
show -1 for these. So I filtered for 1 but ended up with Records that
have -1 displayed.

I am sure the problem was any non-zero value is interpreted as True and
since there is no format specified, Access displays -1 for the non-zero
value!

Sorry, no ideas about MS-SQL7.
 
Back
Top