AutoNumber field in VBA

  • Thread starter Thread starter Eric Dreksler
  • Start date Start date
E

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
 
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
 
That did the trick.

Thanks again,
Eric Dreksler

Douglas J. Steele said:
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!)



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

CurrentDb.TableDefs("rAutoCollectionLettersExtended_").Fields.Append
Click to expand...
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
Click to expand...
Click to expand...
 
Eric Dreksler said:
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.
Click to expand...


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;"
 
Back
Top