Re: How to create an AutoNumber field with a SQL statement?



minjie@xxxxxxxxxx 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

.

Relevant Pages