If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
AutoNumber Reset
Background:
I have a database I am rewriting in Access 2000 (split DB) that was originally written in VB 3.0 with Access 2.0. The database has about 70 tables and was actually designed very well, completely normalized, very efficient and had autonumber fields for all table primary keys. So I simply imported the tables into Access 2000 for the back end and I am replicating the forms and behavior in an Access 2000 front end. Problem: After working on this for 6 months including having the users do some preliminary testing I have just discovered that one of my tables had reset the next autonumber value to 1. Obviously I couldn't add records to the table because key value 1 already exists. To recover from this I copied the table (structure only) and ran an append query to create all of the records with proper key values. Problem solved!!! Questions: How could this have happened? If this happened to one table can I prevent it from happening to another in the future? I had repaired and compacted the front and back ends a day or so before this problem surfaced. I have never seen a repair and compact cause this problem. Thanks in advance! |
#2
|
|||
|
|||
"Reggie Laffond" wrote in
: So I simply imported the tables into Access 2000 for the back end and I have just discovered that one of my tables had reset the next autonumber value to 1. Questions: How could this have happened? If you import a table _structure_ then the seed will start from scratch. If you imported the table with its data then the seed will fall back to (highest existing autonumber + 1). In effect you are just creating a new table. I had repaired and compacted the front and back ends Compacting a db with one or more empty tables will reset the autonumber in that table or those tables. I don't understand how it matters, though. If you want to start with an empty database, then it's appropriate to let all counters restart. If you want to start with a seeded database, then you will need some method of initialising it, which in turn means that you think very carefully about what data you need to import, in what order, and how. This is a technical procedure called Data Migration. All the best Tim F |
#3
|
|||
|
|||
I might have given too much information and made my question unclear.
Basically I had a table with 939 records in it with the primary key (auto number field) from 0 to 942 (with three missing numbers caused by deleted records). I compacted and repaired the database.. The next day when I tried to add a new record to the table the auto number kept coming up 1 which already was used in the first record in the table. I just want to know if "repair and compact" could reset the auto number seed even though there were 939 record in the table. Or is there some other known bug that could cause this to happen. I just want to prevent it in the future. Thanks! "Tim Ferguson" wrote in message ... "Reggie Laffond" wrote in : So I simply imported the tables into Access 2000 for the back end and I have just discovered that one of my tables had reset the next autonumber value to 1. Questions: How could this have happened? If you import a table _structure_ then the seed will start from scratch. If you imported the table with its data then the seed will fall back to (highest existing autonumber + 1). In effect you are just creating a new table. I had repaired and compacted the front and back ends Compacting a db with one or more empty tables will reset the autonumber in that table or those tables. I don't understand how it matters, though. If you want to start with an empty database, then it's appropriate to let all counters restart. If you want to start with a seeded database, then you will need some method of initialising it, which in turn means that you think very carefully about what data you need to import, in what order, and how. This is a technical procedure called Data Migration. All the best Tim F |
#4
|
|||
|
|||
Updating your Jet file should solve this issue I believe. See:
ACC2000: AutoNumber Field Duplicates Previous Values After You Compact and Repair a Database http://support.microsoft.com/default...b;en-us;257408 -- Jeff Conrad Access Junkie Bend, Oregon "Reggie Laffond" wrote in message ... I might have given too much information and made my question unclear. Basically I had a table with 939 records in it with the primary key (auto number field) from 0 to 942 (with three missing numbers caused by deleted records). I compacted and repaired the database.. The next day when I tried to add a new record to the table the auto number kept coming up 1 which already was used in the first record in the table. I just want to know if "repair and compact" could reset the auto number seed even though there were 939 record in the table. Or is there some other known bug that could cause this to happen. I just want to prevent it in the future. Thanks! "Tim Ferguson" wrote in message ... "Reggie Laffond" wrote in : So I simply imported the tables into Access 2000 for the back end and I have just discovered that one of my tables had reset the next autonumber value to 1. Questions: How could this have happened? If you import a table _structure_ then the seed will start from scratch. If you imported the table with its data then the seed will fall back to (highest existing autonumber + 1). In effect you are just creating a new table. I had repaired and compacted the front and back ends Compacting a db with one or more empty tables will reset the autonumber in that table or those tables. I don't understand how it matters, though. If you want to start with an empty database, then it's appropriate to let all counters restart. If you want to start with a seeded database, then you will need some method of initialising it, which in turn means that you think very carefully about what data you need to import, in what order, and how. This is a technical procedure called Data Migration. All the best Tim F |
#5
|
|||
|
|||
"Reggie Laffond" wrote in news:e$nuFu#AFHA.1296
@TK2MSFTNGP10.phx.gbl: . I compacted and repaired the database.. The next day when I tried to add a new record to the table the auto number kept coming up 1 which already was used in the first record in the table. As Jeff says, this is a bug. Update Access with latest service packs. All the best Tim F |
#6
|
|||
|
|||
Will do. Thanks for the quick reply! Great newsgroup!
"Jeff Conrad" wrote in message ... Updating your Jet file should solve this issue I believe. See: ACC2000: AutoNumber Field Duplicates Previous Values After You Compact and Repair a Database http://support.microsoft.com/default...b;en-us;257408 -- Jeff Conrad Access Junkie Bend, Oregon "Reggie Laffond" wrote in message ... I might have given too much information and made my question unclear. Basically I had a table with 939 records in it with the primary key (auto number field) from 0 to 942 (with three missing numbers caused by deleted records). I compacted and repaired the database.. The next day when I tried to add a new record to the table the auto number kept coming up 1 which already was used in the first record in the table. I just want to know if "repair and compact" could reset the auto number seed even though there were 939 record in the table. Or is there some other known bug that could cause this to happen. I just want to prevent it in the future. Thanks! "Tim Ferguson" wrote in message ... "Reggie Laffond" wrote in : So I simply imported the tables into Access 2000 for the back end and I have just discovered that one of my tables had reset the next autonumber value to 1. Questions: How could this have happened? If you import a table _structure_ then the seed will start from scratch. If you imported the table with its data then the seed will fall back to (highest existing autonumber + 1). In effect you are just creating a new table. I had repaired and compacted the front and back ends Compacting a db with one or more empty tables will reset the autonumber in that table or those tables. I don't understand how it matters, though. If you want to start with an empty database, then it's appropriate to let all counters restart. If you want to start with a seeded database, then you will need some method of initialising it, which in turn means that you think very carefully about what data you need to import, in what order, and how. This is a technical procedure called Data Migration. All the best Tim F |
#7
|
|||
|
|||
Will do. Thanks for the quick reply! Great newsgroup!
"Tim Ferguson" wrote in message ... "Reggie Laffond" wrote in news:e$nuFu#AFHA.1296 @TK2MSFTNGP10.phx.gbl: . I compacted and repaired the database.. The next day when I tried to add a new record to the table the auto number kept coming up 1 which already was used in the first record in the table. As Jeff says, this is a bug. Update Access with latest service packs. All the best Tim F |
#8
|
|||
|
|||
You're welcome, glad to help.
Come back any time. -- Jeff Conrad Access Junkie Bend, Oregon "Reggie Laffond" wrote in message ... Will do. Thanks for the quick reply! Great newsgroup! "Jeff Conrad" wrote in message ... Updating your Jet file should solve this issue I believe. See: ACC2000: AutoNumber Field Duplicates Previous Values After You Compact and Repair a Database http://support.microsoft.com/default...b;en-us;257408 -- Jeff Conrad Access Junkie Bend, Oregon "Reggie Laffond" wrote in message ... I might have given too much information and made my question unclear. Basically I had a table with 939 records in it with the primary key (auto number field) from 0 to 942 (with three missing numbers caused by deleted records). I compacted and repaired the database.. The next day when I tried to add a new record to the table the auto number kept coming up 1 which already was used in the first record in the table. I just want to know if "repair and compact" could reset the auto number seed even though there were 939 record in the table. Or is there some other known bug that could cause this to happen. I just want to prevent it in the future. Thanks! "Tim Ferguson" wrote in message ... "Reggie Laffond" wrote in : So I simply imported the tables into Access 2000 for the back end and I have just discovered that one of my tables had reset the next autonumber value to 1. Questions: How could this have happened? If you import a table _structure_ then the seed will start from scratch. If you imported the table with its data then the seed will fall back to (highest existing autonumber + 1). In effect you are just creating a new table. I had repaired and compacted the front and back ends Compacting a db with one or more empty tables will reset the autonumber in that table or those tables. I don't understand how it matters, though. If you want to start with an empty database, then it's appropriate to let all counters restart. If you want to start with a seeded database, then you will need some method of initialising it, which in turn means that you think very carefully about what data you need to import, in what order, and how. This is a technical procedure called Data Migration. All the best Tim F |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
compacting database doesn't reset autonumber (ver 2000) | JulieD | New Users | 3 | January 24th, 2005 04:25 PM |
Autonumber | Ally H. | General Discussion | 7 | August 27th, 2004 04:51 PM |
Autonumber -Reset to a lower number | Mike | General Discussion | 2 | June 5th, 2004 01:54 AM |
Reset autonumber to zero | joseph | Database Design | 1 | May 31st, 2004 03:51 AM |
reset autonumber value in table | Database Design | 0 | April 29th, 2004 02:11 PM |