Programmatically add AutoNum

[Guest]

Using Office 2003 and Windows XP;

I thought the following SQL would append an AutoNum to an existing table:

Dim sSQL As String
sSQL = "ALTER TABLE [tblMyTable] ADD COLUMN ([RecordID] IDENTITY (1, 1));"
CurrentDb.Execute sSQL

But, I get "Syntax error in field definition"; Does anyone know how to get
this right?

Thanks much.

 

[Marshall Barton]

Using Office 2003 and Windows XP;

I thought the following SQL would append an AutoNum to an existing table:

Dim sSQL As String
sSQL = "ALTER TABLE [tblMyTable] ADD COLUMN ([RecordID] IDENTITY (1, 1));"
CurrentDb.Execute sSQL

But, I get "Syntax error in field definition"; Does anyone know how to get
this right?

I think(?) this would be the Jet equivalent:

ALTER TABLE tblMyTable
ADD COLUMN RecordID COUNTER
CONSTRAINT pk PRIMARY KEY