How create Autonumber field in code?

[paul.schrum]

With help from this group, I recently got this code to work:

sqlStr = "CREATE TABLE tbl_urgencies (" & _
"urgencyID INTEGER NOT NULL, " & _
"urgencyDescription VARCHAR(31), " & _
"urgencyPriority INTEGER, " & _
"PRIMARY KEY (urgencyID))"

dbNew.Execute sqlStr


I then opened the database which I had just created from code. I
opened the table in design view and noticed that field urgencyID is
type "Number", not Autonumber. Isn't it preferable for this to be
Autonumber? What would be the syntax to get it to Autonumber instead
of Number?

Thanks.

- Paul Schrum

 

[Marshall Barton]

With help from this group, I recently got this code to work:

sqlStr = "CREATE TABLE tbl_urgencies (" & _
"urgencyID INTEGER NOT NULL, " & _
"urgencyDescription VARCHAR(31), " & _
"urgencyPriority INTEGER, " & _
"PRIMARY KEY (urgencyID))"

dbNew.Execute sqlStr


I then opened the database which I had just created from code. I
opened the table in design view and noticed that field urgencyID is
type "Number", not Autonumber. Isn't it preferable for this to be
Autonumber? What would be the syntax to get it to Autonumber instead
of Number?

For a Jet database, use COUNTER for the urgencyID field
(instead of INTEGER).

 

['69 Camaro]

Hi, Paul.

Isn't it preferable for this to be
Autonumber?

You haven't given us enough information to make that determination. What
reasons do you have for making the column an AutoNumber?

What would be the syntax to get it to Autonumber instead
of Number?

Here are two ways:

CREATE TABLE tbl_urgencies
(urgencyID COUNTER (1, 1),
urgencyDescription VARCHAR(31),
urgencyPriority INTEGER,
CONSTRAINT PrimaryKey PRIMARY KEY (urgencyID));

Or:

CREATE TABLE tbl_urgencies
(urgencyID AutoIncrement,
urgencyDescription TEXT (31),
urgencyPriority LONG,
CONSTRAINT PrimaryKey Primary Key (urgencyID));

You _always_ want to name a constraint, because Jet will provide a default,
nearly unintelligible name for you otherwise. If you name it PrimaryKey, it
will have the identical name as the primary keys of the tables where the
primary key is created in Design View. Also note that whether you use
VARCHAR or TEXT in the SQL code, it will create a Text field. And that
whether you use INTEGER or LONG in the SQL code, it will create a LONG
INTEGER (a four byte LONG data type) field.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[paul.schrum]

Hi, Paul.


You haven't given us enough information to make that determination. What
reasons do you have for making the column an AutoNumber?

This table has a small number of values that will not change after
initial setup. (Values will be "Very High", "High", "Standard", and
"Low".) I just always make my primary keys AutoNumbers. I don't know
why I started except I probably read it in a book somewhere when I was
self-teaching Access or Relational Database design. I also plan to
use it in my main tables, including the main one, tbl_tasks, which
will have a Foreign Key to itself.

Also, regarding the syntax for making it Autonumber, thanks to Gunny
and to Marshall Barton for the answer and the advice on naming
constraints.

- Paul