Copy data from one mdb to other mdb through vb6

Posted on 2004-10-28
Last Modified: 2008-03-17
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.
Question by:Coolcld
    LVL 8

    Expert Comment

    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
    LVL 8

    Expert Comment

    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]
    LVL 41

    Accepted Solution

    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"

    Author Comment

    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.

    Author Comment

    Hi shanesuebsahakarn ,

    Can we use ADODB instead of DAO? Which one is best?

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    In the previous article, Using a Critera Form to Filter Records (, the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
    In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
    What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
    With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

    913 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now