Go Back   Access World Forums > Microsoft Access Discussion > Tables

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 02-13-2007, 09:12 AM   #1
97fleminglja
Registered User
 
Join Date: Feb 2007
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
97fleminglja is on a distinguished road
Pasting data with autonumber

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.

97fleminglja is offline   Reply With Quote
Old 02-13-2007, 09:30 AM   #2
boblarson
Smeghead
 
boblarson's Avatar
 
Join Date: Jan 2001
Location: Oregon, USA
Posts: 32,068
Thanks: 97
Thanked 1,811 Times in 1,573 Posts
boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold
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.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
boblarson is offline   Reply With Quote
Old 02-13-2007, 10:53 AM   #3
Banaticus
Registered User
 
Join Date: Jan 2006
Posts: 153
Thanks: 0
Thanked 1 Time in 1 Post
Banaticus is on a distinguished road
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!

__________________
If you like or dislike this post, click the Scales of Justice in the top right corner of the post, then click to say whether you approve or disapprove of this post.
Banaticus is offline   Reply With Quote
Old 10-11-2018, 05:15 AM   #4
muse
Newly Registered User
 
Join Date: Jun 2018
Posts: 1
Thanks: 1
Thanked 0 Times in 0 Posts
muse is on a distinguished road
Re: Pasting data with autonumber

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!
muse is offline   Reply With Quote
Old 10-11-2018, 06:46 AM   #5
gemma-the-husky
Super Moderator
 
gemma-the-husky's Avatar
 
Join Date: Sep 2006
Location: UK
Posts: 13,460
Thanks: 51
Thanked 948 Times in 917 Posts
gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all
Re: Pasting data with autonumber

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)
__________________
Dave (Male!)
Gemma was my dog

if a poster helps you, please click the scales at the top right of this posting, or use the thanks button alongside.
gemma-the-husky is offline   Reply With Quote
Old 10-13-2018, 02:42 AM   #6
InFlight
Newly Registered User
 
Join Date: Jun 2015
Posts: 10
Thanks: 3
Thanked 0 Times in 0 Posts
InFlight is on a distinguished road
Thumbs up Re: Pasting data with autonumber

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 by InFlight; 10-13-2018 at 03:08 AM.
InFlight is offline   Reply With Quote
Old 10-14-2018, 07:44 PM   #7
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,394
Thanks: 13
Thanked 1,416 Times in 1,350 Posts
Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light
Re: Pasting data with autonumber

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.


__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
SQL and Data Access Pages CyrusMacsen SQL Server 0 12-12-2005 05:28 PM
Pasting Data onto Reports chris990 Reports 1 09-27-2005 11:34 AM
How to transfer data bigboy General 5 09-08-2005 04:38 AM
using autonumber data against IN (list)? igloo Queries 1 09-27-2004 12:20 PM
Help Saving form data to a different table bessej43 Tables 2 08-27-2002 04:41 AM




All times are GMT -8. The time now is 12:13 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World