CODEWITHSUNDEEP
sqlvb.netms-access
Pezzzz
Pezzzz

Reputation: 748

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

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.

Upvotes: 6

Views: 31556

Answers (3)

Olivier Jacot-Descombes
Olivier Jacot-Descombes

Reputation: 112482

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

Upvotes: 12

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95632

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.

Upvotes: 0

Will A
Will A

Reputation: 24998

You should use the BIT datatype rather than BOOLEAN.

Access data types.

Upvotes: 3

Related Questions