Data Transfer

[kimtp]

Have two databases with different field names. Not all fields can/will be mapped. When I query the 'from' database and there is no mapped field in the 'to' database, I get an error. How to check the from database, if no match, then move on?

Next I would then put the data into a collection and then insert it into the 'to' database. Can that be done all at once or does it have to be one field at a time?

thanx.

 

[jpittawa]

You need to give a more detailed explanation the problem.

It sounds to me like you have to syncronize the data from one table in a database with the data from a table in another database. Further, it sounds like some of the fields are identicle, i.e. they have the same data type and information.

Based on this assumption, the solution that comes to mind would require a fare amount of coding.

I use the ADO to create a source recordset and a destination recordset. Set On Error Resume Next. For each record of the source database, iterate through the Fields object and insert it into the destination Field of the same name. When the destination lacks a Field of the same name, or the same data type, ADO will throw an error but your code with ignore it.

At the end of the record iteration insert the destination record. In the next line test Err.Number <> 0. This will tell you if there is a problem.

This process will require some refinement.

If the databases are SQL Server, I recommend writing a DTS script.

If you give me some more detail, I will try to help you.

 

[kimtp]

jpittawa -

Thanks for the response. Here is what I have so far:

VB form where the user maps his/her fields ('from' table) to the existing table, ie the 'to' table. Collect the data and then do a query:

sSql = &quot;INSERT Into Members (Firstname, Lastname)
SELECT '&quot; & sFirst & &quot;','&quot; & sLast & '&quot;
FROM &quot; & sTable

rs.open ssql, cdata

This code seems to do nothing.

Thanks for the help

Kim