Creating A New Table In Existing Access Database

[PlayfulDragon&Phoenix]

Hi,

I am trying to create a new table in an existing database and having some
issues. When I use the attached code, it gives me an:

Error Executing Database Query.

[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft
Access Driver] Syntax error in field definition.

and says the error is in line three <cfquery name="test"
datasource="DSNjikuhchagi">

I thought that maybe I should try a <cfinsert> but that seems to demand an
already existing tablename.

Any help for a newbie?

Thanks in advance....



<cfquery name="test" datasource="existingAccessDB"> create table student_list
( s_ID autonumber unique primary key, s_FName text, s_LName text, s_Bday
date, s_Address text, s_City text, s_State text, s_Zip text, s_HPhone
text, s_CPhone text, s_Email text, s_Rank text, s_LastTest date,
s_AddlInfo memo ); </cfquery>

[mxstu]

There are at least two problems that I can see:

- I think autonumber should be changed to counter
- You need to define the size of your "text" fields: someColumn text(50)

[Dan Bracuk]

Not sure if this is the case in Access, but in addition to what mxstu said, you
might have to declare your primary key field as not null.

Originally posted by: mxstu
There are at least two problems that I can see:

- I think autonumber should be changed to counter
- You need to define the size of your "text" fields: someColumn text(50)

[paross1]

Maybe this might work better. You need the CONSTRAINT keyword. Also, since
primary keys are unique (and not null), by definition, it would be redundant to
include the UNIQUE constraint.

<cfquery name="test" datasource="existingAccessDB">
create table student_list
(
s_ID counter CONSTRAINT student_list_pk PRIMARY KEY,
s_FName text,
s_LName text,
s_Bday date,
s_Address text,
s_City text,
s_State text,
s_Zip text,
s_HPhone text,
s_CPhone text,
s_Email text,
s_Rank text,
s_LastTest date,
s_AddlInfo memo
);
</cfquery>

Phil

[PlayfulDragon&Phoenix]

Thanks Phil (and everyone else) for your help. That worked great. I hope I'll be able to return the favor some how, some day.

Ben

[mxstu]

>Your text fields will default to 255 characters unless you specify a size

Access lets you define a varchar column without a size? That is just awful. Why am I not suprised ;-)