 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Database Design / January 2005Autonums - corrupt/very high
1. Does anyone know why/how an autonum field in a table in Access (2000) can jump from 837 to 258978675? If I do an (SQL)insert, then a DMAX, I get 258978675 (or whatever is the next (high) #). If I do a manual insert, then 838 is allocated as the autonum field. On a different platform the next number is always 258978675, regardless of the insert method? It appears the autonumbering mechanism is running two sequences? 2. Is there any way of replicating the table, and ensuring the next number is 838. The copy process seems to inherit the high sequence. (I tried the append query and it didn't work) thanks very much - in advance. Hi NJS, Autonumbers aren't meant to be sequential, just unique. If you insist on trying to get them to conform to some human-meaningful sequence, you're setting yourself up for some major headaches down the road...should you delete a record, or replicate the database, the autonumbers will go out of whack. Having said that, you can either delete all records with the weird autonumbers, compact the db, then re-append the records; or change the autonumber field to a Number (Integer) field, use an update query to renumber the records; then change the field back to an autonumber (if it's the table's pk, you'll have to turn that "off" before trying this). And of course, related records in other tables will have to be changed, too. Good luck, LeAnne > 1. Does anyone know why/how an autonum field in a table in Access (2000) can > jump from 837 to 258978675? If I do an (SQL)insert, then a DMAX, I get [quoted text clipped - 7 lines] > append query and it didn't work) > thanks very much - in advance. Thanks Leanne, Unfortunately Autonumbers ARE sequential (unless specified random or sync). Once one has populated a (long) field it is not possible to change it to autonum. I am unable to delete the 'weird' fields - error = "Key not found" The update method does not work/help - there are two numbering sequences in effect: one from 838 upwards and one from 258978675. Even if it was permissable, I cannot copy the existing data to a new table and then set the column to autonum because there are missing numbers. Norman. > Hi NJS, > [quoted text clipped - 24 lines] > > append query and it didn't work) > > thanks very much - in advance. > Thanks Leanne, > Unfortunately Autonumbers ARE sequential (unless specified random or sync). Sorry, this is incorrect. Autonumbers will ALWAYS have gaps. They may START sequentially when you first create them. But if you were to delete any record (say, #458), then the numbers would no longer be sequential. Access will not renumber the remaining records from 459 onward to maintain the sequence (nor should it, as it would then have to perpetuate the change in all related records). The same goes if more than one record is deleted. And should you delete a large chunk of records from a table and/or append new records without first doing a Compact/Repair, Access will not necessarily begin the next new record with the next number in sequence. Again, Autonumbers are NOT meant to be sequential. Access doesn't care if one record is number 28, and the next one is -72.5643210E3 (which can happen if you replicate!)...they're still uniquely identified, and that's all that matters to Access. > Once one has populated a (long) field it is not possible to change it to > autonum. Whoops! You're correct. You can change an autonumber to Long Int, but not the other way around. > I am unable to delete the 'weird' fields - error = "Key not found" Huh? Just open the table in Datasheet View, highlight the rows you want to delete, then click the Delete toolbar button (red "X"). Again, you MUST Compact/Repair before attempting to re-append the records. > The update method does not work/help - there are two numbering sequences in > effect: one from 838 upwards and one from 258978675. You lost me here. > Even if it was permissable, I cannot copy the existing data to a new table > and then set the column to autonum because there are missing numbers See above. LeAnne >>Hi NJS, >> [quoted text clipped - 24 lines] >>>append query and it didn't work) >>>thanks very much - in advance. Leanne, By default Autonums ARE issued sequentially('Increment'), unless you specify 'random' or are synchronsing. I know that you can delete records, leaving gaps. Access WILL begin the next new record with the next (unused) number in sequence (unless you have deleted some). My problems are much more complex than where we are dwelling. I need to know how to recreate the table, with the next autonum immediately after 837 (It now issues 258978675 because it is inheriting the high sequence). The append method does not work in this case. > > Thanks Leanne, > > Unfortunately Autonumbers ARE sequential (unless specified random or sync). [quoted text clipped - 65 lines] > >>>append query and it didn't work) > >>>thanks very much - in advance. Hi, I've read the previous thread on your message. You need to reset the count, To do that you have to recreate the ID field which can't be done in a table that has data, so we recreate the entire table. 0) Compact and repair the database. 1) Copy and paste the TableOld (with a different name, TableNew) and choose structure only when prompted. Check that the ID on the new table is Increment and not Random. The AutoNumber is reset to 1 2) Run an append query from TableOld to TableNew, excluding the record with ID 258978675 3) Append the record with ID 258978675, changing it's ID to 837 4) Drop TableOld and rename TableNew to TableOld. 5) Update fields in related tables that reference ID 258978675 to 837 6) Reset relationships. HTH, Graeme. Thanks Graeme & Leanne. This worked great (I will have to manually enter the 'high' data (due to autonum) but that is no problem. I believe my prior attempts did not work because I copied data as well, not just structure. Do you have any idea what causes this state? cheers. Norman. > Hi, I've read the previous thread on your message. > [quoted text clipped - 14 lines] > > HTH, Graeme. >Do you have any idea what causes this state? An Append query will very often leave a gap. I *speculate*, without much evidence, that Access estimates the number of records which will be appended, ignoring any criteria on the append query; sets the "next autonumber" value accordingly; and appends the records which actually meet the criteria. If Access guesses you'll be appending 50250 records and you actually append only 50, you'll get a 50200 number gap. John W. Vinson[MVP] Sounds feasible, especailly with multi-user and possible bad comms. - very odd. > >Do you have any idea what causes this state? > [quoted text clipped - 6 lines] > > John W. Vinson[MVP] |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.