AutoNumber field in VBA

[Eric Dreksler]

I'm trying to add an AutoNumber field to a spreadsheet I've imported into a
table. I'm adding a couple of columns using the following format:

CurrentDb.TableDefs("rAutoCollectionLettersExtended_").Fields.Append
CurrentDb.TableDefs("rAutoCollectionLettersExtended_").CreateField("CurrentS
tatus", adInteger)

I'm trying to find the [Type] option that will set the field to
AutoIncrement (or any other method), just so I don't have to have sequence
function hanging around and for simple ease of design purposes.

Thanks In Advance,
Eric Dreksler

 

[Douglas J. Steele]

Here's an example from the Help file. fld1 is an Autonumber. Note that you
need to create the field as a Long Integer (dbLong), then change its
Attributes before you append it to the TableDef object. You can't do it in a
single step like you're trying to do.

Dim dbs As Database
Dim tdf As TableDef, fld1 As Field

Set dbs = CurrentDb
Set tdf = dbs.CreateTableDef("Contacts")
Set fld1 = tdf.CreateField("ContactID", dbLong)
fld1.Attributes = fld1.Attributes + dbAutoIncrField
tdf.Fields.Append fld1

 

[Eric Dreksler]

That did the trick.

Thanks again,
Eric Dreksler

Here's an example from the Help file. fld1 is an Autonumber. Note that you
need to create the field as a Long Integer (dbLong), then change its
Attributes before you append it to the TableDef object. You can't do it in a
single step like you're trying to do.

Dim dbs As Database
Dim tdf As TableDef, fld1 As Field

Set dbs = CurrentDb
Set tdf = dbs.CreateTableDef("Contacts")
Set fld1 = tdf.CreateField("ContactID", dbLong)
fld1.Attributes = fld1.Attributes + dbAutoIncrField
tdf.Fields.Append fld1


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I'm trying to add an AutoNumber field to a spreadsheet I've imported

into

a
table. I'm adding a couple of columns using the following format:

CurrentDb.TableDefs("rAutoCollectionLettersExtended_").Fields.Append

CurrentDb.TableDefs("rAutoCollectionLettersExtended_").CreateField("CurrentS

tatus", adInteger)

I'm trying to find the [Type] option that will set the field to
AutoIncrement (or any other method), just so I don't have to have sequence
function hanging around and for simple ease of design purposes.

Thanks In Advance,
Eric Dreksler

 

[Marshall Barton]

I'm trying to add an AutoNumber field to a spreadsheet I've imported into a
table. I'm adding a couple of columns using the following format:

CurrentDb.TableDefs("rAutoCollectionLettersExtended_").Fields.Append
CurrentDb.TableDefs("rAutoCollectionLettersExtended_").CreateField("CurrentS
tatus", adInteger)

I'm trying to find the [Type] option that will set the field to
AutoIncrement (or any other method), just so I don't have to have sequence
function hanging around and for simple ease of design purposes.

Set fld = tbl.CreateField("SeqNo", dbLong)
fld.Attributes = dbAutoIncrField
tbl.Fields.Append fld

An alternative is to use an SQL DDL statement. Here's an
untested example:

DataDB.Execute _
"ALTER TABLE tablename" _
& "ADD COLUMN [keyfield] COUNTER" _
& " CONSTRAINT PrimaryKey PRIMARY KEY;"