How to open ADO Connection and Recordset objects by using Visual Basic .NET

This article was previously published under Q308047
This article has been archived. It is offered "as is" and will no longer be updated.
Caution ADO and ADO MD have not been fully tested in a Microsoft .NET Framework environment. They may cause intermittent issues, especially in service-based applications or in multithreaded applications. The techniques that are discussed in this article should only be used as a temporary measure during migration to ADO.NET. You should only use these techniques after you have conducted complete testing to make sure that there are no compatibility issues. Any issues that are caused by using ADO or ADO MD in this manner are unsupported. For more information, see the following article in the Microsoft Knowledge Base:
840667 You receive unexpected errors when using ADO and ADO MD in a .NET Framework application
This article demonstrates different ways to create ActiveX Data Objects (ADO) Connection and Recordset objects in Visual Basic .NET. Note that these objects are ADO objects and not ADO.NET objects.

back to the top

Create ADO Connection and Recordset Objects in Visual Basic .NET

  1. Create a new Visual Basic .NET Windows Application project. Form1 is created by default.
  2. From the Project menu, click Add Reference.
  3. On the COM tab, click Microsoft ActiveX Data Objects 2.x Library, and then click Select.
  4. Add the following code to the General Declarations section of Form1:
    Dim cn As New ADODB.Connection()Dim rs As New ADODB.Recordset()Dim cnStr As StringDim cmd As New ADODB.Command()					
  5. The following code illustrates two different methods to open an ADO Connection object. Each of these methods connect to a Microsoft SQL Server Pubs database and can be added to the Form1 Load event.

    Note User ID <user name> must have permissions to perform these operations on the database.
    ' Modify this connection string to reflect your server and logon information.' Store the connection to a variable to be used throughout this example.cnStr = "Provider=SQLOLEDB;Initial Catalog=Pubs;Data Source=servername;" & _   "User ID=<username>;Password=<strong password>;"        ' 1. Connect through the Connectionstring = cnStrcn.Open()cn.Close()' 2. Connect through the Connection object's Open					
  6. The following code illustrates three different ways to open an ADO Recordset object. Each of these methods connect to a Microsoft SQL Server Pubs database and can be added to the Form1 Load event:
    ' 1. Open Recordset through the Execute method of the Connection = cn.Execute("Select * from Authors")rs.Close()cn.Close()' 2. Open Recordset through the Command.Execute = cncmd.CommandText = "Select * from Authors"rs = cmd.Executers.Close()cn.Close()' 3. Open Recordset without a Connection"Select * from Authors", cnStr)rs.Close()' Release the objects to free = Nothingcn = Nothing     					
  7. Modify the Connection string where indicated to properly connect to your SQL Server.
  8. Press the F11 key to step through the code, and notice the different ways to create a Connection or Recordset object.
NOTE: When you set the Recordset object's ActiveConnection property to an actual string (as opposed to a Connection object), as follows
rs.ActiveConnection = "Provider=SQLOLEDB;Initial Catalog=Pubs;Data Source=servername;User ID=<username>;Password=<strong password>;				
the following build error occurs in Visual Basic .NET:
Value of type 'String' cannot be converted to 'ADODB.Connection'.
back to the top
For additional information, click the article number below to view the article in the Microsoft Knowledge Base:
168336 How To Open ADO Connection and Recordset Objects
 back to the top

For more information about the advantages of the new DataReader and DataAdapter objects in ADO.NET, visit the following Microsoft Developer (MSDN) Web site:

Article ID: 308047 - Last Review: 12/06/2015 05:42:26 - Revision: 5.0

Microsoft Visual Basic .NET 2003 Standard Edition, Microsoft Visual Basic .NET 2002 Standard Edition

  • kbnosurvey kbarchive kbhowtomaster KB308047