How to add a 'Boolean' column to ms-access via SQL in vb.net

Question

I am trying to add a Boolean column to a table in ms-access using SQL. I am using JET, here are the the SQL queries I have tried.

Query = "ALTER TABLE tabDatafiveMinutely ADD CON0001 BOOLEAN DEFAULT FALSE"
Query = "ALTER TABLE tabDatafiveMinutely ADD CON0001 BOOLEAN"

The error I am getting is 'Syntax error in field definition'

Thanks for your help

EDIT:

I would now like to make the default null rather than false. I have tried default null and this still gives me false, can anyone help with this?

RESULT:

An ms-access database can only take true and false and not null. Therefore I have decided to use and integer instead.

"I would now like to make the default null rather than false." That is not possible. An Access YesNo field will accept only True or False, never Null. So you can't make it default to a value it can't hold. — HansUp
– HansUp, Commented Nov 28, 2012 at 15:44

Olivier Jacot-Descombes · Accepted Answer · 2016-07-06 15:14:20Z

12

The equivalent SQL type of a Yes/No column is BIT

ALTER TABLE tabDatafiveMinutely
    ADD COLUMN CON0001 BIT   DEFAULT 0   NOT NULL

Microsoft's documentation says

Note
The DEFAULT statement can be executed only through the Jet OLE DB provider and ADO. It will return an error message if used through the Access SQL View user interface.

As @Pere points out, Jet Engine (Access' query engine) does not apply the DEFAULT value to existing rows. You must run an UPDATE statement after altering the table.

UPDATE tabDatafiveMinutely SET CON0001 = 0 WHERE CON0001 IS NULL

edited Jul 6, 2016 at 15:14

answered Nov 28, 2012 at 13:30

Olivier Jacot-Descombes

114k14 gold badges149 silver badges202 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Pere Over a year ago

Thanks for pointing out. After spending hours on it, finally I realized that the only programatical way in PHP to set a DEFAULT for a column in an ALTER TABLE is via OLEDB using COM objects.

Pere Over a year ago

Oh, by the way. Be very careful with statements like the one above if executed via a SQL window in Access (haven't tried through ADO but I suspect it will act the same way): I just discovered that, for existing rows, it won't apply the DEFAULT supplied value if you set it to true. New ones will, though. Thanks again, MS!

Will A · Accepted Answer · 2012-11-28 13:11:55Z

3

You should use the BIT datatype rather than BOOLEAN.

Access data types.

answered Nov 28, 2012 at 13:11

Will A

25k5 gold badges54 silver badges61 bronze badges

Comments

Mike Sherrill 'Cat Recall' · Accepted Answer · 2012-11-28 13:20:21Z

0

I'm not sure where you're reading the syntax from, but you need a better source.

ALTER TABLE tabDatafiveMinutely
ADD COLUMN CON0001 <datatype>

For a Boolean type, I think you'll need to pick from BIT, INTEGER, or CHAR(1). It's application-dependent. For example, legacy systems often use 't' and 'f' in a CHAR(1) column.

answered Nov 28, 2012 at 13:20

Mike Sherrill 'Cat Recall'

96.6k20 gold badges134 silver badges195 bronze badges

1 Comment

Pezzzz Over a year ago

The query works with BIT or other valid data types, it doesn't require COLUMN.

Collectives™ on Stack Overflow

How to add a 'Boolean' column to ms-access via SQL in vb.net

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related