Timeout expired error in VBA?

Question

The below query after running for a while is showing a "Timeout expired error". I even set the "cmd.CommandTimeout = 3600", but after running for 1 minute I get the "Timeout expired error"

Sub ConnectSqlServer()

        Dim conn As ADODB.Connection
        Dim rs As ADODB.Recordset
        Dim sConnString As String
        Dim cmd As New ADODB.Command
        ' Create the connection string.
        sConnString = "Provider=SQLOLEDB;Data Source=server1;" & _
                      "Initial Catalog=database1;" & _
                      "Integrated Security=SSPI;"


         cmd.ActiveConnection = conn
        ' Create the Connection and Recordset objects.
        Set conn = New ADODB.Connection
        Set rs = New ADODB.Recordset

        cmd.CommandTimeout = 3600
        ' Open the connection and execute.
        conn.Open sConnString
        Set rs = conn.Execute("select column1, column2 from table1;")

        ' Check we have data.
        If Not rs.EOF Then
            ' Transfer result.
            Sheets(1).Range("A8").CopyFromRecordset rs
        ' Close the recordset
            rs.Close
        Else
            MsgBox "Error: No records returned.", vbCritical

        End If

        End Sub

Please let me know what need to changed in code to make it run for long time and show the results.

I wonder if you need to set the timeout on the connection instead of the command. — Jacob H, Feb 15 '18 at 21:19
See answer here https://stackoverflow.com/questions/17574524/adodb-query-timeout — QHarr, Feb 15 '18 at 21:21
It is not set on the connection, it's set on the command `cmd.CommandTimeout = 3600`. Also, the default for the command timeout is 30 so if it's going 60 seconds before timeout (not sure if you actually timed this) then something is off. — Jacob H, Feb 15 '18 at 21:22
@JacobH Yes. Missed the thread. I remember being confused by the timing part when using VBA SQL to handle recordsets and wondering why to avoid timeout I had to use 0! — QHarr, Feb 15 '18 at 21:26

score 1 · Accepted Answer · answered Feb 15 '18 at 21:23

1

So the answer is a two parter.

As mentioned in the comments set it on the Connection object

And to have a longer time set this to 0. Seems a little counter intuitive at first.

Connection.CommandTimeout = 0

I have had this with queries I have run before.

There is also discussion here:

ADODB query timeout

answered Feb 15 '18 at 21:23

QHarr

83,427
12
54
101

If you don't mind, can you edit my code and show me how where to edit the code? Just for a clearer idea. – sat Feb 15 '18 at 21:33
1

put conn.CommandTimeOut = 0 Set before opening the connection, – QHarr Feb 15 '18 at 21:34

Timeout expired error in VBA?

1 Answers1