Microsoft Access VBA Macro Programming

to version 2, depending on what version of Windows you are running, but they will still work
in the same way. If you do not have Version 6.0, use the latest version you have. Mark both
check boxes on the left and click OK.
It is important to have this reference so it gives you full functionality to ADO. It is also
good programming practice.
You can now enter the following code into a module. This assumes you are connecting to
a SQL Server database, but you can also use other Access databases for the connection string
(see the following examples).

Sub ADOExtract()
Dim RsADO As ADODB.Recordset, RsAccess As Recordset
Dim Cnct As String,Cnct 1 as String, Cnct 2 as String

Set Connection = New ADODB.Connection
Cnct 1 = "Provider=SQLOLEDB;Driver={SQL Server Native Client 10.0};"
Cnct2="Server=MyServer;Database=MyDatabase;Uid=MyId;Pwd=MyPassword;"
Cnct=Cnct 1 & Cnct 2
Connection.CommandTimeout = 60
Connection.Open ConnectionString:=Cnct

Set RsAccess = CurrentDb.OpenRecordset("MyDestinationTable")

Set RsADO = New ADODB.Recordset

With RsADO

.Open Source:="select * from MySourceTable", _
ActiveConnection:=Connection, CursorType:=adOpenStatic

Do Until RsADO.EOF

RsAccess.AddNew

RsAccess!Field 1 = RsADO!Field 1

RsAccess!Field 2 = RsADO!Field 2

RsAccess.Update

RsADO.MoveNext

Loop

End With

Set RsADO = Nothing
Set RsAccess = Nothing
End Sub

Chapter 19: Working with External Databases 249