ADODB Recordset timing out but not giving an error

[Zabman]

Hi,

I have a macro that connects to a DB2 database and returns a recordset that I complete subsequent actions with. I have found that longer queries are causing excel to stall - giving me the 'this program is not responding' and forcing me to restart excel. I have played around with the command timeout setting in the connection and extending this out has given me the results I require.... howerer.... I am not happy with this as it still has the problem if query times increase again.

I would expect an error to be thrown up which I can deal with instead of the program just crashing but this isnt happening. I have confirmed there are no 'On Error goto' things so I am stumped as to how to resolve this.

I am creating the recordset like this:
DBRS.Open SQL, CN, adOpenStatic, adLockOptimistic, adCmdText

where SQL is my query, and CN is my connection.

Can anyone help?

 

[Paul Ked]

I am just completing an app that is querying a DB with 30,000 records of 24 columns and a read of all like this:

    With CreateObject("ADODB.Recordset")
        .Open "SELECT * FROM PaidJobs", "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Sett.Cells(38, 2) & "KKDB.accdb;"
        DBPJ.Range("A2").CopyFromRecordset .DataSource
        .Close
    End With

completed in around 2.6 seconds and a search like this:

    Dim rst As Object: Set rst = CreateObject("ADODB.Recordset")
    rst.Open "SELECT * FROM " & Tbl & " WHERE " & Fld & " LIKE " & Fnd & ";", _
        "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Sett.Cells(38, 2) & "KKDB.accdb;"
    Srch.Range("A2").CopyFromRecordset rst.DataSource
    rst.Close: Set rst = Nothing

completed in 1,1 seconds.