Hello all.

I am using Excel 2002 and Windows 2000.

I've been working on a Excel VBA program that uses ADO to get data
from an Access database. Several connections and queries are made
throughout the program, and I've always just used ADO's .GetRows
method to assign the data in the recordset to a two-dimensional
variant array. After that, of course, the data has to be TRANSPOSEd,
and all is fine.

I thought this was "just how it's done".

Then I stumbled across the Range object's .CopyFromRecordset method.
Lo and behold, it seemed that I would be able to put my recordset data
directly into a spreadsheet! No more putting it into a
two-dimensional variant array and TRANSPOSE-ing it!

Alas, after making the adjustments to my code, it does not seem to
work.

I get no errors at all-- it's just that after the line of code with
the .CopyFromRecorset method, my spreadsheet is still blank.

Makes me realize why errors are nice. At least then you have a
starting point for troubleshooting.

Here is the relevant code:

------------------------------------------------------------
Dim adoConnection As ADODB.Connection
Dim adoRecordset As ADODB.Recordset
Dim strConnection As String
Dim strSQL As String

Set adoConnection = New ADODB.Connection
Set adoRecordset = New ADODB.Recordset

' strFilepath is defined in a module
strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
strFilepath
adoConnection.Open strConnection

' build the query string
strSQL = "SELECT * FROM ExcelAnalyteGraph WHERE Location = '" &
lstWell.Text _
& "' AND Name = '" & lstAnalyte.Text

' get the results of the query
adoRecordset.LockType = adLockOptimistic
adoRecordset.CursorLocation = adUseClient
adoRecordset.Open strSQL, adoConnection

With shtData
.Select
.Cells(2, 1).CopyFromRecordset adoRecordset ' after this, shtData
is still blank!
End With

adoRecordset.Close
adoConnection.Close
Set adoRecordset = Nothing
Set adoConnection = Nothing
------------------------------------------------------------


Can anyone please tell me what I am missing?

I do not want to go back to the .GetRows/two-dimensional variant
array/TRANPOSE approach I was previously using!

Thanks in advance,

--emil