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

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
 
S

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.
 
G

Guest

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top