?
Solved

copy back a table with autonumber

Posted on 2014-04-19
6
Medium Priority
?
396 Views
Last Modified: 2014-04-19
Dear Experts,

I have a table with autonumber as index, but I allow the user to delete records randomly.
After that, I want to restore the table with autonumber without skipping, so I thought I could export the table without the autonumber field, then insert it back to the original structure.  As I am trying to do that, I get "invalid argument" error, and does not work.
How can I get my table to refresh, and get the autonumber in order as if I am entering the data as new?
Please advise.
0
Comment
Question by:yballan
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 29

Expert Comment

by:IrogSinta
ID: 40010834
What is your purpose in re-sequencing the autonumber field?  That would be very inefficient to do every time a record is deleted.

Ron
0
 

Author Comment

by:yballan
ID: 40010840
More information:
In attempt to troubleshoot my query, I trimmed it down to just inserting one field, then I got a different error, looking for a mdb file.
But I am using Access 2010, my files are called XXX.accdb, and it is stored in a totally different folder from what the error message says.
I am extremely puzzled, please advise.
Untitled.jpg
0
 

Author Comment

by:yballan
ID: 40010843
Dear IrogSinta,

Thank you for your quick reply, I use the index for traversing forward and backward, and I find it easier to link everything if autonumbered index is in order.
This is meant to be as refreshing the table, and not supposed to happen often.
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 48

Assisted Solution

by:Dale Fye
Dale Fye earned 1000 total points
ID: 40010851
I'm with IrogSinta,

Autonumber fields are meant to be used as primary keys and should be irrelevant to users as they should never even be visible to users.  

Indexing and traversing forward and backwards through the recordset has nothing to do with skipped values in the autonumber field.

Linking to that table will be a nightmare if you allow changes to the autonumber field and will likely break references to other tables.

I never resequence an autonumber field except when I first deploy an application, and then only after deleting all of the records in the table that were created during development and testing.  And only then for tables which are not used as lookup tables for use in my combo boxes and such.
0
 
LVL 29

Accepted Solution

by:
IrogSinta earned 1000 total points
ID: 40010878
I will answer you're question on the problem with your Insert SQL statement question; however, this is just for your learning benefit and does not mean that you should continue down this erroneous path.  Fyed already gave you an excellent explanation on why you should not take that route.  It can definitely cause future problems.  Perhaps you should show us how you traverse your recordsets; you may be doing it wrong.

Here's the correction to your SQL:
INSERT INTO New Products (CategoryS) SELECT CategoryS FROM RefreshNewProduct

Open in new window

0
 

Author Closing Comment

by:yballan
ID: 40010886
Dear feyd and IrogSinta,

I appreciate your educating me for this error, I do realize that I am trying to force something that is not quite right.  I have inherited this large amount of code, and since I am short of time, I did not want to rewrite all of the traversing codes.  I understand that there is something fundamentally flawed with the design of the original code, and what I really should do is to understand it, and correct it.

Once I get through this deadline, I will rewrite this, I do believe that it will cause other problems as you said.
Thank you for the correct SQL script.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses
Course of the Month12 days, 15 hours left to enroll

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question