VB and VBA Users Source Code: Setting a command's timeout property in ADO
[
Home
|
Contents
|
Search
|
Reply
| Previous | Next ]
VB/VBA Source Code
Setting a command's timeout property in ADO
By:
Gavin Fisher
Email (spam proof):
Email the originator of this post
Date:
Friday, August 03, 2001
Hits:
1778
Category:
Database/SQL/ADO
Article:
In earlier versions of ADO, the CommandTimeout propery on the Connection or Command did not work as expected. The work-around was to set the Command Timeout property via the Properties collection. For example, the following code sets the "command time out" property to 1 min, but the command will take at least 2 mins to run. When the command times out, it raises an error. Dim oConn As ADODB.Connection Dim oCmd As ADODB.Command Dim oRS As ADODB.Recordset ' Create and Open a new ADO Connection Set oConn = New ADODB.Connection oConn.Open "Provider=sqloledb;Data Source=(local);Initial Catalog=pubs;User ID=sa;Password=;" ' Create a new ADO Command Set oCmd = New ADODB.Command oCmd.ActiveConnection = oConn oCmd.CommandType = adCmdText ' 2 mins (120 seconds) oCmd.CommandText = "Set NoCount On " & _ "WaitFor Delay '00:02:00' " & _ "Select * From authors Where au_id = ?" oCmd.Properties.Item("Command Time Out").Value = 60 ' 1 min (60 seconds) oCmd.Parameters.Append oCmd.CreateParameter("au_id", adChar, adParamInput, 11, "172-32-1176") ' Create and open an ADO Recordset using the command object Set oRS = New ADODB.Recordset oRS.CursorLocation = adUseClient oRS.Open oCmd ' Should raise an error in 1 min since command will take 2 mins
Terms and Conditions
Support this site
Download a trial version of the Excel Workbook Rebuilder
