Hi Loane,
I'm sure there is a more efficient way of doing this, but not knowing the
data, it's hard to give specifics. Here are a few general
comments/suggestions:
1) Only hit each database once:
strSQL = "SELECT Index1, Field1 FROM Table1 " _
& "WHERE Index<=" & CStr(NoRecords)
....and do the same for Table2 from database #2.
2) Use CopyFromRecordset to copy each recordset to a range (obviously,
Table2 will have to go below Table1).
3) Use Excel's built-in sort (Sort method) to sort the resulting data so it
is in the order you want (from what I understand of your data, you'll
probably sort on Index and then Field1). If you don't have a good secondary
key to determine which table the row came from, you can add that key to a
column to the right of your data (a 1 for Table1 and a 2 for Table2, for
instance) and use that as your second sorting column.
If you really have 100,000 records to return, you'll have to split them over
2 or more worksheets, as each worksheet can hold only ~65k rows.
Another option is to put all the data for each Index on the same row (if you
want that):
1) See step 1 above.
2) Use CopyFromRecordset to copy each recordset to its own worksheet.
3) On the second worksheet (Table2 data), use the VLOOKUP worksheet function
in a third column to "pull" the data from worksheet 1, column 2 based on the
Index #.
A final option (one that I'm not sure will work because of possible
limitations in Access):
1) Set up a linked table in one of your databases that points to the other
database. For this example, let's assume that in DataBase1.mdb, you set up
a link to Table2 in DataBase2.mdb and name the linked table "Table2". Now,
you can use a single SELECT statement to get all the data you need:
strSQL = "SELECT a.Index, a.Field1 As Table1Field1, b.Field1 As
Table2Field1 " _
& "FROM Table1 a INNER JOIN Table2 b ON a.Index=b.Index " _
& "WHERE a.Index<=" & CStr(NoRecords)
2) Use CopyFromRecordset to copy each recordset to the desired range.
Hopefully, this makes sense. If you have any further questions, please
reply to this post and we'll try to help out further.
--
Regards,
Jake Marx
MS MVP - Excel
www.longhead.com
[please keep replies in the newsgroup - email address unmonitored]
Loane Sharp wrote:
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