CODEWITHSUNDEEP
c#winformsms-accessms-access-2007
Sanjeev4evr
Sanjeev4evr

Reputation: 413

Syntax error in CREATE TABLE statement

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();

Upvotes: 0

Views: 1478

Answers (1)

HansUp
HansUp

Reputation: 97101

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

Upvotes: 2

Related Questions