How To Create an Asynchronous Connection in ADO (194960)

SUMMARY

Often, operations involving a database can take some time to process a query, connect to a datasource, or retrieve results to a client. ADO provides a way to perform these operations asynchronously. With this ability, developers can also do other tasks during a long operation, such as displaying a progress dialog box, or offer the user the chance to cancel the operation.

An asynchronous connection can be established in ADO by using the Options parameter to the Connection object's Open method. The following example creates an asynchronous DSN-less connection using the SQL Server driver, waits for the connection to be established, then displays connection properties for that connection. It also demonstrates the use of the WITH statement to set multiple properties of the connection object. To use this example, you must have Microsoft Data Access Components (MDAC) version 2.1 or later installed, which is included in the data components of Visual Studio 6.0 or can be downloaded from:

http://msdn.microsoft.com/dataaccess

MORE INFORMATION

Create and execute the following program. Substitute your own SQL Server, database, user id, and password in the definition of the "lcConnString" variable. The program displays a WAIT WINDOW message until the connection is made, then displays the connection properties in a message box:

   * Begin code
   * The following program creates a DSN-less connection using
   * an existing SQL Server driver. Substitute your own SQL
   * server, database, user id and password parameters in the
   * definition of the lcConnString variable.
   *
   * The program then attempts to create an asynchronous connection
   * to this server, waiting in a loop until the Connection object's
   * state property is no longer attempting to connect.

   #DEFINE adAsyncConnect 16
   #DEFINE adStateConnecting  2
   #DEFINE adUseClient 3
   #DEFINE adModeReadWrite 3
   #DEFINE CR CHR(13)

   lcConnString = "driver={SQL Server};" + ;
      "SERVER=YourServerName;" + ;
      "DATABASE=YourDatabaseName;" + ;
      "UID=YourUserID; " + ;
      "PWD=YourPassword"

   oConnection = CREATEOBJECT("ADODB.Connection")
   WITH oConnection
      .CommandTimeout = 60
      .ConnectionTimeout = 30
      .ConnectionString = lcConnString
      .CursorLocation = adUseClient
      .Mode = adModeReadWrite
   ENDWITH

   oConnection.Open(,,, adAsyncConnect)

   DO WHILE oConnection.State = adStateConnecting
      WAIT WINDOW NOWAIT "Still connecting"
   ENDDO

   WAIT CLEAR
   ? "Connection completed"

   lcTitle = "Default connection properties"
   lcText = "Attributes: " + LTRIM(STR(oConnection.ATTRIBUTES)) + CR + ;
      "Command Timeout: " + LTRIM(STR(oConnection.CommandTimeout)) + CR + ;
      "Connection String: " + oConnection.ConnectionString  + CR + ;
      "Connection Timeout: " + ;
         LTRIM(STR(oConnection.ConnectionTimeout)) + CR + ;
      "Cursor Location: " + ;
         LTRIM(STR(oConnection.CursorLocation)) + CR + ;
      "Default Database: " + oConnection.DefaultDatabase + CR + ;
      "Isolation Level: " + LTRIM(STR(oConnection.IsolationLevel)) + CR + ;
      "Mode: " + LTRIM(STR(oConnection.Mode)) + CR + ;
      "Provider: " + oConnection.Provider + CR + ;
      "State: " + LTRIM(STR(oConnection.State)) + CR + ;
      "Version: " + oConnection.VERSION

   =MESSAGEBOX(lcText)
   * End code

The constants used were defined using the Microsoft Visual Basic 6.0 object browser.