Reputation: 123
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
DBCont is defined in a separate module:
Upvotes: 0
Views: 1759
Reputation: 29592
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