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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

746 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now