Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Feb 2002
    Posts
    155
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Problem with autonumber key field (A2K SR1)

    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?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Problem with autonumber key field (A2K SR1)

    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.

  3. #3
    2 Star Lounger
    Join Date
    Feb 2002
    Posts
    155
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problem with autonumber key field (A2K SR1)

    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.

  4. #4
    2 Star Lounger
    Join Date
    Jun 2002
    Location
    vancouver, BC, Br. Columbia
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problem with autonumber key field (A2K SR1)

    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. <img src=/S/bummer.gif border=0 alt=bummer width=15 height=15>

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Problem with autonumber key field (A2K SR1)

    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.
    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •