DaniWeb IT Discussion Community - Help me with: Access CREATE TABLE() SQL

DaniWeb IT Discussion Community (http://www.daniweb.com/forums/)

- MS Access and FileMaker Pro (http://www.daniweb.com/forums/forum128.html)

- - Help me with: Access CREATE TABLE() SQL (http://www.daniweb.com/forums/thread112708.html)

Help me with: Access CREATE TABLE() SQL

I have a program that creates a SQL statement based upon a treeview list that the user makes. The SQL statement looks like this:

CREATE TABLE Test ( Test ID PRIMARY KEY, Check1 CHAR(255), Check2 MEMO, Check3 INTEGER, Check4 DOUBLE, Check5 DATETIME, Check6 BOOLEAN) VALUES (1, 'DefSML', 'DefLRG', 0, 0.0, '3/7/2008 12:00:00 AM', 'True');

but I continually get a Syntax error - when I check it with a validator it is on the DOUBLE variable. The VB error that i get is System.Data.OleDB.OleDBException: Syntax error in field definition. Which is why I think it is my SQL that is messed up - any help is appreciated. Thanks

Re: Help me with: Access CREATE TABLE() SQL

Quote:

Originally Posted by Mr.Wobbles (Post 554802)

I have a program that creates a SQL statement based upon a treeview list that the user makes. The SQL statement looks like this:

CREATE TABLE Test ( Test ID PRIMARY KEY, Check1 CHAR(255), Check2 MEMO, Check3 INTEGER, Check4 DOUBLE, Check5 DATETIME, Check6 BOOLEAN) VALUES (1, 'DefSML', 'DefLRG', 0, 0.0, '3/7/2008 12:00:00 AM', 'True');

I dont think you can use the VALUES statement at the same time as CREATE in ACCESS.

Field Name has space (causes problems later)
The int specifier is missing for the Primary Key
Then BOOLEAN specifier not valid - use YESNO

But corrected as...
CREATE TABLE Test (TestID int PRIMARY KEY, Check1 CHAR(255), Check2 MEMO, Check3 INTEGER, Check4 DOUBLE, Check5 DATETIME, Check6 YESNO);

should work, then use an INSERT statement for the first record.