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

Copy data from one mdb to other mdb through vb6

Hi,
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?

Assumptions:
--------------
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.
0
Coolcld
Asked:
Coolcld
  • 2
  • 2
1 Solution
 
SashPCommented:
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

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

Cheers Sash
0
 
SashPCommented:
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]
0
 
shanesuebsahakarnCommented:
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"
0
 
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.
0
 
CoolcldAuthor Commented:
Hi shanesuebsahakarn ,

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

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