problem with repeating autonumbers (Access 97)

**hohepa** · 2005-09-01 00:07

Hello, I built a simple database for a friends dog boutique so she could keep track of her customers.

She told me that when she goes to add a new contact that she receives a message that the record cannot be added because it duplicates an existing index. Looking at her customer table I noticed that when a new record is added the autonumber that is being inserted is a number that has already been used. For instance there are 436 records in the table, and the new record I tried to add had an autonumber of 18. The field is set to increment.

Any ideas on how to fix this (and keep it from happening again) would be appreciated. Thanks

**jacksonmacd** · 2005-09-01 01:42

Try compacting the database. That should reset the Autonumber.

**MarkLiquorman** · 2005-09-01 11:21

This is a known and recurring problem. The cause is often doing an append query in which the autonumber field is specified, rather than allowing Access to assign it. Make sure you have all updates to Access and Jet.

To fix this, you will need to go to my website below, on which I have a utility you can download. The easiest one to use is the DAO one.

**HansV** · 2005-09-01 11:32

Does your utility work in Access 97? (See subject line)

**MarkLiquorman** · 2005-09-01 11:45

The db on my website is an A2000 database. However, the technique will work regardless, you'd just need to convert it back.

**MarkLiquorman** · 2005-09-01 12:11

I just updated my website with an Access97 version. Interesting, though, as I thought this was only an Access2000 problem?

**HansV** · 2005-09-01 12:24

Thanks for that!

The problem with AutoNumbers is a known and documented Access 2000 problem, this is the first time I've seen it mentioned for Access 97.

**hohepa** · 2005-09-02 15:14

Thanks, your tool solved the problem. It was my mistake but this was for an Access 2000 issue. Sorry for misleading everyone. Take care

Thread: problem with repeating autonumbers (Access 97)

Thread Tools