0

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();
Sanjeev4evr
  • 403
  • 2
  • 11
  • 23
  • 1
    [By learning the basics](http://msdn.microsoft.com/en-us/library/ms971485.aspx) – Sadique Mar 19 '14 at 12:10
  • Take a look at this question: http://stackoverflow.com/questions/1388971/how-i-create-access-database-at-runtime-in-c – BudBrot Mar 19 '14 at 12:14

1 Answers1

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

HansUp
  • 95,961
  • 11
  • 77
  • 135
  • 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? – Sanjeev4evr Mar 25 '14 at 13:13
  • 1
    Use the `REFERENCES` key word. See [here](http://allenbrowne.com/func-DDL.html#CreateTableDDL) for an example --- look at the example under *"Create the Booking table"*. Post a new question if you run into trouble. – HansUp Mar 25 '14 at 13:28