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
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
212 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
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

791 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