Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    New Lounger
    Join Date
    Jan 2001
    Location
    Victoria, BC
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Autonum change in linked table (Access97)

    I have been trying to increase the autonum number (jump forward) and used the instructions provided in the help. Though, whenever I complete the tasks of deleting the record and re-setting the validation rules/relationships etc., the starting autonum is not where I want it to be (1 after the appended number). What is the correct way to do this? I have tried to un-split, run the append query, and then split again, but to no avail <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Autonum change in linked table (Access97)

    I don't know why you are changing validation rules and relationships. All you need to do is run the append query. New autonumbers will then pick-up after the number. New autonumbers will then pick-up after that new number. Don't delete the dummy record created by the append and don't compact the database until you have entered some new valid records.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    New Lounger
    Join Date
    Jan 2001
    Location
    Victoria, BC
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autonum change in linked table (Access97)

    I should have mentioned that the database is already in use - past dev. If the validation rules are left the way they are, an append will fail as some fields are set to 'required'. Perhaps the relationships and removing the PK are not necessary(?)

  4. #4
    Star Lounger
    Join Date
    Feb 2001
    Location
    Wirral, Merseyside, Merseyside, England
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autonum change in linked table (Access97)

    Bad choice to use the autonumber as a key field or as a join field as the autonumber can reset on compact, restore etc... You will have great difficulties continuing with autonumber as is.

    If you want to continue as you are then the only thing I can think of is to put a dummy record in with the autonumber set to one before the number you want then delete the dummy record. The autonumbering may then continue from the number you want.

    My choice would be to recreate the table with the current autonumber set as just a number field, append your data into it, and put the code into the database to create the number sequencing for you.

    It is bad practice to create a Primary Key on an autonumber field, it nearly always ends in problems when you need to restore data especially when rows have been deleted as it will re-number your records.

  5. #5
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Autonum change in linked table (Access97)

    >>It is bad practice to create a Primary Key on an autonumber field, it nearly always ends in problems when you need to restore data especially when rows have been deleted as it will re-number your records.<<

    Do you mean it will renumber existing records? I have never seen that before. An autonumber is generally a perfect choice for a PrimaryKey since it can not be changed.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  6. #6
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Autonum change in linked table (Access97)

    Yes, the append will fail if certain required fields don't have data in them. So you can either remove the Required flag (as you did), or just make sure you are supplying valid data in your append. As for relationships, I forgot that Access balks at changing to an autonumber unless you delete relationships, even if you are changing from a long interger. Of course, you only need to change the relationships that include this field. It is not really necessary to delete the PK, since the same field will remain the PK; but this trivial.

    I was assuming you were not going to renumber the existing records; rather you just wanted to start numbering new records from the number specified in your append query.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  7. #7
    New Lounger
    Join Date
    Jan 2001
    Location
    Victoria, BC
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autonum change in linked table (Access97)

    Thanks. Would it make a difference if I made a query in the front end (database is split) or did it to the _be.mdb(back-end)?

  8. #8
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Autonum change in linked table (Access97)

    It makes no difference where you run the query from. You could do the whole thing from the frontend, if you were comfortable opening the backend database in code and deleting relationships, etc. I'm not!<g> So, I would do it all from the backend.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  9. #9
    Bronze Lounger
    Join Date
    Feb 2001
    Location
    Escondido, California, USA
    Posts
    1,458
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Autonum change in linked table (Access97)

    Mar,

    I did not see any response to your question regarding the use of Autonumber fields as primary keys, and I am MOST curious about it. I agree with your comment that Autonumbered fields are a perfect choice for a Primary Key. If not, then I'm going to have a horrendous mess some time in the not too distant future!

    Have you heard anything more about this? It is my understanding that autonumber will not renumber the records when some are deleted or compressed or repaired. I sure hope that is the case.

  10. #10
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Autonum change in linked table (Access97)

    >>then I'm going to have a horrendous mess some time in the not too distant future!<<

    Chuck,

    You and me both! I haven't heard any more about this, but I figured it might be because of the weekend. However, since I've never heard that comment anywhere before, I'm not too worried. I'm guessing we misinterpreted the statement, and it perhaps had to do with adding an autonumber field to a record.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  11. #11
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autonum change in linked table (Access97)

    I agree that an autonumber field is a great choice for a primary key. However, I've encountered "challenges" with using autoumber primary key fields in the following situation: When I archive records from my "active" table to an "archive" table, I'd like the primary key (the autonumber field) to remain unique across all records in both tables (since it provides the relationship to various other tables). However, once a particular autonumber value is gone from the "active" table, a new record added may be given this value, thus duplicating a primary key in the "archive" table. To avoid this, (based on advice from Charlotte here in Woody's Lounge), I set up a "master" list of if IDs that are autonumbered. They then relate to an ID field in both the archive and active tables which are not autonumbered. This adds some overhead when adding and deleting records (adding a record to the active table means adding a new record to the master list (with a new and unique-across-both-tables autonumber primary key ID), then actually adding the new record in the active table and copying the master list autonumber value into the field that's related to the master list ID). Deleting records can be taken care of through referential integrity. It seems like there ought to be a less cumbersome way of doing this -- is there???

    Regardless, even with this sort of arrangement, I've never seen autonumber fields getting re-numbered. Boy, would that be a mess!

    Tom <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  12. #12
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Autonum change in linked table (Access97)

    Well, you could set-up your autonumber to create a random number rather than a sequential one. The odds that you would re-use an autonumber is very small. This is what is used in replicated databases.

    If you never archive the most recent active record (that is, with the highest autonumber) then even a sequential autonumber should not duplicate. Even after a compact, the next autonumber assigned will be 1 more than the current highest number. You could aways do a DLookup to see if it was in archive table.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  13. #13
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autonum change in linked table (Access97)

    I, too, thought the autonumber-increment method would use the next higher number. But I found duplicates appearing (as described in my previous post) -- the newer records were being assigned "old" (archived) numbers even though there were higher active autonumbers -- the autonumbering scheme seemed to be "filling holes". I'll have to admit that I wasn't too diligent about repairing/compacting after archiving (removing records from the active table) -- that may be part of the problem. Also, and this may be circumstantial, I noticed this problem only after upgrading from Access 97 to 2000.

    Autonumber-random is a good idea. With the odds of duplicating an existing record at around 1 in 4 billion, I guess it's pretty safe (unless you've got a fairly big database!). If I were to worry about such odds, I should be buying a lot more Lotto tickets!

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

    Re: Autonum change in linked table (Access97)

    Sorry, but I disagree with your advice on autonumbers. I've been using them without problems since Access 1.0. They are *not* suitable if you want an uninterrupted sequence of numbers, but requiring that suggests that the key will have meaning, and that violates the whole idea of the autonumber.
    Charlotte

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

    Re: Autonum change in linked table (Access97)

    What you're describing has always existed with autonumbers, and it's really more an issue of the way you're archiving than a problem with autonumbers.

    If you delete records (either after archiving or for any other reason), a compact will reset the next sequential autonumber to the next number after the highest autonumber left in the table. If you delete *all* the records, the next highest number is 1, and you would have to keep a "seed" record in the table to avoid stepping on archived numbers. However, a simpler remedy is to have an additional field that will work with the autonumber to provide a unique key, even if the autonumbers are the same.
    Charlotte

Page 1 of 2 12 LastLast

Posting Permissions

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