Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    data808 is offline Noob
    Windows 8 Access 2007
    Join Date
    Aug 2012
    Posts
    727

    Primary Key Autonumber

    I have a table that has a primary key that does an autonumber in increments. Then I made some queries that will append records by date and then also another query that will delete what was appended. Then I created a reverse sequence of this process in case I change my mind and would like to put back the records I archived, however, when I do this, the primary key is now thrown off and will still revert to the last primary key that was appended before the archive ever took place. In other words it still thinks that the records I appended before I did the reverse, are still there. So now it will not let me save the record because it is a duplicate.



    Is there a way to set the autonumber for the primary key to where I want it to begin? If not, how do you fix something like this?

    Thanks.

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    The purpose of the autonumber is to provide a unique number - not necessarily positive nor incremental/sequential. When you use the autonumber as PK, the DBMS uses this to uniquely identify each record in your table. If you need a separate number that you can control, why not add a field to your table, populate it and use it for your purposes. The DBMS will use the autonumber PK for its system purposes.

    see this for more info

  3. #3
    data808 is offline Noob
    Windows 8 Access 2007
    Join Date
    Aug 2012
    Posts
    727
    Thanks. I do understand what the primary key is and what its used for. If the primary key is supposed to create a unique key then why is it creating duplicates? I know it has something to do with my archiving then reversing the archive. I just need to know how to fix it. Making a text box for me to have to manually give it a record number is not sufficient. I like how the auto number/primary key works, its just not working like its supposed to at the moment. Like I mentioned above, when I archive it takes out a set of records that I choose by date. Those records have a primary key number and now the database thinks those records are gone and can be reused which is correct if I don't reverse the archive I just did. Now if I archive the records back, meaning reverse the process, the database thinks those records still don't exist in the table and will continue to use those primary keys that are now back in the table. This prevents me from saving the record because its a duplicate. So how do I make the database know that these primary keys are now back in the table. Shouldn't the database know what keys are there and what keys aren't?

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Instead of moving (appending and deleting records) for archiving, why not just have a field that indicates this status?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    data808 is offline Noob
    Windows 8 Access 2007
    Join Date
    Aug 2012
    Posts
    727
    Indicates that the record has been copied to another table? I'm trying to remove the clutter of old records to somewhere else.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    No, indicates that record should be excluded from queries. "Clutter" should not be a concern. Archiving might be a valid process if db is reaching size limit, in which case multiple backends might be better. Use queries to exclude records that are considered 'archived'. This can be a date cutoff or a Yes/No field.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    data808 is offline Noob
    Windows 8 Access 2007
    Join Date
    Aug 2012
    Posts
    727
    Well that is a good idea but size limit is also a concern. I don't want it to reach the size limit.

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    How likely is it will hit the limit?

    Did you run Compact & Repair then try to 'restore' archived record with the old PK?

    Might not be able to use autonumber as PK/FK if you want to do this archiving. Are you also archiving related records from dependent tables?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    wes9659 is offline Advanced Beginner
    Windows 8 Access 2010 64bit
    Join Date
    Apr 2014
    Location
    Dover, Ohio
    Posts
    88
    Do you simply wish to start your auto number count from 1 again? Can you zip and attach table?

  10. #10
    data808 is offline Noob
    Windows 8 Access 2007
    Join Date
    Aug 2012
    Posts
    727
    Not necessarily start from 1 although that would be good to know how to do in the future if I ever have to. I really would like to just get the table to recognize what primary keys are already in use so that when the auto number function takes place it won't be a duplicate. I can't send the table because there is confidential info in it. Sorry.

  11. #11
    data808 is offline Noob
    Windows 8 Access 2007
    Join Date
    Aug 2012
    Posts
    727
    What is the file size limit? How does it work? Is the limit by how many records a file can handle or is it the actual size of the file through MegaBytes's? Or both? I have a database that has the potential of reaching 46,800 records each year. What kind of limit or concerns am I looking at here?

    The archive that I do seems to be ok for now. I noticed that my PK is at 12,000 something at the moment. The records I have archived are in the 8,000s. Once the archive is finished I notice that the PK goes back down to the 8,000s because it knows that those numbers are not being used anymore. The problem I have is in 2 parts. The first is what I explained above and that is if I reverse these records that have a PK of 8,000 something, its almost as if the database is tricked into thinking that these records still are not being used even though I reversed the archive and now the records are back in the table so now the autonumber for the PK is creating duplicate numbers. The other problem I am predicting is that even though I do not reverse the archive and the PK goes back down from 12,000 to 8,000 to recycle what was used prior to the archive, once its starts to hit numbers that are actually being used in the table will it know to skip those numbers to create unique ones or will I run into the same problem as the reversing the archive process? Just in this case the problem will come at a later time when the PK works its way back up to the numbers that are being used?

  12. #12
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Access file size limit is 2GB.

    Is the db split? What size is the file now in bytes? How long did it take to get to that size? Do the math to estimate potential growth.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  13. #13
    John_G is offline VIP
    Windows XP Access 2003
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Hi -

    Going back to your post #3 above - I think your problem is this.

    Once you delete a record with an autonumber field in it, you cannot put it back into the table and have the same value for the autonumber field as it had when it was deleted. That is one of the features of the autonumber type.

    If you want to be able to archive and unarchive, I suggest you manage that ID value yourself, with a unique and required index on it, rather than use autonumber.

    HTH

    John

  14. #14
    data808 is offline Noob
    Windows 8 Access 2007
    Join Date
    Aug 2012
    Posts
    727
    Would the random number feature work?

  15. #15
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Here is what I was able to do.

    Create a record with autonumber (Index No Duplicates). Delete the record. Use INSERT sql to add record with the deleted ID.

    Save record to archive table with the original ID. Restore to main table with the original ID.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. AutoNumber Primary Key until manually entered
    By anonymust in forum Database Design
    Replies: 3
    Last Post: 05-11-2014, 08:34 AM
  2. Using AutoNumber primary key on sorted column
    By grkatz823 in forum Access
    Replies: 7
    Last Post: 03-04-2013, 03:47 PM
  3. Sequential autonumber primary key
    By JackCampion in forum Access
    Replies: 0
    Last Post: 09-17-2012, 05:07 PM
  4. Replies: 4
    Last Post: 05-07-2012, 12:08 PM
  5. Replies: 2
    Last Post: 04-30-2010, 09:43 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums