Pasting data with autonumber (1 Viewer)

97fleminglja

New member
Local time
Today, 00:28
Joined
Feb 13, 2007
Messages
1
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

Smeghead
Local time
Yesterday, 16:28
Joined
Jan 12, 2001
Messages
32,068
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

Registered User.
Local time
Yesterday, 16:28
Joined
Jan 23, 2006
Messages
153
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

New member
Local time
Yesterday, 19:28
Joined
Jun 15, 2018
Messages
2
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

Super Moderator
Staff member
Local time
Today, 00:28
Joined
Sep 12, 2006
Messages
13,892
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

User
Local time
Today, 11:28
Joined
Jun 11, 2015
Messages
109
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
 
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 19:28
Joined
Feb 19, 2002
Messages
28,764
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

Registered User.
Local time
Yesterday, 19:28
Joined
Apr 22, 2014
Messages
214
Thank you everyone. I will backup tables and sue the append query. Thanks again for your time.
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom