Reset autonumber field and delete records?

[clebergyn]

I have a table with relationship with another table. Is there a way to reset the autonumber field and delete data without disrupting relationships?

[ItsMe]

In general, deleting data can be disruptive. What is resetting the Autonumber going to do for you?

[clebergyn]

It is a friend who is in need.
I know that when you create a new table equal to another, the autonumber field is zero, however the relationship crumbles

[ItsMe]

If you have a copy of your table, you can import those records into an existing table of the same structure (fields). The trick is not to import records that will violate the No Duplicates rule for the Autonumber field.

You can create a copy of your original table and not include data, just copy the structure of the table only. Or if you have a copy of the table and it includes temporary records, you can select all of the records and then delete them. The objective is to have no records in the table copy. This will be your table that you can import records into. You can reset the Autonumber to zero by performing a compact and repair when there are no records.

After the table's Autonumber is reset to zero, you can choose the autonumber start sequence by importing a long integer into that field (Autonumber field). You can use an action query to append a single record into your table. If this single record you import into your table has a value of '1999' and that value is appended to the Autonumber field, the next record created will begin with 2000. You can delete the single imported record. As long as you do not perform a compact and repair, the next record created will be 2000.

[ssanfu]

I think you are wasting your time worrying what the autonumber number is or trying to reset it.

Autonumbers fields are not guaranteed to be sequential, nor are they guaranteed to be positive. They are only meant to be unique.
Autonumbers are NOT meant to have any real world meaning. I've seen where people use autonumbers for Patient IDs or Employee IDs and have the autonumber field visible on a form. This is a very bad practice.

See:
http://www.utteraccess.com/wiki/index.php/Autonumbers
http://www.fmsinc.com/free/newtips/primarykey.asp

[ItsMe]

I am not positive what the OP needs. My impression is they need to correct something with their table's Primary Key.

[orange]

I also think we need to hear from the OP. What data does he/she have? How important is it? How many records are involved? Since this data is related to another table, resetting the autonumber will break the existing relationships.

[clebergyn]

OK, I have an idea about this, thanks! thank you all!