Hi there

I'm a beginning ADO user ... please help!

Is there a more efficient and less time-consuming way to achieve the
following effect (pulling Access data into Excel) ...?

I have two different *.mdb files with identical field etc. structures
(really a single database that is bigger than 2GB and that I've split into
two files). A given piece of analysis in Excel requires records from both
files (however I split the database). In each iteration (over 200,000 loops)
I open a connection to the first database and create a recordset and,
thereafter, open a connection to the second database and create a new
recordset. The two recordsets are then placed in a contiguous range on an
Excel sheet and I proceed with the analysis from there ...

I've truncated my code below.

Please help

Loane

Dim rsData As ADODB.Recordset
Dim strConnect As String
Dim strSQL As String

For i = 1 to NoRecords

For j = 1 to 2

If j = 1 Then
strConnect = "...\DataBase1.mdb;"
strSQL = "SELECT Field1 FROM Table1 WHERE Index = i"
ElseIf j = 2 Then
strConnect = "...\DataBase2.mdb;"
strSQL = "SELECT Field1 FROM Table2 WHERE Index = i"
End If

rsData.Open strSQL, strConnect, adOpenForwardOnly, adLockReadOnly,
adCmdText

Cells(i, 1).CopyFromRecordset rsData

rsData.Close

Next j

[Some code representing Excel-based analysis goes in here. The analysis
currently requires that the data in the two recordsets (i.e. drawn from
Table1 and Table 2, respectively) be deposited in a contiguous range in a
worksheet.]

Next i

Set rsData = Nothing