Create table query with a field named size?? 1

[dflanagan]

Hey!
I am having a heck of a time dynamically creating a table (via the create table command) against an Access 2000 database where I want the field to be named "size" (without the quotes). My asp page returns:

Microsoft JET Database Engine (0x80040E14)
Syntax error in field definition.

Here is a shot of the line of code that does the query:

crstate="CREATE TABLE "
crstate2=" (ID counter, "&myoption&" text, addprice currency, addship currency, primary key (ID))"
crstate= crstate&identRS("clinum&quot&myoption
crstate= crstate & crstate2
set connect = Server.CreateObject("ADODB.Connection&quot
connect.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("mydb.mdb&quot

connect.Execute(crstate)


Basically, this code creates a table with the name that they decide, prefixed with their client number. In this case, the table is going to be named "1size". The problem is in the create table query. The second field will be called "size", but I think that "size" is some sort of keyword or something. It seems to work for any other word!!

I realize that I could just append something to the field name to get around this, but that would also mean plowing through my other pages looking for calls to that field, and correcting them....

Hope someone can help!!

Dave

 

[Falcon99]

Can you enclose the field name in brackets?
Access seems to like brackets.

. . . , ["&myoption&"] text, . . .

HTH

 

[dflanagan]

YES!!!!!!

YOU GOT IT!!

THANKS ALOT!

You get a star for the day!

Dave

 

[Falcon99]

Thanks, Dave!

 

[Tarwn]

The reason this was causing a problem is because the word "size" (without the quotes ) is a reserved word in access, so it attempted to resolve this as a function (i think a function at least, have to check on that) and found it to be in error because either it was expecting arguments or it wasn't expecting a function in that location.
it's generally a bad idea to use reserved words for field names because it causes a lot of problems if someone else has to come along later to maintain the site. you may realize that the name has to be surrounded by brackets, but they may not and will be confused as to why 1) none of their SQL works when they write it, and b) why you only have brackets around that one field. Later they will put brackets around it and their SQL will work fine, but they will never know why.

-Tarwn --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
No more vacation for me

FAQ FAQ20-2863
= new Forums.Posting.General.GettingAnswers()

 

[dflanagan]

This is true, but in this case, the user of the site is dictating the name of the field (by their input). I do not want to tell them that they cannot use certain words.

 

[Tarwn]

If this is the case then you should probably go ahead and surround all the fields with brackets in case they use another reserved word (and put a comment in your code noting that) and make double double extra sure they aren't duplicating words or using illegal characters in names.
-Tarwn --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
No more vacation for me

FAQ FAQ20-2863
= new Forums.Posting.General.GettingAnswers()

 

[dflanagan]

right, that is what I am doing now..... Darn users! why do they have to have minds of their own????



Thanks for the help!

 

[Tarwn]

Heh
I know, right?
The single biggest problem software developers face is users.


-Tarwn --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
No more vacation for me

FAQ FAQ20-2863
= new Forums.Posting.General.GettingAnswers()