Auto-increment field reset

[Alan T]

I have inserted and deleted records into a table has auto-incrment field as
primary key.
Now the table is empty after I deleted all the records inside Access 2003,
how do I reset the auto_increment field value to 1?

 

[Jeff Boyce]

Why? As in "why do you care what value the Access autonumber field holds?"

Access autonumbers are intended to be used as generally unique row
identifiers, to aid in establishing relationships among tables. They are
not particularly fit for human consumption.


--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[Joseph Meehan]

I have inserted and deleted records into a table has auto-incrment
field as primary key.
Now the table is empty after I deleted all the records inside Access
2003, how do I reset the auto_increment field value to 1?

You can do a compact of the database, but you probably should not. The
autonumber function offers too many ways of getting out of order to be used
for anything other than what was intended, that is providing a unique
number. It is best that you don't allow users to even see it.

If you need consecutive numbers then the usual method of providing them
is the use of DMAX.

 

[Paul Shapiro]

Compact the database, which is the menu name for reorganizing the database.
From the Tools menu, Database Utilities, Compact and Repair. This resets all
autonumbers to one more than the highest existing number.
Paul Shapiro

 

[Guest]

autonumbers to one more than the highest existing number.

AFAIK, it doesn't even do that anymore. It only resets
Empty Tables to One.

In some (past) versions of Jet, compact used to reset
Autonumber to Max+1, but Autonumber was broken
in Jet 4.0 since first release, and as part of the
abortive fiddling around trying to get it to work
correctly, reset to Max+1 was been dropped.

Unless it was changed again as part of A2007.

(david)

 

[Paul Shapiro]

That's interesting. In A2003 I thought compacting still resets all the
autonumbers. But I just tested it and the autonumber was NOT reset.

I did find a case where using SQL Server DTS or SSIS to transfer data from a
Sql Server db to Access would consistently trash the autonumbering and
nothing repaired it. Each new row was given an existing number, so the
inserts always failed. Compacting didn't help at all- maybe because it's not
resettting the autonumber any more? My only solution was to transfer data by
linking the SQL Server tables into the Access db and using Access sql to do
the transfer. Then the autonumbers kept working.
Paul Shapiro

 

[Guest]

I have this issue, A2007 does not reset auto number fields to max+1 on
compacting, which according to the help file it should.

This will become an issue for databases as extramely large auto number
values will degrade performance of the database overtime.

In my case the table has about 800 records and new records come in at about
615647. This is just like the old jet 4 issue with auto number fields.

 

[Todos Menos [MSFT]]

with SQL Server and Access Data Projects; you can specify SEED and
INCREMENT values

add that to the list of 'yet another thing that MDB can't do'

 

[Todos Menos [MSFT]]

Paul

truncate will reset to the seed value
truncate is a heck of a lot faster than delete from also

 

[Todos Menos [MSFT]]

DMax?

do you understand the performance implications of using DMAX?

you must have always worked on really really tiny databases, kid

 

[RoyVidar]

with SQL Server and Access Data Projects; you can specify SEED and
INCREMENT values

add that to the list of 'yet another thing that MDB can't do'

but, but, but, Aaron, that's been possible some years, starting with
the introduction of Jet 4.0.

currentproject.connection.execute _
"ALTER TABLE myTable ALTER COLUMN myAuto INT Identity (1, 1)"

or, the following DDL should also work

"ALTER TABLE myTable ALTER COLUMN myAuto COUNTER(1,1)"

what about knowing just a tiny bit about the technology you bash?

 

[Todos Menos [MSFT]]

what about 'who gives a crap' and it's not helpnig you guys in this
situation

I don't need to know anything else about MDB; it failed the litmus
test a decade ago; and you kids are stuck in the 90s

DAO is too buggy for real world use.
ADO is _ALWAYS_ faster.

BECAUSE OF THE FACT THAT MDB IS TOO UNRELIABLE FOR REAL WORLD USE

 

[Todos Menos [MSFT]]

introduction of Jet 4.0?

you mean Access 2000?

Jet 4.0 isn't worthy of anything; because anyone using Jet 4.0 _CAN_
be using ADP and ADP is superior technology


Next you'll be claiming that you can use SQL PROFILER with MDB crap

ROFLMAO

 

[Guest]

Append query using linked tables including the auto-number
field ALWAYS resets the autonumber field (again, unless it
has been fixed and I wasn't told). It resets to the last appended
value, which would be ok if the target table was empty, but...

(david)

 

[Guest]

You can explicitly set the autonumber start value (this was
the new feature which led to all the broken behaviour).

Search around: sample code is available.

(david)