CODEWITHSUNDEEP
excelvbatimeoutadorecordset
Dasal Kalubowila
Dasal Kalubowila

Reputation: 123

Query timeout expired Excel VBA ADODB Recordset

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 enter image description here

DBCont is defined in a separate module: enter image description here

Upvotes: 0

Views: 1772

Answers (1)

FunThomas
FunThomas

Reputation: 29652

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

Upvotes: 1

Related Questions