Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
CopyFromRecordset does nothing
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
CopyFromRecordset does nothing
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. 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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
CopyFromRecordset does nothing
"Tim" wrote in message ...
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
CopyFromRecordset does nothing
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) wrote in message . com... 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. -- |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
CopyFromRecordset does nothing
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
CopyFromRecordset Problem | Excel Discussion (Misc queries) | |||
Format data in Excel after using copyfromrecordset | Excel Discussion (Misc queries) | |||
copyfromrecordset performance | Excel Programming | |||
CopyFromRecordset - Is there a way to filter data that's copied? | Excel Programming | |||
Copyfromrecordset Bug ? | Excel Programming |