How to transfer data from one external database to another?

[apeters]

I'm using MS Access to connect to two external databases. I need to programmatically pull data from one database and copy it to the other, without copying it to local MS Access tables first.

My first effort involved (manually) creating links to the tables in the source and target databases, then programmatically creating an ADO connection to CurrentProject.Connection, and executing "INSERT INTO TABLE_B SELECT * FROM TABLE_A" against the new connection. This worked, but relies on creating linked tables.

Is there a way to do it without linked tables, i.e. just with ADO?

 

[ezfriend]

You could probably declare two ADOs connecting to both database and then ADO1 is to pull record from database1 and then using ADO2 to insert into the second databae.

Sample Code (not tested yet)

Private Sub DoThis()

   dim rs1 as ADODB.Recordset
   dim ssql as String

   OpenDatabaseConnection True
   OpenDatabaseConnection False

   ssql = ""
   ssql = ssql & "SELECT * FROM tblA "
   set rs1 = gdb1.execute(ssql, , adcmdtext)


   do while rs1.eof = false


        ssql = ""
        ssql = ssql & "INSERT INTO tblA(FirstName_TX, LastName_TX, Address_TX,.....) "
        ssql = ssql & "values("
        ssql = ssql & "'" & rs1!FirstName & "" & "', "
        ssql = ssql & "'" & rs1!LastName& "" & "', "
        ..............................
        ..............................
        etc....
        gdb2.execute ssql, , adcmdtext


        rs1.movenext
   loop

   rs1.close
   set rs1 = nothing

   CloseDatabaseConnection True
   CloseDatabaseConnection False

End Sub

MODULE1

Option Explicit

Public gdb1 As ADODB.Connection
Public gdb2 As ADODB.Connection

Public Sub OpenDatabaseConnection(ByVal bFirstDatabase As Boolean)

    Dim sConnectionString As String
    
    If bFirstDatabase = True Then
    
        Set gdb1 = New ADODB.Connection
        'FirstDatabase
        sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & CurrentProject.Path & "\db1.mdb;"
    
        gdb1.ConnectionString = sConnectionString
        gdb1.Open
    
    Else
        'Second Database
        sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & CurrentProject.Path & "\db2.mdb;"
    
        gdb2.ConnectionString = sConnectionString
        gdb2.Open
    
    End If
    
End Sub

Public Sub CloseDatabaseConnection(ByVal bFirstDatabase As Boolean)
    
    If bFirstDatabase = True Then
    
        If gdb1 Is Nothing = False Then
            gdb1.Close
        End If
        
        Set gdb1 = Nothing
    Else
        If gdb2 Is Nothing = False Then
            gdb2.Close
        End If
        
        Set gdb2 = Nothing
    
    End If
    
End Sub

 

[apeters]

Thank you