AutoNumbered KeyField Long Integer duplicating value

[wilpeter]

MemberID field is the Key in a busy Members table that has been in use for almost a decade. The field is an AutoNumber type, formatted 00000, and is Long Integer. As a member's activity becomes dormant in excess of 2 years, the record is 'archived' (a 'dormant date' is inserted in an existing field and that record is appended to a matching Archived Members table. The process leaves gaps in the sequentially numbered records that remain.

When a Member returns at a later date, the 'dormant date' in his record in the Archived Members table is removed and the record is appended to the Members table. 99% of the time this has not been a problem, since the absent MemberID was not assigned to anyone else during its absence.

My question is: Is there a different (preferred) handling of this Key field that would avoid the occasional reassignment of a missing number to a new member? Are there any Properties or Data types that come into play to guarantee non-duplication when the Key field must remain vacant yet its value must remain assigned. Put yet another way: Can Access 2010 guarantee that a corresponding field in two tables will not hold a duplicate value?
Every couple of years (i.e. rarely), the field value increments hugely (once by 60,000 numbers) and we don't notice it for a few days. We append an unassigned number in the appropriate range and this 'slap on the wrist' seems to restore the incrementing to the lower range.

The database is an mdb (from A2003) but being operated in A2010 without converting. Thanks for any insights you may have.

[ssanfu]

Your main problem (IMO ) is that if you are truly are using an autonumber field, you are misusing it. An autonumber does not and should not have any real world meaning.

See these sites:

http://www.utteraccess.com/wiki/index.php/Autonumbers
http://www.fmsinc.com/free/newtips/primarykey.asp
http://www.bluemoosetech.com/microso...ial.php?jid=54
http://access.mvps.org/access/general/gen0025.htm



If you need sequential numbers, look at Paul's site: http://www.baldyweb.com/CustomAutonumber.htm

[wilpeter]

Well I thank you for the references and compliment you on your speed of delivery. I visited and read each of them, but failed to find the answer to my question. As a novice, I likely understood 60-75% but did satisfy myself that I'm on the right track. Long Integer was good because I won't run out of numbers; the AutoNumber is safely meaningless to me and to the member; the order of entry is not important (we have a date field for that). It is not used for sequencing (we use LastName/FirstName) for that. The gaps are not a problem as people die (yes, even in Canada); in fact, 4 out of 5 originally autonumbered records have dropped out from leaving. We never deliberately reassign a deleted/missing number, with so many new ones available. The MemberID is the One side of several linked relationships.

So, back to the original question...anybody?

[ItsMe]

You should be good for just over two million records.

If you are archiving to another table, I would just be sure to add another primary key to the archive table. The original MemberID should act as a foreign key. if there is a need to bring back a specific record relative to the MemberID, you would just append it the correct way back into the original table and the MemberID field will go into the Autonumber field not problem.

Avoid changing the MemberID data type when it is in your archive table as an FK (keep as long)

The only hiccup I can imagine is if...

One day you archive a member's record into the other table. It just so happened this member is the Newest member. His MemberID is the last in the series of Autonumbers. That evening you decide to do your diligent "Compact and Repair'. The next morning, a new member signs up and gets the MemberID of, you guessed it, the retired member from the evening before.

I believe this is how this will play out. So don't compact and repair until just after you get a new member. Or you could just use a Boolean to show a MemberID as archived and leave it be. Then, run an archive on a schedule to avoid the compact and repair blues. The schedule would have a rule like... Hey we got a new member! Lets archive before we create a new MemberID record! Yippee

Or something

[wilpeter]

No problem with Archived Member Data, with respect to it having its own PK. Also, there are no transactions performed on records in Archive--other than reinstatement or eventual deletion--it's just a parking lot.

Archiving is done quarterly. Reinstatement is done anytime a member decides to walk in after his absence.

Compact & Repair has been done every time the database is closed over the past 10 years--i.e. perhaps four times a day. Some days, several members join, then it might be months before another joins.

I tested your theory and you were right. If a new member was accidentally archived (with 364 days left on his membership!), and the database closed and compacted, the 'next number generated' would be that same number. Then, the accidentally archived member could not be reinstated without giving him a brand new number. Interestingly enough, when the reinstatement fails, Error Code 2950 is cited. Which, if I'm not mistaken is a catch-all code for "blowed if I know what happened!"

Anyway, I'm continuing without change...except that we'll write down exactly what transpired if it happens again (maybe 6 months from now).
Appreciate your input!

[ItsMe]

I suppose an update query of the foreign keys with the new MemberID would not be the end of the world.

Cheers