The code resides in VB.
I need to copy data from 4 tables from one mdb to other. 3 of the tables have same structure in both the databases. One table has additional fields in second database.
What is the best way of doing it through VB? Currently I used the following method
---Set objMsAccess = CreateObject("Access.Application")
---objMsAccess.OpenCurrentDatabase f_strOldDbPath, True
---Set l_rstOld = objMsAccess.CurrentDb.OpenRecordset("Select .. From tblSubset")
---Open the second database in the same way as above
---Loop through the recordset and insert records one by one to the table in other database
I know this is not the best way of doing. Is this way of connection is better than OLEDB or ODBC? Can anyone suggest a better method?
1) The two databases can be of different versions (eg: one is 'access97' and the other is 'access2000'). The code should copy the data from one version to other.
Thanks in Advance.