copy records

[cor]

Hi, i got the following problem. I got like 10 tables filled with several records. I want from each table all the records added to these same tables with just 1 field changed (same field in all tables: company_ID). I tried this first in code by walking through all tables and make a temp table then changed this field and then make an append query, but of course this gave conflicts, since all tables have an autonumber indexed primary key field. So im not sure how to solve this problem. Thanks for your time, Cor

 

[marianne]

why do you want each tables records to be added to same tables. I dont get what you want to achieve here? doesn't relating all the tables together solve your problem?

 

[Galaxiom]

Tell us more about your data. What are the tables and their fields storing?
I suspect you probably need to review the structure before continuing.

.

 

[cor]

Marianne,
I want to make a “blueprint” of the dataset of company_id 1 into dataset company_id 2 (and all the tables that are referred) . So that we are able to compare results of both datasets, with adjusting just some values. So basically we want to simulate a new situation in the dataset 2 and compare that with the current situation(dataset 1)

 

[Galaxiom]

If you planning to operate the same queries on the duplicated data the easiest way would be to make copies the database frontend and backend. Place them in separate folders to make sure you don't get them mixed up.

Then change all the linked tables in the duplicated front end to point to the duplicated backend. This essentially gives you a complete independent duplication of the whole thing that you can run just like the live dataset.

Make absolutely sure that every link has been changed to the duplicated backend or disaster will ensue.

Then change the fields in the duplicate data to suit your whatif situation. I would also change the background of the duplicate forms to another colour so you know you where you are. Wouldn't want to get it wrong.

When you want to update the duplicate data you would just need to copy the live backend over the duplicate. Make sure you copy in the right direnction. It is worth making a batch file command to do this so you will never copy in the wrong direction.

Comparing the outcomes could be done by running the reports in each database. Easy but clunky. The alternative of linking one frontend to both datasets is potentially very dangerous. You must take a lot of precautions if you go this way.

You could connect the required tables from the live set into the duplicate set making sure they are named very clearly. Do this in the duplicate FE. This way you are only likely to corrupt your duplicate data if you fail to edit a copy of a query correctly.

You would have to duplicate the queries and edit these copies to use the live tables. Forms would have to be changed to use the copied queries and tables. This can be a horrible task and if you miss any reference to the tables or queries based on the other dataset you will get inconsistent results. Once again I emphasise that you do this in the duplicate

The safest way is to import the tables from the live set rather than linking. This ensures they will never be corrupted but you would have to update them each time you needed to compare with the live data.

If you did link the tables you would want to ensure there were no update queries used in case you missed editing one. Make all of your forms that access the live data use a SnapShot RecordSet Type. This means they can not change the original data.

Then build queries and reports to compare the results of the existing queries made on each set of data.

 

[cor]

Thank you very much for your reply GalaxionAtHome. Too bad this will not be a suitable solution here. I want it to be user friendly where one can add or remove datasets as often as one wishes in an user friendly interface. So there can be made a third dataset too. It just seemed so easy to add all records in all tables where company_ID=1 and change this into company_ID=2. The solution I can think of now is to go through all tables and read the fields and leave out the autonumber field. so I can copy the records in code. This might be too advanced programming for me though lol. Was hoping for an easier way to fix this. Thanks again!

 

[Galaxiom]

Have a look at this post.
http://www.access-programmers.co.uk/forums/showthread.php?t=104025

It shows how to use VBA to delete a linked table and replace it with another. Make backends for all your alternative datasets and then link the one you want with a procedure.

To be on the safe side I would still keep the live dataset and front end independant and do this on a copy.