Copy data from one mdb to other mdb through vb6

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.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Hi Coolcld

Public Sub CopyObject( _
  eType As AcObjectType, _
  strName As String, _
  strNewName As String, _
  strDatabase As String)

  On Error GoTo PROC_ERR
  ' Overwrite existing object
  DoCmd.SetWarnings False
  DoCmd.CopyObject strDatabase, strNewName, eType, strName
  DoCmd.SetWarnings True

  Exit Sub
  MsgBox "Error: " & Err.Number & ". " & Err.Description, , _
  Resume PROC_EXIT
End Sub

Cheers Sash
Ingnore my previous post

1. Open the database that you want to copy the data to.
2. Link the tables from the source database
3. Write a simple insert query to extract the data from the linked table to the destination table.

insert into [table name] (field list) select * from [source table name]
OpenCurrentDatabase opens an instance of Access to execute your code, so it is probably not the best way.

A better way would be to use a DAO connection.

Dim db As DAO.Database
Dim wks As DAO.WorkSpace

Set wks=CreateWorkspace("","admin","")
Set db=wks.OpenDatabase("C:\MyDb.mdb")

db.Execute "INSERT INTO MyTable IN 'C:\MyOtherDB' (Field1, Field2) SELECT Field1, Field2 FROM MyTable"

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CoolcldAuthor Commented:
Hi SashP,
I need to link & delink the tables at run time right? Because the source database will be available only at run time. i.e. Users will select the source database.
CoolcldAuthor Commented:
Hi shanesuebsahakarn ,

Can we use ADODB instead of DAO? Which one is best?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.