Syntax when setting ado rs or conn timeout (not adp)

[amos]

I'm trying to use ado to test connectivity with a sql server, and would
like the timeout value to be pretty short (5 seconds or so). But I can't
find the right way to set the timeout value. All of my tests fail after
30 seconds or so if the sql server is stopped. Access 2003 mdb.

Public Function GetUserIDFromDBLogin()
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset

Set cnn = New ADODB.Connection
Set rs = New ADODB.Recordset

With cnn
.ConnectionString = "Driver=SQL Native Client;" _
& "Server=mybox\SQLEXPRESS;" _
& "Database=mysqldb;UID=sa;pwd="
.CommandTimeout = 5
.Open
End With

With rs
.Source = "Select * FROM tlkpMyLittleTable"
Set .ActiveConnection = cnn
.CursorType = adOpenKeyset
.Open
MsgBox "Count " & .RecordCount
End With
End Function

 

[Sylvain Lafontaine]

Well, if you take the precaution of writing « not adp » in your subject, I
don't know why this post has been crossposted to in
m.p.access.adp.sqlserver.

In your case, you could try .Connection Timeout instead of .CommandTimeOut.

 

[Guest]

I'm trying to use ado to test connectivity with a sql server, and would
like the timeout value to be pretty short (5 seconds or so). But I can't
find the right way to set the timeout value. All of my tests fail after
30 seconds or so if the sql server is stopped. Access 2003 mdb.

Public Function GetUserIDFromDBLogin()
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset

Set cnn = New ADODB.Connection
Set rs = New ADODB.Recordset

With cnn
.ConnectionString = "Driver=SQL Native Client;" _
& "Server=mybox\SQLEXPRESS;" _
& "Database=mysqldb;UID=sa;pwd="
.CommandTimeout = 5
.Open
End With

With rs
.Source = "Select * FROM tlkpMyLittleTable"
Set .ActiveConnection = cnn
.CursorType = adOpenKeyset
.Open
MsgBox "Count " & .RecordCount
End With
End Function