asked on

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

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?

chiche

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;

brewdog

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

ASKER CERTIFIED SOLUTION

KangaRoo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

xavier51

ASKER

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!