Solved

Recycling unused Autonumber entries in MS Access Table

Posted on 2009-07-13
20
974 Views
Last Modified: 2013-11-29
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.
0
Comment
Question by:John Mc Hale
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 4
  • +2
20 Comments
 
LVL 28

Expert Comment

by:omgang
ID: 24841551
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
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 24841560
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
 
LVL 58
ID: 24841565
No reason and you really don't want to for a number of reasons.  Just keep letting it go up.
JimD.
0
A new era in Cloud training has arrived.

A day that will go down in Cloud history.. But are you ready for it? Will you accept this Cloud challenge?

 
LVL 28

Expert Comment

by:omgang
ID: 24841601
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
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 24841773
omgang,

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

Regards,

Patrick
0
 
LVL 28

Expert Comment

by:omgang
ID: 24841844
Good point.  Yes, they should be decalared as the same type as the table field (autonumber = long).
OM Gang
0
 
LVL 13

Author Comment

by:John Mc Hale
ID: 24841927
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
 
LVL 58
ID: 24841961
<<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
 
LVL 28

Expert Comment

by:omgang
ID: 24841969
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
 
LVL 28

Expert Comment

by:omgang
ID: 24842221
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
 
LVL 28

Accepted Solution

by:
omgang earned 500 total points
ID: 24842263
Pic of tbl_Person with gap after record 30005.
OM Gang
tbl-Person.jpg
0
 
LVL 13

Author Comment

by:John Mc Hale
ID: 24842356
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
 
LVL 13

Author Closing Comment

by:John Mc Hale
ID: 31602904
Apart from slight code modification, does just what I want
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24842474
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
 
LVL 13

Author Comment

by:John Mc Hale
ID: 24842675
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24847106
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24847129
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
 
LVL 13

Author Comment

by:John Mc Hale
ID: 24847640
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
 
LVL 58
ID: 24848381
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24848667
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

Featured Post

Do you have a plan for Continuity?

It's inevitable. People leave organizations creating a gap in your service. That's where Percona comes in.

See how Pepper.com relies on Percona to:
-Manage their database
-Guarantee data safety and protection
-Provide database expertise that is available for any situation

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question