AutoIncrement field seed problem

[Matt Jolly]

Hi

I am having trouble when I add an AutoIncrement field to an exisitng table.
The problem is that the initial Seed value is fixed at 1 and ignores data
that is already in the table. In other words, creating the field generates
values for the existing records (e.g. 1, 2, 3, 4, 5...) but then when I go
to add a new record it tries to use 1 again (and as I have added a unique
index on the field this fails).

Here is the code I have used to create the field:

' Add Id field
Set Fld = TDef.CreateField("Id", dbLong)
Fld.Attributes = Fld.Attributes Or dbAutoIncrField
.Fields.Append Fld

SQL = "CREATE UNIQUE INDEX IdOrder ON Lot (Id ASC) WITH DISALLOW
NULL"
Tdb.Execute SQL

I have found a way to set the Seed value on such a field using ADO, or when
creating a table using DDL, but I am limited to adding the field to an
existing table using DAO. Any ideas?

Cheers

Matt

 

[Allen Browne]

DAO has not been updated to set the Seed of the AutoNumber.

You can fudge it to a higher number by appending a record with the value 1
less than you want, and then deleting it. Access automatically sets the Seed
in this case.

Details:
Set AutoNumbers to start from ...
at:
http://allenbrowne.com/ser-26.html