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?
xavier51Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
KangaRooConnect With a Mentor Commented:
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
 
chicheCommented:

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
 
brewdogCommented:
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
 
xavier51Author Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.