Creating a dynamic copy of database

[ryetee]

I have a relatively small data base.

There are a number of what I call reference tables. These are fairly static and in the main will not change.

There are a further half a dozen tables all linked together.

The system basically takes an enquiry which may lead to a repair taking place. The repair will have a number of jobs and each job may have parts replaced.

What I want to do is have an identical database which to begin with will have exact copies of all the reference tables. The remaining half a dozen tables initially will be empty but grow with time.

The user wants to be able to copy the completed repairs he selects over to the 2nd database (which is initially empty). As weeks go by he wants to select further completed jobs that have not already been copied over to the 2nd database. The 2nd database will be updated so I can't just copy over all completed repairs each time.

I had envisage him doing this by using a front end to the 2 databases and firstly selecting which of the completed jobs which haven't been copied over. This is a fairly straight forward query that will update a field on the repair table.

Once he has done this I was thinking that I could run a query that would pull all the rows from the 6 tables from database 1 and update the corresponding tables/rows on the 2nd database.

I don't actually know how to approach that. The process needs to be simple for the user. ie 1. select repairs to copy over 2. run something to copy them over.

Any ideas gratefully received.

 

[essaytee]

Is there reason why you must have the second database? Why not simply use the same database and include completed flags or completion dates, as fields, in relevant tables. Users only see new or fresh records, doesn't matter if there are a thousand records not displayed.

If you must move data around, move it around within the same back-end file.

 

[ryetee]

Is there reason why you must have the second database? Why not simply use the same database and include completed flags or completion dates, as fields, in relevant tables. Users only see new or fresh records, doesn't matter if there are a thousand records not displayed.

If you must move data around, move it around within the same back-end file.

Yep! The original database will still be in use.
The 2nd database will be 'played' around with and data amended. The original needs to be kept intact.

 

[essaytee]

So the original will retain all the records, sorry I misread your question. For some reason I had it in mind records were being deleted from the first database.

Can't help with code sample at the moment as I'm on the road.

 

[ryetee]

So the original will retain all the records, sorry I misread your question. For some reason I had it in mind records were being deleted from the first database.

Can't help with code sample at the moment as I'm on the road.

No 1st database stays the same.

 

[ryetee]

So the original will retain all the records, sorry I misread your question. For some reason I had it in mind records were being deleted from the first database.

Can't help with code sample at the moment as I'm on the road.

You still on the road!! ?

 

[essaytee]

You still on the road!! ?

Back at home now, late on a Saturday night.

In relation to your first database, with the tables that have records that will be copied across, have you considered including a flag field (transferred yes/no) or a date of transfer field. So when it comes time to transfer records only records that haven't been transferred or copied will be transferred. Obvious next step is to update the fields in the first database via an update query.

 

[ryetee]

Back at home now, late on a Saturday night.

In relation to your first database, with the tables that have records that will be copied across, have you considered including a flag field (transferred yes/no) or a date of transfer field. So when it comes time to transfer records only records that haven't been transferred or copied will be transferred. Obvious next step is to update the fields in the first database via an update query.

Selecting the records isn't a problem (I don't think). One of the files has a completion date on it and all other tables are linked.

Without having to explain the system it's akin to the following...

Customer Enquiry 1 record
Order 1 record (this holds the completion date)
Jobs within order 1 to many with the order.
Parts 1 to many with the jobs
Swaps 0 to many with parts

I want the ID to be the same in both databases.
I want to be able to get the user (just the 1 user for this part of the system) to run a set of queries (through a form interface) that will read the above tables from database 1 and populate database 2. I want the user to say press a button (or maybe select the orders himself - he may not want all the completed orders at the same time) and behind the scenes it all happens!!

I'm after the approach I have to take. Do I have to deal with tables individually, can I do everything with an update query etc etc.

All help appreciated!

 

[essaytee]

Selecting the records isn't a problem (I don't think). One of the files has a completion date on it and all other tables are linked.

Without having to explain the system it's akin to the following...

Customer Enquiry 1 record
Order 1 record (this holds the completion date)
Jobs within order 1 to many with the order.
Parts 1 to many with the jobs
Swaps 0 to many with parts

I want the ID to be the same in both databases.
I want to be able to get the user (just the 1 user for this part of the system) to run a set of queries (through a form interface) that will read the above tables from database 1 and populate database 2. I want the user to say press a button (or maybe select the orders himself - he may not want all the completed orders at the same time) and behind the scenes it all happens!!

I'm after the approach I have to take. Do I have to deal with tables individually, can I do everything with an update query etc etc.

All help appreciated!

For simplicity, I would first ensure that the target database have a copy of the source tables.

In your front-end (source) create Update queries. Deal with one table per Append query. When clicking on the Append query button from the ribbon, the following dialog window appears:

Select 'Another database' and enter your target database file. Then select the corresponding table, your target table to be appended.

Either use the asterisk (all fields) or purposely select every field. Either way, you will need to filter only to new records.

Save the Append query, name it accordingly.

For your linking tables, it's as per the above, but the source Append query will need to be joined to the master/parent table to enable you to filter to new records only.

If your target database enforces referential integrity then I would append the queries in the correct order. The master/parent table first followed by the linking tables.

Don't forget that after appending to the target database you will need to update your source tables as well.

You run/execute these queries via code behind a command button.

DoCmd.SetWarnings False
DoCmd.OpenQuery "qry_update_tbl_whatever"
DoCmd.OpenQuery "..... and so on"
DoCmd.SetWarnings True

The above is but one approach, and it doesn't address issues of failure during the appending/updating process but in the interim, get this working, and it can be further enhanced if need be.

As I finish writing this a thought came to mind. I understand your target database itself will be updated so it will not be an exact copy of the source database. If the target tables are not modified, that is, additional fields added, then it would be quite feasible to simply copy the source tables in their entirety if you didn't want to mess about with filtering to new records only. This approach may alleviate the problems of failure to a lesser extent during the appending/updating process.

 

[ryetee]

For simplicity, I would first ensure that the target database have a copy of the source tables.

In your front-end (source) create Update queries. Deal with one table per Append query. When clicking on the Append query button from the ribbon, the following dialog window appears:

Select 'Another database' and enter your target database file. Then select the corresponding table, your target table to be appended.

Either use the asterisk (all fields) or purposely select every field. Either way, you will need to filter only to new records.

Save the Append query, name it accordingly.

For your linking tables, it's as per the above, but the source Append query will need to be joined to the master/parent table to enable you to filter to new records only.

If your target database enforces referential integrity then I would append the queries in the correct order. The master/parent table first followed by the linking tables.

Don't forget that after appending to the target database you will need to update your source tables as well.

You run/execute these queries via code behind a command button.

DoCmd.SetWarnings False
DoCmd.OpenQuery "qry_update_tbl_whatever"
DoCmd.OpenQuery "..... and so on"
DoCmd.SetWarnings True

The above is but one approach, and it doesn't address issues of failure during the appending/updating process but in the interim, get this working, and it can be further enhanced if need be.

I as finish writing this a thought came to mind. I understand your target database itself will be updated so it will not be an exact copy of the source database. If the target tables are not modified, that is, additional fields added, then it would be quite feasible to simply copy the source tables in their entirety if you didn't want to mess about with filtering to new records only. This approach may alleviate the problems of failure to a lesser extent during the appending/updating process.

I've taken a slightly different approach which (sort of) caters for failure during appending.

First of all the user clicks on an export button. The event runs queries for all the linked tables from the 1st database in the "DoCmd.TransferText acExportDelim" statement.
The user will then click on the import button which runs the "DoCmd.TransferText acImportDelim" statement for each appending to all of the linked tables in the second database.

The queries on the export filter out any records already copied across.

I think what I've done is restartable but I am looking to run this with a VBscript which will take a back up of the database just in case!

 

[essaytee]

I've taken a slightly different approach which (sort of) caters for failure during appending.

First of all the user clicks on an export button. The event runs queries for all the linked tables from the 1st database in the "DoCmd.TransferText acExportDelim" statement.
The user will then click on the import button which runs the "DoCmd.TransferText acImportDelim" statement for each appending to all of the linked tables in the second database.

The queries on the export filter out any records already copied across.

I think what I've done is restartable but I am looking to run this with a VBscript which will take a back up of the database just in case!

To clarify, from the source database you export the data to a temporary file. Then open the target database and import the data from the same temporary file.

I don't understand your term, 'restartable'.

 

[ryetee]

To clarify, from the source database you export the data to a temporary file. Then open the target database and import the data from the same temporary file.

I don't understand your term, 'restartable'.

In the front end I link to both databases. I've changed the name of the tables in the 2nd database in this front end. So I export the data from database 1 to a temp file and then import that temp file into the 2nd database but all from the same front end.

By restartable I mean I can run it several times and it won't screw the data up in the second database.