Query timeout expired Excel VBA ADODB Recordset

Question

I get Query timeout expired when I run this code.

I have tried putting in timeout limits on the Conn and the Command from Query timeout expired when trying to run a short procedure but not successful.

You can see my comments in CAPS in the code for the timeout clauses. In SSMS it executes in about 45sec. My workbook has multiple queries using the same connection. This is the heaviest query (returning about 7k rows), so I'm pretty sure the problem is a query timeout, not connection.

Sub Units()
    Application.ScreenUpdating = False

    Dim rs4 As Object
    Set rs4 = CreateObject("ADODB.Recordset") '04Unit2

    Dim sqlstr04 As String
    sqlstr04 = "select * from dbo.[04Units]"

    Sheet17.Cells.Clear
    Call connectDatabase
                                    
    rs4.Open sqlstr04, DBCONT
    DBCONT.commandtimeout = 120   'CONNECTION TIMEOUT
    rs4.commandtimeout = 120        'RECORDSET QUERY TIMEOUT
    'Debug.Print sqlstrledger03

    For intColIndex = 0 To rs4.Fields.Count - 1
        Sheet17.Range("A1").Offset(0, intColIndex).Value = 
        rs4.Fields(intColIndex).Name
    Next
                   
    Sheet17.Range("A2").CopyFromRecordset rs4
End Sub

Error

DBCont is defined in a separate module:

Please do not paste code in images, you can easily copy paste the code in the question as text (it's easier to do it, we can copy the code and debug it). — Raymond Wu, Nov 26 '21 at 16:07
What happens if you try returning a very small subset of the data? E.g. `Select Top 1 * from dbo.[04Units]` does it complete? — Ryan Wildry, Nov 26 '21 at 16:15
You need to set the timeout values *before* the SQL command is executed - in your case move the 2 timeout statements on top of the `rs.Open`-command. — FunThomas, Nov 29 '21 at 12:54
@FunThomas, if you can actually provide that as an Answer, the bounty is yours. — Dasal Kalubowila, Dec 01 '21 at 13:00

score 1 · Answer 1 · answered Dec 01 '21 at 13:16

The Open-method of your recordset already triggers the execution of the SQL-command. Your TimeOut-commands must be set before the command is executed.

Simply change the order of your code and set the Timeout before you execute the SQL command should do the trick (I think a recordset object has no timeout method)

DBCONT.commandtimeout = 120   'CONNECTION TIMEOUT
rs4.Open sqlstr04, DBCONT

Query timeout expired Excel VBA ADODB Recordset

1 Answers1