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?

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
http://mvp.support.microsoft.com/

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

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

"Alan T" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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?
>
>

Alan T wrote:
> 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.

--
Joseph Meehan

Dia 's Muire duit

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

"Alan T" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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?

> 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" <(E-Mail Removed)> wrote in message
news:%23gdq%(E-Mail Removed)...
> 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
>
> "Alan T" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> >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?
>
>

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

<david@epsomdotcomdotau> wrote in message
news:OAxwZ$(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
> news:%23gdq%(E-Mail Removed)...
>> 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
>>
>> "Alan T" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>> >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?

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.



"Joseph Meehan" wrote:

> Alan T wrote:
> > 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.
>
> --
> Joseph Meehan
>
> Dia 's Muire duit
>
>
>
>

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'





On Apr 1, 5:01 pm, "Alan T" <alanpltseNOS...@yahoo.com.au> wrote:
> 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?

Paul

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




On Apr 2, 10:30 am, "Paul Shapiro" <p...@hideme.broadwayData.com>
wrote:
> 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
>
> <david@epsomdotcomdotau> wrote in message
>
> news:OAxwZ$(E-Mail Removed)...
>
>
>
> >> 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" <p...@hideme.broadwayData.com> wrote in message
> >news:%23gdq%(E-Mail Removed)...
> >> 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
>
> >> "Alan T" <alanpltseNOS...@yahoo.com.au> wrote in message
> >>news:(E-Mail Removed)...
> >> >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?- Hide quoted text -
>
> - Show quoted text -

DMax?

do you understand the performance implications of using DMAX?

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





On Apr 1, 6:09 pm, "Joseph Meehan" <sligoNoSPAM...@hotmail.com> wrote:
> Alan T wrote:
> > 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.
>
> --
> Joseph Meehan
>
> Dia 's Muire duit