Hi,
I have an excel spreadsheet that uses ADO (Jet) to retrieve data from another, large excel spreadsheet.
My sql query is as follows:
Dim text, query As String
query = "SELECT * FROM [Tracker$] WHERE `Part Desc` LIKE `%" & text & "%` ;"
This results in the following string (tested to pull the value for the text variable correctly)
"SELECT * FROM [Tracker$] WHERE `Part Desc` LIKE `%_text here_%` ; "
This is the code that I'm having trouble with:
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & "E:\ECN.xls" & ";" & _
"Extended Properties=""Excel 8.0;HDR=YES"""
rs.Open query, cn, 1, 1
Cells(3, 1).CopyFromRecordset rs
Display More
Now, if I enter a query "text" that should return results, I get the first ~1282 rows, and excel errors out with 80004005 (no description given) at this line:
Unfortunately, this isn't a problem with a specific row or the data in the rows, because, If several of the first "result" rows are deleted from the source file, I will still get the first 1282 rows (including rows not previously returned in the first query). Hence, copying from the record set stops due to the number of rows copied, not because of the data contained in those rows. The spreadsheet does not contain any OLE Objects or array data.
Also, if I enter a query that should not return any results, I get a 80040e14 (no description given) at this line:
I have tried this on different computers, with different versions of Windows and Excel, and I still get a variation of the same problem.
Note: strangely enough, this only happens when query for the Part Desc column. All other columns, including a simple "SELECT * FROM [Tracker$]" (returns > 8000 rows) works perfectly.