Creating an Access table with sql query and default bit values

Question

This is my code for creating the table (in the sql view of designing a query in access)

 CREATE table Track (WebCompareString CHAR(255), Master INT, Child INT, 
 Merged BIT NOT NULL DEFAULT 0, Children_Updated BIT NOT NULL DEFAULT 0,
 Deleted BIT NOT NULL DEFAULT 0, TrackId INT PRIMARY KEY;

When trying to run! this I get the following error: Syntax error in CREATE TABLE statement.

See Edit 2, it works from VBA with `CurrentProject.Connection.Execute`. — Andre, Nov 11 '15 at 14:59

score 2 · Answer 1 · edited May 23 '17 at 12:03

The missing closing parenthesis is a minor problem.

The main problem is the limited support for the DEFAULT clause in Access SQL.

You need to either run the SQL command via an ADO connection:
SQL SET DEFAULT not working in MS Access

or set the Access option unter Query Design to ANSI 92 compatible:
SQL to add column with default value

Edit

Yes, I tested the second option (only the second one), in Access 2010 in a .accdb.
I set the option Object Designer - Query design - SQL Server Compatible Syntax (ANSI 92) - Use in this database. (Exact wording is guessed since I have a German Access).

Access showed a warning, then did an automatic Compact&Repair. After that I could execute the SQL from the question in a new query, only adding the closing parenthesis to the SQL string.

The Yes/No fields were created with default value = 0.

Edit 2

With the SQL Server Compatible Syntax (ANSI 92) option back to unchecked, I tested the first suggestion too, by simply following HansUp's code.
The following sub created the table with the default values.

Public Sub CreateTrackTable()

    Dim S As String

    S = "CREATE table Track (WebCompareString CHAR(255), Master INT, Child INT, " & _
        "Merged BIT NOT NULL DEFAULT 0, Children_Updated BIT NOT NULL DEFAULT 0, " & _
        "Deleted BIT NOT NULL DEFAULT 0, TrackId INT PRIMARY KEY);"

    CurrentProject.Connection.Execute S

End Sub

So you can't run this query from the Query designer (unless you set the syntax option), but you can do it from VBA.

score 1 · Answer 2 · answered Nov 09 '15 at 16:51

1

You are missing a closing parenthesis at the end. Add a ')' right before ';'. With that added, this statement completed successfully (with MySQL).

answered Nov 09 '15 at 16:51

DBug

2,502
1
12
25

score 1 · Answer 3 · answered Nov 09 '15 at 16:53

1

You've forgotten a ')' at the end.

CREATE table Track (WebCompareString CHAR(255), Master INT, Child INT, 
Merged BIT NOT NULL DEFAULT 0, Children_Updated BIT NOT NULL DEFAULT 0,
Deleted BIT NOT NULL DEFAULT 0, TrackId INT PRIMARY KEY);

answered Nov 09 '15 at 16:53

bork

1,556
4
21
42

score 0 · Answer 4 · answered Nov 09 '15 at 16:54

0

If you pasted your code correct, than it seems you might have missed the closing bracket at the end.

answered Nov 09 '15 at 16:54

mv1

558
4
8

score 0 · Answer 5 · edited May 23 '17 at 12:23

you are missing the ending parenthesis. You should also have a look over SQL naming conventions and best practices (Database, Table and Column Naming Conventions?). Best of luck!

 CREATE table Track (
   TrackId INT PRIMARY KEY,
   WebCompareString CHAR(255),
   Master INT,
   Child INT, 
   Merged BIT NOT NULL DEFAULT 0,
   Children_Updated BIT NOT NULL DEFAULT 0,
   Deleted BIT NOT NULL DEFAULT 0) ;

Creating an Access table with sql query and default bit values

5 Answers5

Linked