Solved

Recycling unused Autonumber entries in MS Access Table

Posted on 2009-07-13
20
904 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
  • 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 57
ID: 24841565
No reason and you really don't want to for a number of reasons.  Just keep letting it go up.
JimD.
0
 
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 57
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
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 57
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

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

757 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now