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
|
|||
|
|||
Autonum problem
I have an autonum field in table1 that is the relational field to table2. It
is an indexed field in table1 that will not allow duplicates, but is not the primary key. I need to add and delete records from both table from time to time. Recently I ran into a problem when I needed to add several records to table1. The append query kept trying to reuse a deleted autonum and would error out. I had to rebuild the tables to make the process move forward again. Why would it attempt to reuse a number instead of choosing the next highest number? Right now I don't have a lot of records so it wasn't too much of a problem. It will be a problem if it does it again in the future. How can I prevent this error from happening in the future? Thank you, Cathy |
#2
|
|||
|
|||
Autonum problem
Does the append query contain the autonumber field as one of the output
fields? If so, remove that field from the output field list of the append query -- the autonumber field will be assigned a value by the table when a new record is added. -- Ken Snell MS ACCESS MVP "Cathy" wrote in message ... I have an autonum field in table1 that is the relational field to table2. It is an indexed field in table1 that will not allow duplicates, but is not the primary key. I need to add and delete records from both table from time to time. Recently I ran into a problem when I needed to add several records to table1. The append query kept trying to reuse a deleted autonum and would error out. I had to rebuild the tables to make the process move forward again. Why would it attempt to reuse a number instead of choosing the next highest number? Right now I don't have a lot of records so it wasn't too much of a problem. It will be a problem if it does it again in the future. How can I prevent this error from happening in the future? Thank you, Cathy |
#3
|
|||
|
|||
Autonum problem
Ken,
The append query does not have the autonumber field as an output field. I expected to assign a new number. Instead it kept trying to assign an old, deleted number. I've never had an autonum behave this way before... have you? Thnks "Ken Snell (MVP)" wrote: Does the append query contain the autonumber field as one of the output fields? If so, remove that field from the output field list of the append query -- the autonumber field will be assigned a value by the table when a new record is added. -- Ken Snell MS ACCESS MVP "Cathy" wrote in message ... I have an autonum field in table1 that is the relational field to table2. It is an indexed field in table1 that will not allow duplicates, but is not the primary key. I need to add and delete records from both table from time to time. Recently I ran into a problem when I needed to add several records to table1. The append query kept trying to reuse a deleted autonum and would error out. I had to rebuild the tables to make the process move forward again. Why would it attempt to reuse a number instead of choosing the next highest number? Right now I don't have a lot of records so it wasn't too much of a problem. It will be a problem if it does it again in the future. How can I prevent this error from happening in the future? Thank you, Cathy |
#4
|
|||
|
|||
Autonum problem
Reuse of an "obsolete" number could indicate a corruption problem in the
database. Make a backup copy of the database, then run compact and repair on the database. If that does not work, create a new database file and import all the objects and data into that new file from the old file. Another way that you might get this problem is if you append a record by a query and that query has the autonumber field in the field list. ACCESS / Jet will let you assign numbers to an autonumber field by an append query, and this resets the seed of the autonumber field, which means it could then start using old numbers if your query appended an old value into that field. -- Ken Snell MS ACCESS MVP "Cathy" wrote in message news Ken, The append query does not have the autonumber field as an output field. I expected to assign a new number. Instead it kept trying to assign an old, deleted number. I've never had an autonum behave this way before... have you? Thnks "Ken Snell (MVP)" wrote: Does the append query contain the autonumber field as one of the output fields? If so, remove that field from the output field list of the append query -- the autonumber field will be assigned a value by the table when a new record is added. -- Ken Snell MS ACCESS MVP "Cathy" wrote in message ... I have an autonum field in table1 that is the relational field to table2. It is an indexed field in table1 that will not allow duplicates, but is not the primary key. I need to add and delete records from both table from time to time. Recently I ran into a problem when I needed to add several records to table1. The append query kept trying to reuse a deleted autonum and would error out. I had to rebuild the tables to make the process move forward again. Why would it attempt to reuse a number instead of choosing the next highest number? Right now I don't have a lot of records so it wasn't too much of a problem. It will be a problem if it does it again in the future. How can I prevent this error from happening in the future? Thank you, Cathy |
Thread Tools | |
Display Modes | |
|
|