ADO Database Table Boolean Column

Question

Hello everyone.

I am having a bit of trouble with ADO. I have deployed a database application, which uses Access. With the release of different versions database tables have different fields, some added others deleted etc. What I can't get to work is how to add a BOOLEAN field in the database.

For upgrade purposes I use the standart sql query component with a sql that looks like this :

ALTER TABLE XXX ADD COLUMN YY BOOLEAN

while this works for other data types, such as VARCHAR, INTEGER, DOUBLE etc, it does not with BOOLEAN. I suspect it's Access's fault with it's YES/NO thing for boolean, but who knows.

Also how can I add fields to a table using TADOTable?

Thanks in advance.

Answer 1

Try BIT, not BOOLEAN

Answer 2

Not sure about Access, but SQL Server uses a bit type to handle boolean values.

Answer 3

You can't do many operations on BIT (or your own custom) type, much better is TINYINT(1) and use 0 / 1.

Answer 4

In Microsoft Access SQL, The BIT column directly corresponds to the YES/NO field. I have experienced an odd behavior with it if you attempt to convert this later to SQL Server, and my advise is to then do the following:

When EVER you do a check against this field, remember the syntax should be (FIELD <> 0) for checking TRUE, and (FIELD = 0) for checking false. SQL Server doesn't understand the concept of TRUE/FALSE, and in access the value returns -1 and 0, while in SQL Server the values are 1 and 0.

In access it will only render a check box if you also set the field to not null. If nulls are allowed, then it will display the 0 or -1 or empty.