Moving data from one database to another - foreign keys question (1 Viewer)

[treeman]

I have a split database where I've been working on the front end mostly with some changes to the backend (some new tables, fields, relationships). I've been working with test data, though there are a few records in a few tables in the test BE that I'm going to want to retain,

In the meantime, data has been entered into a remote back end separate from my test version while I was working on the front end via the test BE.

Now that I have the front end the way I want it, I want to bring the data from the remote back end into my new backend (again, which is similar but with some changes). The remote BE is not very large. There are about 18 tables total, the largest of which has about 500 records.

I understand I can use an append query to do this. I'm unclear on how the data will re-relate when Foreign keys will not equal the same ID # once these are imported or added into a table?

If someone can clarify this for me or maybe point me in the proper direction, I'd greatly appreciate it.

Thanks

 

[MarkK]

Two methods jump to mind . . .
1) Add a temporary field for the old ID in the target table, and then use an append query. That way you can still link the new parent records to the old child records, and then append those children. Repeat as required. Then delete the old ID fields from the new tables.
2) Write code to open recordsets and insert row by row, get the new ID, then insert the children using as FK the new parent's PK, and so on.

1) will be faster to execute but harder to debug and control, and if you botch it, you'll botch it big-time.
2) will take some coding, but it's easier to test (learn how to BeginTrans, CommitTrans and Rollback) and you'll make more messes, but smaller ones.

Either way, it's going to be tedious. You'll wish you had 3 tables of 1,000,000 records, rather than 18 tables of < 500 records. All the best.

 

[treeman]

Thanks Mark,

My limited knowledge of coding leaves me to #1.

Another thought crossed my mind based on some reading I've done:

Does it make more sense for me to go the other way? Exporting my Test BE end (just structure (relationships, table definitions) not data, into the old BE with the current data? From some of the reading I've done, it seems like it's possible to do that in some form.

Also, are you familiar with BTAB Development's Free Access tools? (I could not post the link). There is a BACK-END AUTO-UPDATE TOOL and a DATABASE MERGE ANALYSIS TOOL which may take the place of the the coding you suggested?

Thanks again

 

[MarkK]

As far as which BE gets imported into which BE, that is your call. I know nothing about either one, so I have no info on which to make a judgement. And there's not going to be a free tool to do this. You wrote both files, right? The data is in a completely custom design in each file, so moving the data is going to be a one-off job, with a completely custom process.

But this is a serious hazard you've encountered, and it's why we split the file in the first place, so that we never end up with authoritative data in two locations. This merge you now have to do is a total head-ache.

 

[treeman]

I may not have been clear. What I was getting at was not which BE data gets imported into which BE.

The two options would be:

1) Moving his data from the old BE ,using an append query into, lets say, empty tables in the new BE. These new tables would be the same as the tables in the old BE except they may have a few more fields that would have to eventually be populated or can possibly just wait to be populated when new records are entered. And there may be some new relationships set. In addition, there would be some completely new tables that would obviously be empty because the old BE has no data yet to fill them. That's OK. The only thing that would have to be tackled would to establish new Foreign Keys and you suggested a way to accomplish this.

2) Keeping his old BE as is and using that to link to the new FE. The Data would remain in place. I would just have to add the new tables from the testing BE (they would be empty). Add some additional fields to the tables that were changed and I think that would be it as far as the BE goes. The foreign keys and ID fields would remain exactly the same.

I hope I'm not being redundant but it seems like I may not have explained it well enough so I wanted to clarify and ask again.

 

[MarkK]

I'm not sure what the question is here, sorry. Of the two options you outline, I can't say what would be better because I know nothing about the data.