Reset Autonumber and keep sequence after Deleteing record

[Mohsin Malik]

Hello,

I have a Microsoft Access database with SQL Server backend with 10000 records in my table, I have a autonumber field and in my database the purpose of the autonumber field is just to keep the record sequence (not to treat as a unique identifier) and I don't want to use a number field to manually enter the sequence. Whenever the record is deleted or the user won't save the record, it put gaps in the record sequence, Is there any way to refresh the autonumbers automatically "After delete confirm" event or "After Insert/update" event, please advise.

Many Thanks
Mohsin

 

[pr2-eugin]

You need to understand what Autonumbers type is all about :

http://www.utteraccess.com/wiki/index.php/Autonumbers
http://access.mvps.org/access/general/gen0025.htm

 

[namliam]

Only way to reset autonumbers, is to compact the database or recreate the column.

If you just want the order, what would the exact sequence matter?
1,2,3,4 works just as well as 1,5,18,65

 

[Mohsin Malik]

Thank you for the reply, For example if I delete ID record # 3, I want the sequence to always be 1,2,3,4,5,6,7,8,9,10 not 1,2,4,5,6,7,8,9,10. Please let me know if there is a way to accomplish this. I am not sure recreating the column with SQL Server linked database through VBA? or compacting and repairing the database will not remove the gaps in sequence.

Many Thanks
Mohsin

 

[pr2-eugin]

It is not a guarantee "Compact & Repair" will reset/reseed the counter. I have not myself seen this happen, but I have read it might not set it back in sequence.

 

[Chrisopia]

autonumber should never be used for personal reasons, its for access only. Its not editable and it can't be relied upon.

You MUST use a number field if you require to keep a sequence.
It's fairly straight forward to implement, with a query or VBA:

'adding a new record
TheID = DMax(TheID,TheTable) + 1

(lookup Dmax for more info on how to use it)

It will always look up the last value and add 1.
But if you delete record #3, DMax will still ignore the record deleted and look for the highest number.

If a record is deleted, you must use an update query to reassign the IDs of the other records, so if record #3 is deleted, record #4 becomes #3, like this:

'Within the delete command
Dim theSQL As String
Dim TheDeletedID As Long
Dim RecordCount As Long
Dim theID As Long

TheDeletedID = Me.theID '#3
RecordCount = DCount("theID","theTable","theID > TheDeletedID")

for i = (TheDeletedID + 1) to RecordCount 'every ID after the deleted record

theSQL = "UPDATE theTable SET theID=theID-1 WHERE theID=" & i  & ";"

DoCmd.SetWarnings false
docmd.runSQL theSQL
doCmd.SetWarnings true

Next i

(untested)

That should give you a start.

Though, even after that, why would you need to reassign the numbers to all the records just because #3 is deleted??

 

[namliam]

Autonumber as said shouldnt be used for anything really meaningfull. If you want it just for the order.... its fine, but then you should also not matter about 1,2,3,4,5 or 1,3,5,6,9 just for ordering is just for ordering.

If you for some reason NEED 1,2,3,4,5 autonumber isnt suitable for you. and the DMAX solution might be your way to go... However if 1,2,3,4,5 is created and then 3 is deleted, then what? An order is but an order who would care about the actual number behind the order?

 

[gemma-the-husky]

you can't ever have what you want though.

what you should probably do is not delete things. just have a deleted flag - so if you "delete" item 3, you mark it as deleted, but the record 3 is still there.

The last thing you want to do is renumber every record to reflect a deleted record. It is a lot of work, and can't really be useful.

 

[Rx_]

Using SQL Server linked tables, I agree with Gemma-the-husky.
All of my tables have a column named "Activity" - the default is "A" for Active.
A delete just changes the A to I (inactive). Actually, anythining but A is inactive.
For example, if a table is used for a list box, and a record is no longer needed to show up in the list box to asign, just change the A to an I and use that filter for the list box to only show "A".
Very important: for historical records - if that record was actually deleted - then the historical record has nothing to look-up. Reports stop working for the same reason.

So, simply changing the A to an I doesn't allow that record to be used going forward (e.g. in a list box) but it allows those historical records to show the past.

Deleting and reassigning autocounter will have unintended results or added maintenance overhead.

 

[jdraw]

I agree totally with Dave and Rx. But I'm curious why do you need the sequence?

 

[MarvinM]

Mohsin,

I have to agree with almost everyone else that monkeying around with the field is a bad idea. However, I must say that what you are asking is possible to accomplish by one means or another.

Please note that the others have given invaluable advice. The most poignant is about not actually deleting a record, but rather flagging it as no longer active. If you want to go a step further, you could even timestamp the record for an Effective Date when the record was "deleted".

Back to the original question, let's say that you do have records numbered 1,2,4,5,6,7,8,9,10, as in your second post. And you want to call the 3rd record 3 instead of 4, and the 4th record 4, and so on. Here is a way to go about it.

First of all, get away from the autonumber thing—that will not work out for you. You will need another numeric field. Call it something like RowNumber. (Not ID or RecordNumber because this number will be constantly changing.) With 10,000 records, I'd pick a Long Integer. (Integer only goes up to 32,767.)

Then, every time a record is added, you increment the value with a DMax(RowNumber)+1. DCount(RowNumber)+1 should give you the same answer, but I wouldn't take the chance.

Every time a record is deleted, you would need to renumber the whole table. Actually, only from that record forward; however, it will be easier to run a function to renumber the whole table. For an idea of how to do that, go to this other thread: http://www.access-programmers.co.uk/forums/showthread.php?p=832052

HTH

_________________
Regards,
Marvin M
Windows 7 Professional, MS Access 2007/2010
Windows 8 Professional, MS Access 2013
---------------------------------------------------------------------------------------------
If my post has helped you, please click the scales or click the 'Thumbs up'. Thanks!
---------------------------------------------------------------------------------------------

 

[safam]

autonumber should never be used for personal reasons, its for access only. Its not editable and it can't be relied upon.

You MUST use a number field if you require to keep a sequence.
It's fairly straight forward to implement, with a query or VBA:

'adding a new record
TheID = DMax(TheID,TheTable) + 1

sir, please help me where am i supposed to implement this code."TheID = DMax(TheID,TheTable) + 1" only for a new record.

 

[Steve R.]

... I have a autonumber field and in my database the purpose of the autonumber field is just to keep the record sequence (not to treat as a unique identifier) and I don't want to use a number field to manually enter the sequence. Whenever the record is deleted or the user won't save the record, it put gaps in the record sequence, Is there any way to refresh the autonumbers automatically "After delete confirm" event or "After Insert/update" event, please advise.

I agree totally with Dave and Rx. But I'm curious why do you need the sequence?

As jdraw asked; what is the significance of this autonumber field? Additionally, using the autonumber capability carries the implication that the numbers would be unique. Why are sequence "gaps" of significance? It doesn't change the sort order. See the comments by gemma-the-husky below.

... what you should probably do is not delete things. just have a deleted flag - so if you "delete" item 3, you mark it as deleted, but the record 3 is still there. ... The last thing you want to do is renumber every record to reflect a deleted record. It is a lot of work, and can't really be useful.

Create an integer field in your table labeled something like "TheID" and follow the advice of Chrisopia below. "TheTable" refers to the name of the table that you are using. The code is executed at the time a new recorded is created. DMin, DMax Functions

autonumber should never be used for personal reasons, its for access only. Its not editable and it can't be relied upon.

You MUST use a number field if you require to keep a sequence.
It's fairly straight forward to implement, with a query or VBA:

'adding a new record
TheID = DMax(TheID,TheTable) + 1

(lookup Dmax for more info on how to use it)

 

[gemma-the-husky]

seriously. if you really need to renumber an autonumber record then you will be skilled enough to know how to do it.

if you aren't skilled enough to know how to do it, then you need to accept advice that you ought not be doing this. There is absolutely no reason to renumber autonumber values. 100% no reason.

occasionally, I have decided that I don't like an autonumber sequence for a particular field, and have changed the autonumber to just be a manually controlled number, instead. for example, for an order status where I decide that I want to be able to control specific values.