Appending data from an external file with the same structure

[Cosmos75]

What I'm trying to accomplish is to append data from another file into a file with the same table structure. There are some problems that I can see at the moment:

1. Autonumber fields as primary keys/unique indexes could cause problems with duplicates
2. Data must be appended on 'one-side' (parent) tables first for the 'many-side' (child) tables

Putting aside the first problem for now, is there a way to deal with the second issue?

I can iterate through the DAO tabledefs collection and use db.Execute to run an append action query, but that just goes through the tables in alphabetical order which isn't helpful, because data from a many-side table (child) could be attempted to have data appended which would fail since the one-side table (parent) hasn't had data appended to it.

The only solution I can think of involves manually setting the order the tables in code, e.g.

db.Execute "INSERT INTO [tblParent1]  SELECT [tblParent1].* FROM [tblParent1] IN 'C:\Folder\'"

db.Execute "INSERT INTO [tblParent2]  SELECT [tblParent2].* FROM [tblParent2] IN 'C:\Folder\'"

db.Execute "INSERT INTO [tblChild1]  SELECT [tblChild1].* FROM [tblChild1] IN 'C:\Folder\'"

db.Execute "INSERT INTO [tblChild2]  SELECT [tblChild2].* FROM [tblChild2] IN 'C:\Folder\'"

I was wanting to see if there is a way to avoid having to do that.

Thanks!

 

[Ranman256]

Use TransferText,
Or
Save the file to a target file:ie c:\folder\file2import.txt
Attach the file as an external table,
Overwrite new file over the old target
run append query

 

[plog]

Let's use data to see if I understand. You have a Main database where data needs to end up and you have a Source database where data needs to be imported from. Here's a simplified version of the tables\fields in each:

Main_Parent
M_P_ID, autonumber, primary key

Main_Child
M_C_ID, autonumber, primary key
M_ID_P, number, foreign key to Main_Parent

--

Source_Parent
S_P_ID, autonumber, primary key

Source_Child
S_C_ID, autonumber, primary key
S_ID_P, number, foreign key to Main_Parent

When you put Source_Parent into Main_Parent its going to assign it a M_P_ID that won't match whats in Source_Child.

So far so good? If so, I would add a field to Main_Parent to capture the S_P_ID value:

Main_Parent
M_P_ID, autonumber, primary key
S_P_ID, number, holds value in Source_Parent.S_P_ID

Then a simple APPEND query will work to move data from Source_Parent to Main_Parent as long as you move the S_P_ID as well. Then to move Source_Child to Main_Child you make an APPEND query using Source_Child, Source_Parent and Main_Parent. By doing that you can determine the correct M_P_ID that each Source_Child needs to have in its S_ID_P field.

 

[Cosmos75]

Use TransferText,
Or
Save the file to a target file:ie c:\folder\file2import.txt
Attach the file as an external table,
Overwrite new file over the old target
run append query

Thank you for your reply! I'll have to look into that. Never used TranserText before.

 

[Cosmos75]

If so, I would add a field to Main_Parent to capture the S_P_ID value:

Main_Parent
M_P_ID, autonumber, primary key
S_P_ID, number, holds value in Source_Parent.S_P_ID

Then a simple APPEND query will work to move data from Source_Parent to Main_Parent as long as you move the S_P_ID as well. Then to move Source_Child to Main_Child you make an APPEND query using Source_Child, Source_Parent and Main_Parent. By doing that you can determine the correct M_P_ID that each Source_Child needs to have in its S_ID_P field.

Thank you for the reply!

That could workable for the issue for appending autonumber data, but I don't think that will work since the M_P_ID is used as a foreign key within the main database copy and I'd still need to maintain those relationships between the 'source' file which mirrors the 'main' file. Perhaps I just need to think your suggestion through some more.

The way this is set up (by someone else, not me), is that have a front end and back end. They want to distribute it to other people to populate it with their data. Then add all the data from those copies back into a main version that has all the data.

Yes, it would be nice if it was just a single back end on a network with distributed front end. But that won't work for this situation because not everyone will have access to the network.

 

[Cosmos75]

Well, the best thing I could think of so far is to add code to set the starting autonumber for all tables in each copy via code and use the following DDL SQL:

ALTER TABLE [Main_Parent]
ALTER COLUMN [M_P_ID] COUNTER (100000,1)

Each copy isn't going to have too many records so setting the autonumber far enough apart should work. I know it's not ideal and can still be an issue.

Just have to make sure that I also set the main database to a number greater than each of the copies, so that if the main version is used to enter data, then I can at least mitigate issues of overlapping autonumber field values.