ODBC Error Code = 37000 (Syntax error or access violation)?

[XMLidiot]

I am a newbie to CF and am just trying out my skills. I wanted to do a simple database query by an SQL statement.

My code looks like this:

<cfquery name=addtbl datasource=allaire_beginner>
CREATE TABLE members(fname,lname,age integer)
</cfquery>

Table created successfully!

--------------
Everytime I access my page I get this error:

ODBC Error Code = 37000 (Syntax error or access violation)


[Microsoft][ODBC Microsoft Access Driver] Syntax error in field definition.



The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (1:1) to (1:49).

-----------
Could someone please explain to me what this means and help me out with my code. All responses will be appreciated greatly.

 

[tlhawkins]

I have never actually done this myself but every time I've seen it done all fields had there own type like this:

<cfquery name=addtbl datasource=allaire_beginner>
CREATE TABLE members
(
fname text(20),
lname text(20),
age integer
)
</cfquery>

Might give that a try.
Hope it works.

 

[ram123]

It is not the coldfusion problem. i tried to run the same sql statement on the ms access data base, and i got the same error. you may have to specify the datatype for all fields and specify number as datatype instead of integer.

 

[GunJack]

Hey all,

I have to use that technique on a remote server for db changes and here's some code that works for me on MS access.

create table members
(
fName text(50) null,
lName text(50) null,
age short
)

The &quot;null&quot; is supposed to tell it to allow nulls on the field but it never seems to work for me.

Good luck,
GJ

 

[tleish]

The CREATE TABLE statement requires a table name and a list of columns along with their corresponding data types. There are several optional elements that you can also include in the CREATE statement, but for the moment, let's just deal with the required ones. The basic syntax is as follows:

CREATE TABLE table ( column datatype ) ;

tlhawkins code should work.

- tleish

 

[XMLidiot]

Thanks everyone from your responses. It works now!