How to create an AutoNumber field with a SQL statement?

[minjie]

I need to upgrade a MS Access database (Version 2002) with a script
only, i.e., via SQL statement, not via Access GUI, and I'm having
trouble defining an AutoNumber field. I got an exception error when
running the follwoing (in a C++ program using ADO):

......
cmdStr = "CREATE TABLE mytab "
"([Id] AutoNumber, "
"[Desc] TEXT(50), "
"CONSTRAINT [PK_MYTAB] PRIMARY KEY ([Id])); ";
pConn->Execute(cmdStr, NULL, adExecuteNoRecords);

When I changed the "Id" column data type to "integer", the program
updated the Access database successfully. However, the "id" field now
becomes a regular integer field. How do I make it an AutoNumber field
with a SQL statement?

Thanks.

[Bri]

mi****@excite.com wrote:

I need to upgrade a MS Access database (Version 2002) with a script
only, i.e., via SQL statement, not via Access GUI, and I'm having
trouble defining an AutoNumber field. I got an exception error when
running the follwoing (in a C++ program using ADO):

......
cmdStr = "CREATE TABLE mytab "
"([Id] AutoNumber, "
"[Desc] TEXT(50), "
"CONSTRAINT [PK_MYTAB] PRIMARY KEY ([Id])); ";
pConn->Execute(cmdStr, NULL, adExecuteNoRecords);

When I changed the "Id" column data type to "integer", the program
updated the Access database successfully. However, the "id" field now
becomes a regular integer field. How do I make it an AutoNumber field
with a SQL statement?

Thanks.

I thought this was going to be an easy one. SQL Server and MySQL both
have a syntax for this but Access doesn't seem to. At least I can't find
it either. In these you define the Type as Long and then an extra
parameter makes it an Identity. The Access Help doesn't mention this
parameter, so it may not exist.

I did find that you can do it with the CreateTable method of the
DAO.Database object with an Attribute parameter. I don't know if that
helps or not. There appears to be a lot more options to CreateTable than
the SQL Create Table.

--
Bri

[rkc]

AUTOINCREMENT

[minjie]

rkc wrote:

AUTOINCREMENT

Thank you guys very much! AUTOINCREMENT worked!

[Bri]

rkc wrote:

AUTOINCREMENT

Excellent! I searched through the Create Table help and it didn't have
specific info on Data Type. The Data Type help calles it AutoNumber not
AutoIncrement. Where did you find that AutoIncrement was the correct
Type? Sometimes help needs help.

--
Bri

[rkc]

Bri wrote:

rkc wrote:

AUTOINCREMENT

Excellent! I searched through the Create Table help and it didn't have
specific info on Data Type. The Data Type help calles it AutoNumber not
AutoIncrement. Where did you find that AutoIncrement was the correct
Type? Sometimes help needs help.

I have no idea where I learned half of what I know. I dug that bit
of info out of a class that writes create table ddl to an html file.

A Google search now that you know should reveal several resources.

I just did a search and discovered that COUNTER also works.

[Lyle Fairfield]

TTBOMR, when MS-Access went from JET 3.5 to JET 4.0, it was also the
time when ADPs and MSDE were at the fore-front of the glitz message
from MS. Either they forgot to tell us about JET 4.0, or many of us,
including me, forgot to look.
The !!!!!new!!!!! (6 years old?) thingmes in JET 4.0 are discussed at
http://support.microsoft.com/kb/q275561/
but not as extensively as one might wish.
JET 4.0 has some great !!!!!new!!!!! capabilities, but as I see it,
they are 99.44% ignored in CDMA and elsewhere.

[Bri]

Lyle Fairfield wrote:

TTBOMR, when MS-Access went from JET 3.5 to JET 4.0, it was also the
time when ADPs and MSDE were at the fore-front of the glitz message
from MS. Either they forgot to tell us about JET 4.0, or many of us,
including me, forgot to look.
The !!!!!new!!!!! (6 years old?) thingmes in JET 4.0 are discussed at
http://support.microsoft.com/kb/q275561/
but not as extensively as one might wish.
JET 4.0 has some great !!!!!new!!!!! capabilities, but as I see it,
they are 99.44% ignored in CDMA and elsewhere.

Since I still do most of my development in AC97, I haven't really looked
deeply into the new features of AC2K+ let alone Jet 4. This is a very
comprehensive list of the Jet 4 features and you are right, there are a
lot of great 'new' capabilities. I was particularly impressed with the
ability to create Procedures (although I suspect they are limited to
underlying querys and do not have the features that T-SQL has) and with
the ability to do bi-directional replication with SQL Server. I've added
that link to my ever growing list of Access resources. Thanks.

--
Bri

[Bri]

rkc wrote:

Bri wrote:

rkc wrote:

AUTOINCREMENT

Excellent! I searched through the Create Table help and it didn't have
specific info on Data Type. The Data Type help calles it AutoNumber
not AutoIncrement. Where did you find that AutoIncrement was the
correct Type? Sometimes help needs help.

I have no idea where I learned half of what I know. I dug that bit
of info out of a class that writes create table ddl to an html file.

A Google search now that you know should reveal several resources.

I just did a search and discovered that COUNTER also works.

Searching for AUTOINCREMENT in help found it in a comparison of data
type equivalents to ANSI SQL data types and in the list of reserved
words. Everywhere else in help it is referred to as Autonumber. I guess
this lack of consistancy is why it was so hard to find. None of the help
examples showed it's use either. In the link that Lyle supplied, it is
explained and is used in an example.

Thanks for the info.

--
Bri

[david epsom dot com dot au]

When Jet 4.0 came out, we were told that:

"But to maintain backward compatibility with earlier versions of Microsoft
Jet, the enhancements to SQL are available only when the database engine is
set to operate in ANSI SQL 92 mode. "

As it turns out, either that was only partly true, or else DAO has since
been extended. I was told that I couldn't use the new ALTER TABLE stuff: it
was years later that someone else pointed out that I could.

(david)
"Lyle Fairfield" <ly***********@aim.com> wrote in message
news:11**********************@j33g2000cwa.googlegr oups.com...

TTBOMR, when MS-Access went from JET 3.5 to JET 4.0, it was also the
time when ADPs and MSDE were at the fore-front of the glitz message
from MS. Either they forgot to tell us about JET 4.0, or many of us,
including me, forgot to look.
The !!!!!new!!!!! (6 years old?) thingmes in JET 4.0 are discussed at
http://support.microsoft.com/kb/q275561/
but not as extensively as one might wish.
JET 4.0 has some great !!!!!new!!!!! capabilities, but as I see it,
they are 99.44% ignored in CDMA and elsewhere.