Syntax error in CREATE TABLE statement

Question

I created a MS Access database at runtime and tried to create a table. Below code is showing the error "Syntax error in CREATE TABLE statement" while creating a table at runtime.

cmmd.CommandText = "CREATE TABLE tblContacts( [SectionID] AUTOINCREMENT PRIMARY KEY,[ScetionName] Text(50), [CatID] Number(Integer), [Rate] Number(Double), [Prefix] Text(5), [Suffix] Text(5), [NextNumber] Number(Integer), [Inactive] Yes, [ModUserID] Number(Integer),[ModDate] Date)";
cmmd.ExecuteNonQuery();

Take a look at this question: stackoverflow.com/questions/1388971/… — BudBrot
– BudBrot, Commented Mar 19, 2014 at 12:14

HansUp · Accepted Answer · 2014-03-25 12:54:17Z

2

The Access db engine will balk at field type declarations such as Number(Integer). Assuming you will execute the statement from an OleDb connection, use this one ...

cmmd.CommandText = "CREATE TABLE tblContacts( [SectionID] COUNTER PRIMARY KEY,[ScetionName] Text(50), [CatID] Long, [Rate] Double, [Prefix] Text(5), [Suffix] Text(5), [NextNumber] Number(Integer), [Inactive] YesNo, [ModUserID] Long,[ModDate] DateTime)";

You can find a table which includes valid Access DDL field type declarations here: Field type reference - names and values for DDL, DAO, and ADOX

answered Mar 25, 2014 at 12:54

HansUp

97.3k11 gold badges80 silver badges139 bronze badges

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

2 Comments

Sanjeev4evr Over a year ago

Thank you @HansUp. It worked well. Please help me in doing relationships. The CatID should be from the Category table? How to relate it CatID field to tblCategory?

HansUp Over a year ago

Use the REFERENCES key word. See here for an example --- look at the example under "Create the Booking table". Post a new question if you run into trouble.

Collectives™ on Stack Overflow

Syntax error in CREATE TABLE statement

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related