Importing data into table from macro

[Paul]

Hi there,

I am extracting records from an external database and I want to write the
results into MSAccess tables. I am able to do this using the following
example code:-

Dim db As Database
Dim dbrs As New ADODB.Recordset
Dim rs As New ADODB.Recordset

Set db = CurrentDb

dbrs.Open "YearEnd", CurrentProject.Connection, adOpenKeyset,
adLockOptimistic

OpenADOConnection 'Macro to open connection to external database

SQLSeltxt = SQL query

rs.Open SQLSeltxt, cnnADO, adOpenStatic, adLockOptimistic, adCmdText

If Not rs.EOF Then

rs.MoveFirst
While Not rs.EOF
dbrs.AddNew
With dbrs
.Fields("CaseKey") = rs("CASE_KEY")
.Fields("CaseMbrKey") = rs("CASE_MBR_KEY")
.Fields("FdDescId") = rs("FD_DESC_ID")
.Fields("Units") = rs("SUM(BDA.AMT)/UV.BID_AMT")
End With
rs.MoveNext
Wend
dbrs.Update
Else
MsgBox "No records found."
End If

rs.Close
dbrs.Close

The problem I have is that I have to add records into the Access database
one at the time. Is there a method I can use to add all the returned rows
using one command?

Thanks for the help.

Cheers

Paul

 

[Steve Schapel]

Paul

You may be able to use a TransferDatabase action in a macro, or a
DoCmd.TransferDatabase method in a VBA procedure, to get the remote data
into your database, and then use an Append Query, via a OpenQuery action
in a macro, or a CurrentDb.Execute method in a VBA procedure, to put the
imported data into the required local table(s).