VBA query times out, ignores connection timeout setting

I have a VBA form that is timing out accessing a MS SQL server view. I have two button on this form. On runs a report preview a la the standard wizard setup. The other run code in the form where I open the view and loop through the rows outputting a text transaction file.

The view takes about 1:14 minutes to run. Under File --> Connection --> advanced, I set the timeout to 120 seconds and that took care of the report preview. However, the query coded in my VBA form times out after 30 seconds, regardless of what I set the connection timeout to. Whats up? Here's my query call:

    Dim rs As New ADODB.Recordset

    rs.Open "select * from _vwPaActuaryActive", GetADoConnectString("PA"), adOpenForwardOnly, adLockReadOnly, adCmdText

LVL 1
jmarkfoleyAsked:
Who is Participating?
 
andrewbleakleyConnect With a Mentor Commented:
Sorry
Set rs = cmd.Execute
I don't have a VB IDE on this machine

0
 
andrewbleakleyCommented:
The time you have set is the connection timeout (the time after which it assumes the SQL server is not present if it receives no response). You will need to use an ADODB.Command object and set it's CommandTimeout property as below.

Dim cn as New ADODB.Connection
Dim cmd as New ADODB.Command
Dim rs As New ADODB.Recordset
cn.Open( GetADoConnectString("PA") )
cmd.ActiveConnection = cn
cmd.CommandType = adCmdText
cmd.CommandTimeout = 120
cmd.CommandText = "select * from _vwPaActuaryActive"
rs = cmd.Execute
rs.Close()
0
 
jmarkfoleyAuthor Commented:
Compiling your example gives me: "Invalid use of property" on the line: rs= cmd.Execute

suggestions?
0
 
jmarkfoleyAuthor Commented:
That did it! Thanks. One of these days I'm going to have to read up on 'set'. Once upon a time, there wasn't a difference between 'a = b' and 'set a = b'.

0
 
andrewbleakleyCommented:
a = b assigns a VALUE to a variable
set a = b assigns an OBJECT to a variable
0
All Courses

From novice to tech pro — start learning today.