Recycling unused Autonumber entries in MS Access Table

Given a Microsoft Access table that has an Autonumber as it's primary key, I need an algorithm to find unused autonumber key field values; i.e. records that have either been deleted or cancelled; so that I can reuse these IDs by inserting records into the table using the unused number.

Hope this makes sense.

Eg:

Table tbl_person
ID Autonumber (PK)
Lastname         (Text)
Firstname         (Text)

Entries:

ID                    Firstname             Lastname
1                     John                     Smith
3                     Paul                      Jones


Need effective algorithm to find that ID=2 is unused.

BTW:

This table contains thousands of records, so that performance is KEY.

Regards.
LVL 13
John Mc HaleForensic Computer Examiner, Analyst/Programmer & Database ArchitectAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

omgangIT ManagerCommented:
this will find the first unused key value and return it to the called.  In my exqample it returns 2

ID_AutoNumber      LastName      FirstName
1      Smith      John
3      Jones      Paul
4      Gang      OM

OM Gang

Public Function FindUnusedKey()
On Error GoTo Err_FindUnusedKey
 
    Dim rs As DAO.Recordset
    Dim intKeyValPrior As Integer, intKeyValNext As Integer
    Dim intUseThisKeyVal As Integer
    
    intKeyValPrior = 0
    intKeyValNext = 0
    
    Set rs = CurrentDb.OpenRecordset("tbl_Person")
    rs.MoveFirst
    Do Until rs.EOF
        intKeyValNext = rs!ID_AutoNumber
        If intKeyValNext - intKeyValPrior > 1 Then
            intUseThisKeyVal = intKeyValPrior + 1
            GoTo Exit_FindUnusedKey
        End If
        intKeyValPrior = intKeyValNext
        rs.MoveNext
    Loop
    
Exit_FindUnusedKey:
    FindUnusedKey = intUseThisKeyVal
    Set rs = Nothing
    Exit Function
    
Err_FindUnusedKey:
    MsgBox Err.Number & ", " & Err.Description, , "Error"
    Resume Exit_FindUnusedKey
    
End Function

Open in new window

0
Patrick MatthewsCommented:
Hello fredthered,

If the column is set up as an autonumber, this is not possible.

Further, I am not sure why you would even want to.  The value of an autonumber primary key has no semantic
content whatsoever, so why do you care what it is?  :)

Regards,

Patrick
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
No reason and you really don't want to for a number of reasons.  Just keep letting it go up.
JimD.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

omgangIT ManagerCommented:
Actually, using an insert/append query does allow you to insert a record into the table with an unused PK autonumber value

This is what I used to insert a record with PK = 2
INSERT INTO tbl_Person ( LastName, FirstName, ID_AutoNumber )
SELECT "Gang" AS LName, "OM" AS FName, 2 AS ID;


ID_AutoNumber      LastName      FirstName
1      Smith      John
2      Gang      OM
3      Jones      Paul
4      Gang      OM


Not disagreeing with your argument that it defeats the purpose of the autonumber field and is unnecessary but it is possible and does what FredTheRed asks.
OM Gang
0
Patrick MatthewsCommented:
omgang,

You may want to declare those variables as Long.  Once you hit ID = 32,768, you get an overflow :)

Regards,

Patrick
0
omgangIT ManagerCommented:
Good point.  Yes, they should be decalared as the same type as the table field (autonumber = long).
OM Gang
0
John Mc HaleForensic Computer Examiner, Analyst/Programmer & Database ArchitectAuthor Commented:
If it were me, I would'nt bother, but my client is a little (paranoid) about gaps appearing in the PK. I guess they think that records are being deleted without authority (but then that's down to application security I guess).

Just as well I did'nt write it!

I was hoping for a binary type search for unused key though, as I say the table has almost 30K records, and I don't want thumb twiddling going on while waiting to find next available key.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<If it were me, I would'nt bother, but my client is a little (paranoid) about gaps appearing in the PK. I guess they think that records are being deleted without authority (but then that's down to application security I guess).>>
  If that's the case, then it should be converted over to a app assigned key rather then an autonumber.  I would do your best to talk them out of this.
JimD.
0
omgangIT ManagerCommented:
Won't take you much time/effort to try it and see.  I anticipate it will be much quicker than you think.  I don't have a db available right now with 30,000 records but can run a test against one this evening.
OM Gang
0
omgangIT ManagerCommented:
Small change (see snippet).  This executes and returns a value almost immediately (I populated the sample table tbl_Person with 30008 records) when the first gap is after record 30005.

Here's the result from the immediate window
?FindUnusedKey
30005, 30007
 30006


OM Gang
Public Function FindUnusedKey()
On Error GoTo Err_FindUnusedKey
 
    Dim rs As DAO.Recordset
    Dim lngKeyValPrior As Long, lngKeyValNext As Long
    Dim lngUseThisKeyVal As Long
    Dim strSQL As String
    
    strSQL = "SELECT ID_AutoNumber FROM tbl_Person ORDER BY ID_AutoNumber"
    
    lngKeyValPrior = 0
    lngKeyValNext = 0
    
    Set rs = CurrentDb.OpenRecordset(strSQL)
    rs.MoveFirst
    Do Until rs.EOF
        lngKeyValNext = rs!ID_AutoNumber
        If lngKeyValNext - lngKeyValPrior > 1 Then
            lngUseThisKeyVal = lngKeyValPrior + 1
            GoTo Exit_FindUnusedKey
        End If
        lngKeyValPrior = lngKeyValNext
        rs.MoveNext
    Loop
    
Exit_FindUnusedKey:
    Debug.Print lngKeyValPrior & ", " & lngKeyValNext
    FindUnusedKey = lngUseThisKeyVal
    Set rs = Nothing
    Exit Function
    
Err_FindUnusedKey:
    MsgBox Err.Number & ", " & Err.Description, , "Error"
    Resume Exit_FindUnusedKey
    
End Function

Open in new window

0
omgangIT ManagerCommented:
Pic of tbl_Person with gap after record 30005.
OM Gang
tbl-Person.jpg
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
John Mc HaleForensic Computer Examiner, Analyst/Programmer & Database ArchitectAuthor Commented:
OK tested OMGANG's solution with a gap in autonumber at record ID 23,730 out of 30,000+ records. Put a timer on it. 94 - 100 ms, which is acceptable.

Just to answer Patrick's comment:

To implement OMGANG's (slightly modified) solution in a subform, all you have to do is:

Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo Errh
    Dim longID As Long
    longID = FindUnusedKey("tbl_person", "ID")
    If (longID <> 0) Then Me("ID") = longID


Public Function FindUnusedKey(strTable As String, strKeyfield As String)
On Error GoTo Err_FindUnusedKey
 
    Dim rs As DAO.Recordset
    Dim longKeyValPrior As Long, longKeyValNext As Long
    Dim longUseThisKeyVal As Long
    Dim longTimerTicks As Long
    
    longTimerTicks = APIObject().StopWatch.StartTime
    longKeyValPrior = 0
    longKeyValNext = 0
    
    Set rs = CurrentDb.OpenRecordset(strTable, dbOpenSnapshot)
    rs.MoveFirst
    Do Until rs.EOF
        longKeyValNext = rs(strKeyfield)
        If longKeyValNext - longKeyValPrior > 1 Then
            longUseThisKeyVal = longKeyValPrior + 1
            GoTo Exit_FindUnusedKey
        End If
        longKeyValPrior = longKeyValNext
        rs.MoveNext
    Loop
    
Exit_FindUnusedKey:
    FindUnusedKey = longUseThisKeyVal
    Set rs = Nothing
    Debug.Print APIObject().StopWatch.StopTime - longTimerTicks
    Exit Function
    
Err_FindUnusedKey:
    MsgBox Err.Number & ", " & Err.Description, , "Error"
    Resume Exit_FindUnusedKey
    
End Function

Open in new window

0
John Mc HaleForensic Computer Examiner, Analyst/Programmer & Database ArchitectAuthor Commented:
Apart from slight code modification, does just what I want
0
Jeffrey CoachmanMIS LiasonCommented:
fredthered,

Another other concern here is "Related" Records.
If CustID 16 has placed 9 orders, and then you delete them as a customer, then you must also delete the related orders as well (Cascade Deletes)
If you don't, the "New" Customer 16 will inherit these orphaned Records.
:-O

A final concern is "History"
If you delete Cust ID 16, then someone wants the info on this Customer (Legal reasons, Auditing reasons, ...ect)
You have no record of them.
Worse still is that they may be confused with the new Cust 16.
:-O

So I, like the previous Experts, recommend not fretting over "Holes" in the numbering sequence.
Really, ...it should not matter.
;-)

Can I ask why you feel it is imperative to re-use these numbers?

JeffCoachman
0
John Mc HaleForensic Computer Examiner, Analyst/Programmer & Database ArchitectAuthor Commented:
Thanks Jeff,

Relationships already set up for cascading deletes. Only need to pick up gaps in Autonumber as per OMGANG.

If primary record is deleted, then all relevant traces of the record's existence are deleted.

Regards.
0
Jeffrey CoachmanMIS LiasonCommented:
I have no problem with the accepted answer

But the question still remains... Why?

As has been stated before, gaps in the sequence are meaningless.
0
Jeffrey CoachmanMIS LiasonCommented:
So your system has no way of tracking these deleted records?

So you want a cool system to fill in autonumbers, but having a history of the deleted records is worthless to you?

As the "developer" of this system, that decision ultimately rests with you...

Again, my 2c

JeffCoachman
0
John Mc HaleForensic Computer Examiner, Analyst/Programmer & Database ArchitectAuthor Commented:
Hi Jeff,

I am not the original developer, whom has no further interest in the application. I have been asked to maintain this, and as part of the job spec. to recycle unused autonumber keys in primary tables. Also in the job spec, is to implement a full auditing system, whereby the following details are maintain w.r.t. user activity:

Database Login/Logout
Record Insert/Update/Delete
Form Open/Close
Photograph Upload/Retrieval
All Search Activities (giving details of SQL used)

For these auditible transactions, the auditing subsystem will record:

User, Computer, Date/Time, Audit Event, Form/Query used, and PKID of record affected.

The auditing system will therefore show that on a particular date/time a record with specific ID was deleted.

When a new record is inserted, which recycles that ID, then full details of the new record inserted will be audited.

While I can partially understand your argument, I do not share your point of view that gaps in autonumbers are meaningless. With a good auditing system, you can show quite clearly the history of a particular record ID over it's lifetime.

Furthermore, OMGANG does not appear to think that recycling autonumbers is meaningless, since that member came up with a solution, and quite rightly pointed out that inserting records into a table using autonumbers as the PK is very possible.

I'm only working to a job sheet after all.

Regards.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
fredthered,
  Jeff raised excellent points and I can't agree with him more (it is somewhat what I alluded to when I said you didn't want to do this for a number of reasons).
  Even though you have full trace ability, re-using autonumbers just muddy's the water in terms of trace ability.  I realize your working to spec, but I think you owe it to your client to point out that there are a number of reasons not to do this.
 Probably the strongest of which outside of what Jeff mentioned is that they are simply wasting their money by having you code this.  There is no reason in the world to go back and re-use autonumbers.  In fact, I think if they talked to one of their auditors, they'd be told NOT to re-use them under any circumstance.  I have not met an auditor yet that likes to see an assigned ID re-used.
To me, this is a prime opportunity to earn some significant brownie points with your client and possibly earn future business.
And last, just because something can be done doesn't mean you should.
FWIW,
JimD.
0
Jeffrey CoachmanMIS LiasonCommented:
1. <I do not share your point of view that gaps in autonumbers are meaningless.>
Then what meaning do they have beyond uniquely identifying a record?

2. Again, I have no issue with omgang's post.
He is a top Expert here and he has earned my respect and admiration.

However he did not explictly say that the Autonumber was not meaningless.
His exact quote was:
<Not disagreeing with your argument that it defeats the purpose of the autonumber field and is unnecessary but it is possible and does what FredTheRed asks.
OM Gang>

So he is absolutely correct in that he is answering you question directly.
;-)

This is why my post is clearly labeled as "My 2c" and Jim's is clearly labeled: "FWIW".

;-)

Again, as the "developer" of this system, that decision ultimately rests with you...

;-)

JeffCoachman
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.