Help me with: Access CREATE TABLE() SQL

•

What is DaniWeb IT Discussion Community?

You're currently browsing the MS Access and FileMaker Pro section within the Web Development category of DaniWeb, a massive community of 401,971 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 2,842 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.

Join our community!

Thread Tools Display Modes

Views: 4105 | Replies: 1

•

Join Date: Jun 2007

Posts: 49

Reputation: Mr.Wobbles is an unknown quantity at this point

Rep Power: 2

Solved Threads: 2

Mr.Wobbles Mr.Wobbles is offline

Offline

Light Poster

Help me with: Access CREATE TABLE() SQL

Mar 7th, 2008

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

Help with Code Tags

(Toggle Plain Text)

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

Last edited by peter_budo : Mar 10th, 2008 at 8:15 pm. Reason: Keep It Organized - please use [code] tags

Mr.Wobbles~

•

Join Date: Oct 2005

Location: Charlotte,NC

Posts: 92

Reputation: Kegtapper is an unknown quantity at this point

Rep Power: 3

Solved Threads: 2

Kegtapper

Offline

Junior Poster in Training

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

Mar 30th, 2008

•

Originally Posted by Mr.Wobbles

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

(Toggle Plain Text)
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');
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

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.