Import data from another database

[Conor]

Hi,

I am attempting to run a query on a database and to copy
the resulting recordset into an Access table in another
database programatically.

The code works but is there an easier way?

Sub GetExternaldata()
Const Provider = "Provider=Microsoft.Jet.OLEDB.4.0; "
Const DataSource = "Data Source = Z:\New Folder\Access
Database Samples\db4.mdb"

Dim Rst1 As New ADODB.Recordset
Dim Rst2 As New ADODB.Recordset

Dim Cnt1 As New ADODB.Connection

Dim Cnt2 As ADODB.Connection
Set Cnt2 = CurrentProject.Connection

Cnt1.Open (Provider & DataSource)
Rst1.Open "Supplier", Cnt1, adOpenDynamic, adLockOptimistic
Rst2.Open "Table_1", Cnt2, adOpenDynamic, adLockOptimistic


Rst1.MoveFirst
Do While Not Rst1.EOF
Debug.Print Rst1.Fields(1), Rst1.Fields(2)
Rst2.AddNew
Rst2.Fields(0) = Rst1.Fields(0)
Rst2.Fields(1) = Rst1.Fields(1)
Rst2.Update

Rst1.MoveNext
Loop

End Sub

 

[Tim Ferguson]

I am attempting to run a query on a database and to copy
the resulting recordset into an Access table in another
database programatically.

INSERT INTO OtherTable IN "c:\otherdata\otherfile.mdb"
SELECT Stuff FROM OldTable

It's safer to use the proper insert syntax with the fields list, but you
get the picture...


Tim F

 

[Conor]

Thanks for the help that worked perfectly.

-----Original Message-----


INSERT INTO OtherTable IN "c:\otherdata\otherfile.mdb"
SELECT Stuff FROM OldTable

It's safer to use the proper insert syntax with the fields list, but you
get the picture...


Tim F



.