Pasting data with autonumber

[97fleminglja]

Hi, I have a table holding data with each record allocated an Autonumber data type. I accidentally deleted some of these records, although they are still present in another table. How do i paste them from this other table into my current table, allowing them to keep their original autonumbers instead of just adding them to the end?

(eg - I have records with Autonumbers 1,2,3,4,5 and after deleting 3 and 4 i have 1,2,5.
I need to paste records 3 and 4 (from other table) so as they again become 3 and 4 and not 6 and 7)

Thanks.

 

[boblarson]

Sorry, but you can't. That's why you should NEVER rely on autonumbers as being in sequence, which frequently they are not. Autonumbers only guarantee a UNIQUE number, not consecutive.

 

[Banaticus]

Make a copy of your database before trying this . . .

Go into design view and change the primary key from autonumber to integer. Copy past the relevant records in and manually enter their primary key numbers. Sort the table by number.

(So, the table will look like this:
1, 2, 5
1, 2, 5, 3, 4 -- after you copy/paste the new records in
1, 2, 3, 4, 5 -- after you sort by the number)

Then, go into design view and change the primary key from an integer to an autonumber.

I hope this works!

 

[muse]

Was also looking for a solution to this problem.... proposed solution doesn't work since you can't change it back to autonumber after.

The workaround that I found works best is to import the data you are wanting to copy from, then use an append query to paste the data. Access will also paste/append the autonumber field values properly. voila!

 

[gemma-the-husky]

You can do this.

You can paste/insert an autonumber value in a query.

Of course if the number has been re-used, which is unlikely then it will fail, but the principle is OK.


Also, if you applied relational integrity (and not permitted cascading deletes), then you wouldn't have been able to delete autonumbers that had already been used.


(and on review, I realise this is an old thread, but pleased to see the previous poster @muse found a solution)

 

[InFlight]

Try This it worked for me. It added a row with the numbers i wanted.
You must compact database each time you do it.
add a button on a form and enter this in the on click.
You can use it to rest the Auto Number eg to 999

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim i, x As Variant

Set db = CurrentDb
Set rst = db.OpenRecordset("tbl_Table1")
i = 0 'First Field, for the 4th field enter 3
x = 1000
rst.AddNew
rst.Fields(i) = x
rst.Update
rst.Bookmark = rst.LastModified

rst.Close
Set rst = Nothing

for more than 1 field eg

rst.AddNew
rst.Field (0) = 9
rst.field (3) = 2
rst.Update
rst.Bookmark = rst.LastModified

 

[Pat Hartman]

As Dave said, the correct (as well as simple) way to do this is with an append query. Simply select rows from the backup table (including the autonumber column) and append them to the current table. SIMPLE. No code required.

 

[gsrajan]

Thank you everyone. I will backup tables and sue the append query. Thanks again for your time.