BUG: Refresh Method of Data control Resets QueryTimeout (176258)

SYMPTOMS

The QueryTimeout property of the Database object determines the number of seconds to wait before a timeout occurs when a query is executed. If the DefaultType property of the Data control is set to dbUseODBC, then the QueryTimeout property of the Database object is reset to the default value of 30 when the Refresh method of the Data control is performed.

RESOLUTION

There are several ways to work around this problem, two of which are listed here:

Modify the DefaultType property of the Data control to dbUseJet.
Use the Remote Data Control (MSRDC) rather than the Data control.

However, if it is necessary to use the Data control with an ODBC data source, the following workaround can be used.

Note You must change UID <username> and PWD <strong password> to the correct values before you run this code. Make sure that UID has the appropriate permissions to perform this operation on the database.

   Private ws As Workspace
   Private db As Database

   Private Sub Command1_Click()
   Dim con As String
   Dim sql As String

       con = "ODBC;DSN=your_dsn;UID=<username>;PWD=<strong password>;DATABASE=pubs"
       Set ws = DBEngine.CreateWorkspace("", "", "", dbUseODBC)
       Set db = ws.OpenDatabase("", Connect:=con)

       db.QueryTimeout = 60

       sql = "SELECT * FROM Authors"
       Set Data1.Recordset = db.OpenRecordset(sql, dbOpenDynaset)

       MsgBox db.QueryTimeout
   End Sub

STATUS

Microsoft has confirmed this to be a bug in the Microsoft products listed at the beginning of this article. We are researching this bug and will post new information here in the Microsoft Knowledge Base as it becomes available.

MORE INFORMATION

Steps to Reproduce Behavior

Start Visual Basic and create a new Standard EXE. Form1 is created by default.
Add a Data control and a CommandButton to Form1.

Add the following code to the General Declarations section of Form1.

Note You must change UID <username> and PWD <strong password> to the correct values before you run this code. Make sure that UID has the appropriate permissions to perform this operation on the database.

      Private Sub Command1_Click()

          Data1.Database.QueryTimeout = 60

          Data1.RecordSource = "SELECT * FROM Authors"
          Data1.Refresh

          MsgBox Data1.Database.QueryTimeout

      End Sub

      Private Sub Form_Load()

          Data1.DefaultType = dbUseODBC
          Data1.Connect = "ODBC;DSN=your_dsn;UID=<username>;PWD=<strong password>;DATABASE=pubs"
          Data1.Refresh

      End Sub

Run the project.
Click on Command1 and note that the QueryTimeout is the default value of 30, rather than 60 that it was assigned to.