Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 15 of 15
  1. #1
    New Coder
    Join Date
    Jan 2003
    Location
    Georgia
    Posts
    50
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Unhappy Autonumber in Access 2000

    Does anyone know how to reset the AutoNumber feature in Access 2000 (or 2002) back to 1?

    Please replycomputerguyz@alltel.net

  • #2
    Senior Coder
    Join Date
    Jun 2002
    Location
    41° 8' 52" N -95° 53' 31" W
    Posts
    3,660
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.
    Former ASP Forum Moderator - I'm back!

    If you can teach yourself how to learn, you can learn anything. ;)

  • #3
    New Coder
    Join Date
    Jan 2003
    Location
    Georgia
    Posts
    50
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you, I will try that.

  • #4
    Senior Coder Morgoth's Avatar
    Join Date
    Jun 2002
    Location
    Ontario, Canada Remaining Brain Cells: 6
    Posts
    1,402
    Thanks
    2
    Thanked 1 Time in 1 Post
    I needed something that kept a list like this:
    1,2,3,4,5,6,...
    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

  • #5
    New Coder
    Join Date
    Sep 2002
    Posts
    82
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Morgoth,

    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

    Code:
    Sub DeleteRecord()
    ' Delete code 
    
    	bUpdateSuccess = UpdateRowNum(nElemId)
    
    	If Not (bUpdateSuccess) Then
    		'Got to Error handler Code
    		DisplayErr(bUpdateSuccess,23)
    	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")
    	oConn.ConnectionString=Application("DbWriter")
    	oConn.open
    	
    	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")
    		oConn.BeginTrans
    		If oConn.Errors.Count <> 0 Then
    			oConn.Rollbacktrans
    			' Retry Update 3 times
    			nRetry = 0
    			Do While nRetry < 3
    				' Retry Update
    				oConn.BeginTrans
    				oConn.Execute(sSQL)
    				If oConn.Errors.Count <> 0 Then
    					oConn.RollbackTrans
    					nRetry=nRetry+1
    				Else
    					Exit Do
    				End If
    			Loop
    			If nRetry=3 Then
    				' Cleanup
    				oRs.Close
    				Set oRs = Nothing
    				oConn.Close
    				Set oConn = Nothing
    
    				'Return Failed Update's Record IndexID
    				Return(oRs.Fields("AutoNumFld"))
    				Exit Function
    
    			End If
    		Else
    			oConn.CommitTrans
    		End if
    		oRs.MoveNext
    		nRowNum = nRowNum + 1
    	Loop
    	
    	' Cleanup
    	oRs.Close
    	Set oRs = Nothing
    	oConn.Close
    	Set oConn = Nothing
    	
    	'Return Success!
    	Return(True)
    End Function
    Hope this helps
    a credit card for a newbie from Amex: The Amex Blue card would like to start developing a credit history.

  • #6
    Senior Coder Morgoth's Avatar
    Join Date
    Jun 2002
    Location
    Ontario, Canada Remaining Brain Cells: 6
    Posts
    1,402
    Thanks
    2
    Thanked 1 Time in 1 Post
    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.

  • #7
    New Coder
    Join Date
    Sep 2002
    Posts
    82
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Alex,

    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.
    a credit card for a newbie from Amex: The Amex Blue card would like to start developing a credit history.

  • #8
    Senior Coder Morgoth's Avatar
    Join Date
    Jun 2002
    Location
    Ontario, Canada Remaining Brain Cells: 6
    Posts
    1,402
    Thanks
    2
    Thanked 1 Time in 1 Post
    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".

  • #9
    New Coder
    Join Date
    Sep 2002
    Posts
    82
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.
    a credit card for a newbie from Amex: The Amex Blue card would like to start developing a credit history.

  • #10
    Senior Coder
    Join Date
    Jun 2002
    Location
    41° 8' 52" N -95° 53' 31" W
    Posts
    3,660
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

    Otherwise:

    Do While NOT rs.EOF

    i = i + 1

    Loop

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

    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?
    Last edited by whammy; 01-21-2003 at 03:17 AM.
    Former ASP Forum Moderator - I'm back!

    If you can teach yourself how to learn, you can learn anything. ;)

  • #11
    New Coder
    Join Date
    Sep 2002
    Posts
    82
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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?
    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.
    a credit card for a newbie from Amex: The Amex Blue card would like to start developing a credit history.

  • #12
    Senior Coder Morgoth's Avatar
    Join Date
    Jun 2002
    Location
    Ontario, Canada Remaining Brain Cells: 6
    Posts
    1,402
    Thanks
    2
    Thanked 1 Time in 1 Post
    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.

  • #13
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    this is not a very usefull post, so stop reading and start coding if you're a bit smart.

    else:
    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.

  • #14
    Senior Coder Morgoth's Avatar
    Join Date
    Jun 2002
    Location
    Ontario, Canada Remaining Brain Cells: 6
    Posts
    1,402
    Thanks
    2
    Thanked 1 Time in 1 Post
    You could learn how to not make that mistake again!

  • #15
    New Coder
    Join Date
    Jan 2003
    Location
    Georgia
    Posts
    50
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Thank you

    Thank you all for your help and i will try several of the suggestions.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •