Autonumber Weirdness (1 Viewer)

[hi there]

Hi everyone,

i'm in the process of uploading some data from an Excel spreadsheet into an existing table and i'm getting some screwy things in my PK field. i'm using an autonumber field for my PK and i can get the data to import from Excel but instead of insert the record and creating the next sequential autonumber value and makes a radical jump. for example prior to my import i was at 927 then the next record entered went to 47517249 from there on they sequentially increased (47517250, 47517251,..etc) however what happened after 927. can anyone help me with this.

some background i have tried imported the data in the following ways each giving the same results:

1. bulk import from excel table to existing table
2. link to excel table and then run an append query
3. bulk import from excel table to new table then run an append query

many thanks for any help or suggestions.

 

[Pat Hartman]

Autonumbers skip for any number of reasons but this seems like a large jump. If you have no related tables, you can recreate the current table with new values in the autonumber. Create a new empty table. Create a select query that selects all the rows and orders by the current autonumber. Deselect the current autonumber so it doesn't show in the recordset. Then change the query to an append query and append the data to the new table.

 

[hi there]

hi pat,

thanks for the response. i was thinking about restarting the autonumber. this table has a one-to-many with another table (child). as an alternative to the append query, could i delete the relationship, delete the PK field in the table, and create a new PK autonumber field? are these methods comparable? if so, which method is preferred?

many thanks for your help.

 

[Pat Hartman]

You can't just reassign the primary key when you have relationships. I would do the folllowing:
------- Make a backup before starting ------------------
1. Remove the relationship. You do this by deleting the relationship line itself, not the tables.
2. Make sure that the name auto correct options are all unchecked.
3. Change the definition of the primary key from autonumber to number - long integer.
4. Rename the current key to OldKeyName.
5. Save the table definition but don't close it.
6. Add a new autonumber column and give it the old name.
7. Add a new column defined as long integer to the many-side table.
8. Rename the original foreign key field to OLDforeignkeyname.
9. Save the table.
10. Name the new field to the original name.
11. Create a query that joins the two tables on the OLD fields.
12. Change the query type to update. Update the new foreign key field on the many-side table with the new primary key value from the one-side table.
13. Create a new query that joins the two tables on the new fields and verify that the keys have been created correctly.
14. Delete the OLD fields from each table.
15. Reestablish the relationships and enforce RI.