Using "Size" as a column name generates an error.

[Guest]

Hi, I have these simple statements, cmd has been defined already:

cmd.CommandText = "CREATE TABLE Server_Size ( " & _
"Server_Size_ID AutoIncrement Primary Key, " & _
"Size Text(50) NOT NULL" & _
")"
cmd.Execute

When I run the VBA statements, I got "Syntax error in field definition". I know the cause of this problem is the column name "Size" (i.e. changing "Size" to "Size_1" will solve the problem). However, if I create run the SQL statement as an Access 2000 query, the table will be created. Is this an Access VBA bug? Does anyone know how to resolve this problem? Thanks.

 

[Douglas J. Steele]

How to resolve it is rename the field. Size is a reserved word, and
shouldn't be used (even if running it as a query works)

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

Hi, I have these simple statements, cmd has been defined already:

cmd.CommandText = "CREATE TABLE Server_Size ( " & _
"Server_Size_ID AutoIncrement Primary Key, " & _
"Size Text(50) NOT NULL" & _
")"
cmd.Execute

When I run the VBA statements, I got "Syntax error in field definition".

I know the cause of this problem is the column name "Size" (i.e. changing
"Size" to "Size_1" will solve the problem). However, if I create run the
SQL statement as an Access 2000 query, the table will be created. Is this
an Access VBA bug? Does anyone know how to resolve this problem? Thanks.

 

[david epsom dot com dot au]

Is this an Access VBA bug

No, that DDL is not Access DDL, it is ODBC or ANSI DDL, it allows
you to create a table with a field name that might be legal in some other
situation. You can also create tables with field names that are
legal in Access/US English, but are not legal in other systems -
including names that are not legal in Access/French or Access/German.

(david)

Hi, I have these simple statements, cmd has been defined already:

cmd.CommandText = "CREATE TABLE Server_Size ( " & _
"Server_Size_ID AutoIncrement Primary Key, " & _
"Size Text(50) NOT NULL" & _
")"
cmd.Execute

When I run the VBA statements, I got "Syntax error in field definition".

I know the cause of this problem is the column name "Size" (i.e. changing
"Size" to "Size_1" will solve the problem). However, if I create run the
SQL statement as an Access 2000 query, the table will be created. Is this
an Access VBA bug? Does anyone know how to resolve this problem? Thanks.

 

[Martin Seelhofer]

Sometimes, enclosing such a name in brackets helps: [Size] instead of just
Size...


Cheers,

Martin

 

[Tim Ferguson]

CREATE TABLE Server_Size (
Server_Size_ID AutoIncrement Primary Key,
Size Text(50) NOT NULL
)

1) This works fine for me, using Access 2000 -- but it is sloppy sql, and I
think that later versions are a bit tougher.

2) Doug's comments about using a keyword Size for a field name are correct
-- even if you can get Jet to accept it by using [Size], you'll trip over
it sooner or later. Try something meaningful like GigaBytes instead -- your
successors will love you for it.

3) Another problem in waiting -- a required field without a default value.
This will fail every time you try an INSERT command without specifying the
Gigabytes value.

4) OSI96 (?) syntax for the second line would be

Server_Size_ID INTEGER IDENTITY(1,1) NOT NULL
CONSTRAINT pk PRIMARY KEY,


HTH


Tim F

 

[TC]

(snip)

3) Another problem in waiting -- a required field without a default value.
This will fail every time you try an INSERT command without specifying the
Gigabytes value.

Tim, I disagree that that is a problem. Some developers deliberately >do
not< provide defaults for required fields. The idea is, that you >want< the
error to occur, to identify the hole in your code. If you always provide a
default, there is no explicit warning of those holes. A developer could go
either way, as a matter of personal practice, IMO.

TC

 

[Tim Ferguson]

Some developers deliberately >do
not< provide defaults for required fields. The idea is, that you
always provide a default, there is no explicit warning of those holes.
A developer could go either way, as a matter of personal practice,

Less personal practise than the semantics of the individual case, I would
say; but I don't disagree with anything else you've said.

Bear in mind though that the OP was calling db.Execute without the
dbFailOnError parameter, so (he) obviously wasn't looking for anything to
fail.

All the best


Tim F

 

[TC]

Less personal practise than the semantics of the individual case, I would
say; but I don't disagree with anything else you've said.

Bear in mind though that the OP was calling db.Execute without the
dbFailOnError parameter, so (he) obviously wasn't looking for anything to
fail.

Not necessarily! He might have wanted that if anything >did< fail, the
update should proceed with the remaining records.

I use this in a case where I'm updating a field value to a different value
(in several records), but this might cause duplicate error(s). With
dbfailonerror, the update stops at the first duplicate - that's not what I
want. Without dbfailonerror, the update does all of the records that it

can< do. Then, any original records remaining, are non-updatable

will-be-duplicate's.

Yes? No? Maybe?

TC

 

[Tim Ferguson]

Yes? No? Maybe?

Maybe.... <grin>

All the best


Tim F

 

[david epsom dot com dot au]

Not necessarily! He might have wanted that if anything >did<

update should proceed with the remaining records.

either way, practically guaranteed to lead to database bloat...
best to test first and exclude any actions that are expected
to fail.


(david)

 

[TC]

David, I'm not sure that I understand what you're getting at here. Say that
an update of 10 records actually updates 8, but fails to update 2 (due to
duplicate keys or whatever). Are you dsaying that the two failed updates
will, in and of themseves, cause database bloat?

TC
(off for the day)

 

[david epsom dot com dot au]

an update of 10 records actually updates 8, but fails to update 2

will, in and of themseves, cause database bloat?

Yes.

In and of themselves, only 'bloat' of 2 records, which hardly
matters. 10,000 records daily in a 20,000 record database would
be more of an issue.

(david)

 

[TC]

Ok, that's news to me

TC

Yes.

In and of themselves, only 'bloat' of 2 records, which hardly
matters. 10,000 records daily in a 20,000 record database would
be more of an issue.

(david)