View Full Version : Autonumber in Access 2000

01-17-2003, 09:56 PM
Does anyone know how to reset the AutoNumber feature in Access 2000 (or 2002) back to 1?

Please replycomputerguyz@alltel.net (http://mailto:computerguyz@alltel.net)

01-18-2003, 01:12 AM
You have to delete the autonumber field, and then insert it again. However, if you have related tables that depend on this number, it will of course have no more "id" to relate them to.

01-18-2003, 03:13 PM
Thank you, I will try that.

01-18-2003, 11:50 PM
I needed something that kept a list like this:
And I needed eachone to have the number that comes after the one above, and before the one below. This was give each one it's own number that was in order, no matter if I deleted a record or not. So what I did, was wrote up some new code for deleting.

I kept the autonumber ID field, but for the Order field I had it all numbered, 1,2,3,4,5,6,...
I wrote up a deletion script that went through the list, and deleted the selected field by ID number chosen. Then it updated the Order field to make sure it stayed 1,2,3,4,5,6,... (this method was slow, and I had a few bugs)

I needed it for something specific, but then I stoped using it, and deleted the script. I then again, needed the idea of numbering for something different, and I just used a counter until a selected number. IntNumber = IntNumber + 1

01-20-2003, 07:44 AM

It is Really not suggested to kill an "ID" field. In many cases the field is used for relating one table to another or replication. If I have to set a specific display order for returned values. I create a separate DisplayOrder or ROWID field.

After each Insert or Delete, I run a seperate looping Update function to reset the diplay order.

This is an example of the UpdateRowNum() function. This is a transactional function that will try three time to update the selected records before failing

Sub DeleteRecord()
' Delete code

bUpdateSuccess = UpdateRowNum(nElemId)

If Not (bUpdateSuccess) Then
'Got to Error handler Code
End If
End Sub
Function UpdateRowNum(nElmId)
Dim oConn,oRs,sSQL,nRowNum,nRetry

set oRs = Server.CreateObject("ADODB.RECORDSET")
set oConn = Server.createobject("ADODB.CONNECTION")

sSQL = "SELECT AutoNumFld, ROWID " & _
"FROM Elements " & _
"WHERE ElementID = " & nElmId & _
" ORDER BY ElemName ASC"

oRs.Open sSQL,oConn,2,1
nRowNum = 1
' Loop Thru Record and Update RowID
Do While Now oRs.Eof
sSQL = "Update myTable " & _
"Set ROWID = " & nRowNum & _
" WHERE AutoNumFld = " & oRs.Fields("AutoNumFld")
If oConn.Errors.Count <> 0 Then
' Retry Update 3 times
nRetry = 0
Do While nRetry < 3
' Retry Update
If oConn.Errors.Count <> 0 Then
Exit Do
End If
If nRetry=3 Then
' Cleanup
Set oRs = Nothing
Set oConn = Nothing

'Return Failed Update's Record IndexID
Exit Function

End If
End if
nRowNum = nRowNum + 1

' Cleanup
Set oRs = Nothing
Set oConn = Nothing

'Return Success!
End Function

Hope this helps :cool:

01-20-2003, 08:19 PM
I wasn't trying ot say to get rid of the ID numbers, but in my method what I did was to use the Order ID numbers to list them out, in the way I needed. The ID field could stay with it's 1,2,4,5,7,9,10,14 style.

My method had no need to link any tables together.

01-20-2003, 11:00 PM

That is what the code I sent does. After the insert ot Delete a record the script walks thru a new recordset and renumbers a Display/Row order field sequencially. I added the transactional update code so that if the update fails I'll be notified and can "fix" the database manually if necessary.

01-21-2003, 12:05 AM
You can't have the field that has numbers like that to be autonumber, the database doesnt allow you to edit those. So what you just said is basically what I had made before. but for the field "Order".

01-21-2003, 02:56 AM
No I said to create an "ID" field for data integrity and if needed later relationships between tables. That is a good practice requardless of the table size.
Then create a separate Display/ROWID field as shown below.
In this senarior I am using a single Integer data type to save space as the lists will not be long. If you are expecting 10s of thousands of records, then use a "Long Integer" data type.

The supplied code sample safely updates the highlighted field.


Sorry for the confusion.

01-21-2003, 03:09 AM
I've never had a need to display the presumed "list number?" in any scenario I've encountered (yet), since I haven't seen any reason that kind of "primary key" information is at all important to the user. After all, isn't that field only for the programmer to tie different tables together? ;)

If the primary key is the important link, I'll use that as some variable to pass to a querystring, database, etc.


Do While NOT rs.EOF

i = i + 1


While displaying records... since you have to loop through them anyway... am I totally off base here? :D

As far as the user may NOT know, the records might be numbered 123, 234, 235, 456

What does that matter as long as your database works? :confused:

01-21-2003, 05:30 AM
Hi Whammy,

Basically you are right. Most people use ORDER BY to order the display of lists and leave it at that.

But Morgoth isn't "displaying" the "List number". He is storing the order in which the list items are to be displayed to the customer. And wants to manipulate the presented list order.

Use of a "Display Order" field allows fine control of list item presentation.

I develop/work with Enterprise level Call Handling and Data Wharehousing DBs. The example table I used is basically a simplified version of a list element (garbage) table for storing a wide array of list item data.

Since the the table is self-referencing, it is possible to have the same columns contain both parent and child objects for a number of pick lists.

Several of the call handling database Apps I've work with contain multilevel heirarchal pick lists. The list items are not ordered alphanumerically, but by priority levels set by the customer. Thus the need for the "DisplayOrder" Field. Also, since no record is ever "deleted" in a call handling database, an active status field is usually added. If an element is inactivated/disabled, the "DisplayOrder" field is once again reset accordingly. Oh how I love when I get these change orders..... ;)

What does that matter as long as your database works? :confused:

This is where perfomance tuning starts to really kick in.
I can write a DB that "works", but kills the Db and/or web server when more than 10 people are using the app or website.

Autonumbered "ID" fields have several important functions.
For relationships between data storage tables and source tables, A unique Id is required in the "source" table. Easiest way to accomplish this is by the use of AutoNumber fileds. Also when replicating data between DBs, this ID field is used to track which rows have been replicated.

Primary Keys are fields used by the Index/Query Engines to help identify data within the "Index Catalog". If a Primary Key is not set the engine has to work harder to gather the requested data. As it cannot store the pointers to the data properly.

At times it is more important to design a little more complex table(s), stored procedures/Access Queries or indexes in the DB to reduces the workload on the webserver.

Sorry if it sounds like I'm flaming. I'm not. :cool:

01-21-2003, 07:15 AM
Blah blah blah blah blah....

I needed it before for listing in order with a number for some reason, but I don't need it anymore, and that is why it has been deleted for a year or so.

Whammy, yes loops work too, and that's what I use.

Note: aCcodeMonkey likes to type long posts.

01-21-2003, 08:38 AM
this is not a very usefull post, so stop reading and start coding if you're a bit smart.

stop being so lazy. When you have a bad db-design or you stumble onto a designproblem while building your app --> change the db-design and update all related code. (It usually doesn't take days) That is the only sensible sollution. I've read about a dozen treads that state they have a bad design but ...
in the long run, it'll cause you serious problems, other coders will experience even more problems and you'll end up learning nothing about good db-design and efficient coding.

01-21-2003, 02:13 PM
You could learn how to not make that mistake again!

01-21-2003, 02:44 PM
Thank you all for your help and i will try several of the suggestions.