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

  • Thread starter Thread starter amos
  • Start date Start date
A

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
 
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.
 
amos said:
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
Click to expand...
 
Back
Top