Problem with autonumber key field (A2K SR1)

Topic

New Reply

My database has an autonumber key field and it is giving me problem. I wish somebody had told me not to use autonumber as my key field. But I am too deep in my database design and VBA codes to turn back now. Sigh.

Here is my problem:

I have some records deleted from my database, leaving some gaps in the autonumber. The deleted records were kept in another table within the same database in case I wanted to re-use them. Recently I imported some records from an Excel file which didn’t have an autonumber field in the header row. After importing, I found that the gaps that were left behind by the deleted records were filled with the new records plus some new records with new (previously unused) autonumber. When I attempted to undelete my deleted records, obviously redunctancy of autonumber occurred, resulting in error.

Is it what normally happened to tables with autonumber key field? (I remember I read somewhere that autonumber field will start automatically with the next highest number.) How would I import records from an Excel file so that it will always start with a new autonumber?

Reply | Quote

Replies

There was a problem with AutoNumbers in Jet 4.0 prior to SP4. See ACC2000: AutoNumber Field Duplicates Previous Values After You Compact and Repair a Database for a description of this problem. You can download the latest version of Jet 4.0 – currently SP6 – from ACC2000: Updated Version of Microsoft Jet 4.0 Available in Download Center. To find out which version you currently have, select Help/About… in Access, click System Info, select Active Modules and note the version number of MSJET40. Compare this to the version history given in the second link given above.

Reply | Quote

I had problems with autonumber before and Wendell had advised me to check my version of MSJET. My version of MS Jet is SP6. It looks like there is fundamentally something not quite right with my codes. I will check my codes thoroughly before I cry wolf. Thanks HansV.

Reply | Quote

I understand your frustration but I still think using autonumber as a key field is a good idea. Also, you’d never be able to get those deleted records back into their original table with the same autonumber as they had before. If you’re concerned with sequential numbering, then you need to have a new field to contain the sequential number and increment it through code.

Reply | Quote

There is nothing at all wrong with autonumbers but you have to give some thought to how you use them. I use them by preference in all my designs because they are not meaningful values and so they are not subject to change.

If you’re concerned about gaps in autonumbers, then don’t use them. If you intend to “archive” data and remove it from your current database then don’t use an autonumber or else understand very clearly what the pitfalls are and take precautions. There is no earthly reason to worry about gaps in autonumbers because autonumbers are NOT data, they are simply unique values to identify a records. There are inherently and intentionally meaningless in and of themselves. If you insist on a meaningful number as your key, then don’t use an autonumber.

Reply | Quote