How to add a 'Boolean' column to ms-access via SQL in vb.net
Solution 1
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
Solution 2
You should use the BIT datatype rather than BOOLEAN.
Pezzzz
Updated on July 07, 2020Comments
-
Pezzzz almost 4 years
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 thanfalse
. I have trieddefault null
and this still gives mefalse
, can anyone help with this?RESULT:
An ms-access database can only take
true
andfalse
and not null. Therefore I have decided to use andinteger
instead.