Timeout on SQL and VB script

Question

I am trying to avoid a timeout situation happening. At present the script runs 100% on the test environment, but everything is running local. Therefore now installed on the live environment, it does take a little longer. However it would appear VB timeout default is set to 30 secs. However not being 100% familiar with VB unlike with SQL, then I am unsure on the code to set it. Current code is as follows:

Dim strFile As String
Dim strCon As String
Dim strSQL As String
Dim dateRows As Variant
Dim i As Integer
Dim today As Date
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

 strFile = Workbooks(1).FullName
 strCon = "Provider=SQLOLEDB.1; Data Source=ABC;Initial    catalog=ABC;Integrated Security=ABC;"

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
 range1 = Sheets("Line data ( Do not alter )").Range("AA9")
 cn.Open strCon
 strSQL = "Select "order by userid, appointmentdate "
 rs.Open strSQL, cn

 Sheets("Line data ( Do not alter )").Range("AA9").CopyFromRecordset rs

cn.Close
Set rs = Nothing
Set cn = Nothing
End Sub

I found code which states comm.CommandTimeout=10, but unsure where to put it..

Any help appreciated.

In your case it would cn.CommandTimeout=10 (or whatever you want to set timeout time to) since cn is the variable for your connection object and the CommandTimeout is a property of this object. Since you are using early binding, you can see all the properties of the Connection object by typing . after cn. — Scott Holtzman
– Scott Holtzman, Commented Aug 9, 2017 at 13:20
Perfect - thank you. Do you know how to code a button which would force a quit on the sql script if I set it to 0 and it takes too long? — Processit
– Processit, Commented Aug 9, 2017 at 13:31
If I set it to 0 and it takes too long - just set for it whatever time you feel "takes too long" and you're good. You can also error trap the message if you want to make it cleaner and move on without passing an error to user. — Scott Holtzman
– Scott Holtzman, Commented Aug 9, 2017 at 15:51

Rik Sportel · Accepted Answer · 2017-08-09 14:26:48Z

1

In ADODB you have 2 timeouts:

The Connection timeout as documented here
The Command timeout as documented here

Connection timeout triggers when trying to open the connection. Command timeout when you're running the command.

If you're receiving an error on the cn.Open line: You should set cn.ConnectionTimeout = someSeconds

If you're receiving it on the rs.Open line: You should set cn.CommandTimeout = someSeconds

answered Aug 9, 2017 at 14:26

Rik Sportel

2,6781 gold badge17 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Processit Over a year ago

Thanks that makes sense,.

Collectives™ on Stack Overflow

Timeout on SQL and VB script

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related