Annoying AutoNumber Problem!

[mhayenga]

Ok... I have a database and a program which views the fields in the database. I am using an access 2000 database, ADO, and Jet.OLEDB.4.0. Each entry in the table has an ID number which I am automatically assigning with AutoNumber. The problem is that when I delete a record(for example say that it had ID "15"). The next record that I add will have ID "16", without assigning a record to ID 15. How do I prevent this?

Thanks In Advance....

------------------
Mitchell Hayenga
[email protected]

[chrisjk]

You can't. The idea behind autonumber is so that every record in the table will be unique. At no time will it ever re-use a number. For this reason, deletetions make "gaps" in the numbering system.

You don't have to use autonumber - you could just leave the field as a Number type, and assign your own custom numbering system that did take account of deletions.

Regards,

------------------
- Chris
[email protected]
If it ain't broke - don't fix it

[Paul282]

That's right. Don't use the ID externally. leave it as a primary key for relative tables, joins etc. create your own lookup method. if you need uniqueness and your user base is low throw the return of a now() in there, you'll have the record creation time then too. if the DB is big then stick an index on the column.

you can reset the ID with the truncate table statement but you also delete the contents of the table. My advise is... don't use that field, create you're own