CopyFromRecordset does nothing

[E Harris]

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

 

[Tim]

Emil,

It seems as though the code that you are using should
work, although I tested the method with Excel 2000. I'd
hate to ask but are you sure your query is returning data?
Can you add a line of code such as
MsgBox adoRecordset.RecordCount
MsgBox adoRecordset.Fields(0).Value
to quickly verify that data is being returned? Also your
code doesn't specify what shtData is, but I'd assumed that
it's defined somewhere otherwise Excel would have given an
error message about that, thus my next question is are you
sure you're looking at the right sheet for results? The
fact that Excel doesn't generate any errors seems to
indicate that Excel is functioning as it should (you don't
have a on error resume next, that's not shown below right?)
I can't spot any errors with your code and can't reproduce
the error so it's making it a little difficult, but I'd
look at the steps above to help debug and pin down the
error.

good luck,
Tim

-----Original Message-----
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.

 

[onedaywhen]

You would get the result you described (i.e. a blank range and no
error ) if the recordset is EOF = True, e.g. you have already used
CopyFromRecordset and not moved issued a MoveFirst or your recordset
contains zero rows (perhaps due to a filter). However, there's nothing
in your code to suggest this so if you didn't snip and didn't do
anything in the Immediate Window before the CopyFromRecordset then I'm
stumped.

 

[E Harris]

Emil,

It seems as though the code that you are using should
work, although I tested the method with Excel 2000. I'd
hate to ask but are you sure your query is returning data?
Can you add a line of code such as

Yes, I thought I was sure, because I was using the same code with my
previous method of getting the ado data, but then when I tried:

MsgBox adoRecordset.RecordCount
MsgBox adoRecordset.Fields(0).Value

I get a value for the RecordCount, but gett an error when it tries to
show the .Fields(0).Value-- Run Time Error '3021' saying either EOF or
BOF is true, or record deleted, etc....

to quickly verify that data is being returned? Also your
code doesn't specify what shtData is, but I'd assumed that
it's defined somewhere otherwise Excel would have given an
error message about that, thus my next question is are you
sure you're looking at the right sheet for results? The
fact that Excel doesn't generate any errors seems to
indicate that Excel is functioning as it should (you don't
have a on error resume next, that's not shown below right?)

All of the above are checked and not the problem.

I can't spot any errors with your code and can't reproduce
the error so it's making it a little difficult, but I'd
look at the steps above to help debug and pin down the
error.

good luck,
Tim

Thanks so much for your help.

So now I need to figure out why EOF is true.

 

[E Harris]

I will name my first-born after you.

Seeing the "MoveFirst" triggered the realization for me-- my old
..GetRows() method was still interlaced with my new code. I major
'doh!' moment for me. I somehow omitted it from the post of my code
here.

Thanks both of you Tim and onedaywhen.

I don't know when it would've hit me that was the issue if I had not
have had your input.

-- emil

 

[onedaywhen]

I will name my first-born after you.

Rather than use my alias, you'd better use Jamie, which happily works
for both genders. My 'second-born' is due on Sunday and I hope it's a
boy because it's being named Chip Stephen Tom <g>.

--