asked on

MS Access to SQL Conversion - Bit and Yes/No Datatypes

Hi Experts. I have converted a customer's MS Access 2016 MDB Database to n SQL Server 2012 Database. All went well except the MS Access Yes/No fields that was converted to the SQL Server 2012 Database's Bit datatypes.

I expected that the Yes/No (MS Access) would translate directly to SQL Server 1 (True) and 0 (False). Not so, to be honest, I do not know what happened. In MS Access the values were Yes (-1) and No (0), but after the conversion I see that there are 0 ; 1 ; -1 and Null values in the field.

I read an article that indicated that SQL Server allows for 0 ; 1 and Null values in the Bit datatype definition, and in another article that the Bit data type allows for Yes and No Values or fields that can only contain one of 2 values.

Not surprising then that I am totally confused. I need to rectify the issue on the SQL Database but do not know how. Is there someone that can assist?

Pawan Kumar

Hi,

Bit can save 0 or 1 or NULL in SQL Server.

Sample Example for you

CREATE TABLE Bits
(
  a BIT
)
GO

INSERT INTO Bits VALUES (0),(1),(20)
GO

SELECT * FROM Bits

INSERT INTO Bits VALUES ( 'True' ) , ('False') 

SELECT * FROM Bits

Open in new window

NOTE - If you pass String like 'True' or 'False' string then SQL Server will internally convert it to 0 or 1.

Output

/*------------------------
SELECT * FROM Bits
------------------------*/
a
-----
0
1
1
1
0

(5 row(s) affected)

Open in new window

Note - Also note that if you try to pass any string other than True / False it will not work.. Please check below-

/*------------------------
INSERT INTO Bits VALUES ( 'Pawn' )
------------------------*/
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'Pawn' to data type bit.

Open in new window

Refer more at - https://msdn.microsoft.com/en-IN/library/ms177603.aspx

Hope it helps !

Gustav Brock

In SQL Server, the values are stored as 0, 1, or Null.
The ODBC driver converts these to VBA values: 0, -1, or Null which equals False, True, and Null.

Thus, a normal query will return 0, -1, or Null.
However, a pass-through query will, by definition, return the native values of SQL Server: 0, 1, or Null.

/gustav

ste5an

The correct data type is - as the others already wrote - BIT.

Access/Jet and ODBC will handle this. The only caveat is: You cannot longer use numeric Boolean comparisons in Access using -1/True. Here you need to switch to Not 0/False.

Jim Dettman (EE MVE)

I think part of what you read as well that when used with Access, a bit field in SQL cannot contain a Null. It must always a value. That has more to do with how JET/ACE works rather then anything to do with the data type.

Jim.

ste5an

@Jim: BIT allows NULL.

Pawan Kumar

Yes Bit Allows NULL. Refer the screen shot from the link I provided in first comment.

Hope it helps !

Jim Dettman (EE MVE)

<<@Jim: BIT allows NULL.>>

Yes it allows null, but it can drive the cursor handling in JET/ACE a bit crazy resulting in #Deleted appearing in result sets. Doesn't always happen, but can in certain circumstances.

The "fix" is to always default bit fields to 0 and not allow nulls in the field.

Jim.

Jim Dettman (EE MVE)

I should add that this a known, long standing thing with JET/ACE and ODBC, as are floats, and triggers that change key fields. It's a by-product of the way it's cursor handling was designed.

That's why you always want to add a Time Stamp field to a SQL table if you can. JET/ACE will latch onto that for handling the cursor and you'll avoid problems. But if you don't, then bits, floats, and triggers can have an impact.

Jim.

PatHartman

If you have a lot of code/queries that specifically checks for -1 rather than True, your best option is to convert to SmallInt rather than Bit for the Y/N fields.

In either case, deselect the AllowNulls checkbox and provide a default of True (1 or -1 depending on how you decided the previous suggestion) or False (0) depending on what makes sense.

Armen Stein - Microsoft Access MVP since 2006

No points please. I agree with Pat that SmallInt is the way to go. It will hold -1, +1, 0 and null as necessary. Best practice is to check for 0 or Not 0, rather than checking for -1 or True, since "true" values may be +1 or -1 depending on where they were set.

ASKER CERTIFIED SOLUTION

PatHartman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Anton Greffrath

ASKER

Thanks Pat. . . . . .