Concurrent 80004005 And 80040e14 With Ado Jet

  • Hi,


    I have an excel spreadsheet that uses ADO (Jet) to retrieve data from another, large excel spreadsheet.


    My sql query is as follows:

    Code
    1. Dim text, query As String
    2. 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:



    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:

    Code
    1. Cells(3, 1).CopyFromRecordset rs


    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:

    Code
    1. rs.Open query, cn, 1, 1


    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.

  • Re: Concurrent 80004005 And 80040e14 With Ado Jet


    You can also add:


    Code
    1. On Error Goto errHandler


    as well as:



    to the end. The Connection object might have a better description of the error in question.