Importing Single Text File Into Multiple Tables In Database

[Daz]

Hi,

I have managed to create an export and import test routine for my dbase. What it does is the record that i am currently viewing on the form can be exported as one record only, this works fine with data from just one table. The way I collate the data to be exported is through a query.

The problem is the data that i need to export\import is in multiple tables. ie (Customer_Tbl and Comments_Tbl)and linked on the customers details on the form that i view. The export of the data is no problem, but how do i import the right data to the two tables? I created a specification file for the export\import, but i could not see how to inform the specification file that the data needs to be put into the two tables.

Can it be done, if so how?

Many Thanks

Daz

 

[raskew]

I'm a bit confused. Are you attempting to add to existing tables in the foreign database? If so, and if you are using the TransferDatabase method, are you aware that it will overwrite an existing table in the target database?

Consider using append queries, which you can structure to append just specific items to the target table.

 

[Daz]

Thanks for responding

What i am trying to create is:

a person in one town looks at a customers details via a form in a duplicate dbase to mine, the customers info is split into several tables ie CustDetails_Tbl, CustComments_Tbl, CustOrders_Tbl. this user exports this data for this one customer into a txt file and emails me the data. When i recieve this data i import it into my identical system but the import needs to place the relevant data in the relevant tables CustDetails_Tbl, CustComments_Tbl, CustOrders_Tbl, these are linked by the customers ID.

I hope this is a little clearer

Daz

 

[raskew]

Must be slow--

Based upon your description of identical databases, you already have the information in your database. Doing what you describe merely serves to duplicate the data and brings up problems re primary key. What am I missing?

 

[Daz]

OK,

I am trying to create a reporting system to be used by 15 outlets and one head office. the outlets use this reporting system to enter customer data, name, address, goods purchased, price, income made, commissions paid etc. then on a daily, weekly or monthly basis they export the customer files and send them to head office. Head office import the data into their system and then they can run reports etc from that. The head office version would import the outlets Customer_ID's in a slightly different field. and then create a new customer_ID just for head office.

The data needs to be imported\exported into and from several tables. the export bit i have solved but i am struggling with the import.

Unfortunatley I have no experience with writing code. so it has to be an easy fix if possible.

Darrell...

 

[THoey]

What is the chance of creating a temporary table that you import the whole data record into and then running one or multiple append queries that take portions of each record from that temporary table and place them into the appropriate table? What I mean is a table something like:

Temp_Table
A1
A2
B1
C1
C2

Then three queries that would grab the data for table A and append it, grab the data for table B and append it, etc.

I wouldn't do it with queries myself, but I have more experience writing VB code. If you decide to try the code way, all you need to do is follow a couple of steps:

1. Create a button that would perform your processing. This button would kick off the following steps.
2. Import the data into the temporary table (make sure it is empty first)
3. Open recordsets for the temporary table and for each of the destination tables (See OPENRECORDSET command).
4. Loop through the temp table (see DO UNTIL & RECORDSET.EOF commands)
5. For each record in the temp table, add a new record to the destination tables (see RECORDSET.ADDNEW command).
6. Load the appropriate values to the fields in the destination tables (Dest_Table.FieldName = TempTable.FieldName)
7. Save the records in the destination tables (see the RECORDSET.SAVE command).
8. get the next record in the temp table (see RECORDSET.MOVENEXT)
9. End of loop (back to #4)
10. After all records have been processed, clean out the temp table and close all recordsets.
11. Display Import completed message. Now would be a good time to selfishly promote that next raise too for saving your company all that time...

Hope that helps.


Terry M. Hoey
th3856@txmail.sbc.com

Ever notice that by the time that you realize that you ran a truncate script on the wrong instance, it is too late to stop it?