• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 379
  • Last Modified:

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.
  • 2
  • 2
1 Solution
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"
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?

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now