Solved

Is there any way to temporarily turn off exsting AUTONUMBER attribute so I can merge data from two partial tables?

Posted on 2000-02-16
4
216 Views
Last Modified: 2008-02-01
Here's the problem.  I have several tables that have a parent/child relationship.  One of the fields stored in the child table, is based on an autonumber field from the parent table, assigned when the record was first added. One of my customers ran a function which unknowingly messed up a portion of their data.  I can get a good chunk of it back, but to do so, I need to merge records into an existing table that has the autonumber field. On the surface this sounds easy, but I need to have the autonumber field in the target table reflect its original value from the source table, not a newly assigned number, as I believe is the default behavior.

If I was able to disable the autonumber feature, or somehow specifiy the value of this field as I add the new data, add back my records, life would be much simpler. The autonumber fields in question are part of the primary key in all the tables.

I have tried the obvious approach to set the field-level autonumber attribute through code, both in an Access module, and through VB, but every time I attempt it, I get an runtime error 3219 - "invalid operation"

Code sample:

fd.Attributes = fd.Attributes And Not dbAutoIncrField

I can change this property at will in the database design window, but I do not want to have to manually switch this property back and forth, as it would be easy to forget to switch it back.

Can someone help me with this dillema?
0
Comment
Question by:xavier51
[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
4 Comments
 

Expert Comment

by:chiche
ID: 2527414

the problem isn't to switch from autonumber to number but from number to autonumber:

1) backup your data

2) remove the relationships

3) change the "autonumber" type in the parent tables to "number" (long integer) using the table designer.

4) when you are done "merging" your data, create a NEW EMPTY table with the same strucure as the original table (including the autonumber field)

5) create & run an append query that insert data from the "merged" table into the new table. something like:

INSERT INTO newdata SELECT * FROM merged_data;

6) you should now have a table that has all the records you want and an autonumber field

7) re-create the relationships


hope that helps;
0
 
LVL 10

Expert Comment

by:brewdog
ID: 2527420
I think what you'll probably have to do is:

1. Make a copy of your parent table (the one with the AutoNumber) -- structure and data
2. In one copy of the table, change the AutoNumber to standard Number. This will let you add in the numbers/records you need.
3. If you need to update the numbers back to their "original" numbers, run a query the joins your original and copy parent tables on every field that wouldn't have changed.

This will get the table back to the original state with the data corrected. Getting this field back to an AutoNumber is going to be the trick. Is there any way you can leave it a non-AutoNumber and instead use a separate table or a DMax function to retrieve the next value to be used?

brewdog
0
 
LVL 7

Accepted Solution

by:
KangaRoo earned 100 total points
ID: 2527442
Does it help to know you can override the autonumbering when inserting?

INSERT INTO Table1 ( id )
SELECT 100 AS Expr1;

id is an autonumber field.
0
 

Author Comment

by:xavier51
ID: 2527814
That's what I wanted - seems to work great!.  I was not aware that SQL would override that field.  When I tried doing it through the GUI (using paste append), it didn't pull that value accross, so I just assumed SQL insert would work exactly the same - Bad programmer!

Thanks again!
0

Featured Post

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

726 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