By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,395 Members | 2,513 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,395 IT Pros & Developers. It's quick & easy.

Reset Auto Number Fields

P: n/a
I apologize if this question has been asked before...

I'm trying to reset autonumber fields back to 1. The autonumber fields are
part of the composite primary keys.

I followed the MS help with a temp table and append query. I can change the
number to anything but 1. I'm using Access XP and saving as 2000.

Any help is appreciated.
Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
The AutoNumber should reset if you delete all the data from the tables, and
then compact the database:
Tools | Database Utitlities | Compact

If it does not do that, you probably need the JET 4 service pack from:
http://support.microsoft.com/gp/sp

If you want to programmatically examine whether your tables have bad
autonumbers, you can use this link:
http://allenbrowne.com/ser-40.html
and you could use the technique described in that article to reset the Seed
of your AutoNumber.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"trueblue7" <kp*********@cfl.rr.com> wrote in message
news:NF*****************@tornado.tampabay.rr.com.. .
I apologize if this question has been asked before...

I'm trying to reset autonumber fields back to 1. The autonumber fields are
part of the composite primary keys.

I followed the MS help with a temp table and append query. I can change
the number to anything but 1. I'm using Access XP and saving as 2000.

Any help is appreciated.

Nov 13 '05 #2

P: n/a
Thanks, it worked. It would have saved be a lot of time if that information
would have been included in the help.

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:43***********************@per-qv1-newsreader-01.iinet.net.au...
The AutoNumber should reset if you delete all the data from the tables,
and then compact the database:
Tools | Database Utitlities | Compact

If it does not do that, you probably need the JET 4 service pack from:
http://support.microsoft.com/gp/sp

If you want to programmatically examine whether your tables have bad
autonumbers, you can use this link:
http://allenbrowne.com/ser-40.html
and you could use the technique described in that article to reset the
Seed of your AutoNumber.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"trueblue7" <kp*********@cfl.rr.com> wrote in message
news:NF*****************@tornado.tampabay.rr.com.. .
I apologize if this question has been asked before...

I'm trying to reset autonumber fields back to 1. The autonumber fields
are part of the composite primary keys.

I followed the MS help with a temp table and append query. I can change
the number to anything but 1. I'm using Access XP and saving as 2000.

Any help is appreciated.


Nov 13 '05 #3

P: n/a
>I'm trying to reset autonumber fields back to 1. The autonumber fields
are part of the composite primary keys.

I had the opposite problem -- preventing autonumber from resetting to 1
after compact and repair. It automatically resets to 1 if there is no
data in the table.

I use autonumber for generating invoice numbers -- OK I know I shouldn't,
but I wasn't aware of the reasons when I started.

As a batch of invoice numbers have been generated and invoices sent I
delete the invoice numbers from the table.

Having once done a compact and repair and lost my numbering, I now make
sure there is one invoice number in the table at all times. That way,
when I compact and repair, my numbering is preserved.

Malcolm Webb
Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.