The Recordset Object

The Recordset Object is one of the most straighforward ways to retrieve data from a database.

1. Add a Reference to the most recent Microsoft ActiveX Data Objects Library (ADODB).
2. In the click event of the btnOpenRecordset button, set a String Variable to the Connection String for the Connection Object of ActiveX Data Objects.
3. Open a Database Connection using an ActiveX Data Objects Connection Object.
4. Set a String Variable to the SQL of the Structured Query Language (SQL) Statement to run against the database.
5. Open a Recordset using an ActiveX Data Objects Recordset Object.
6. Access the Fields in the Recordset.
7. Close the Recordset.
8. Close the Database Connection.

1. Right Click on the References folder and choose Add Reference.
   
2. Select the COM menu item on the left hand side, and add a Reference to the most recent ActiveX Data Objects library. In the above screenshot, the most recent ADO library is the Microsoft ActiveX Data Objects 6.1 Library.
   

1. In the click event of the btnOpenRecordset button, add the code to open a recordset.
   
   
   

   Private Sub btnOpenRecordset_Click(sender As Object, e As EventArgs) Handles btnOpenRecordset.Click
    On Error GoTo Errorhandler
   
      Dim oConnection As ADODB.Connection
      Dim oRst As ADODB.Recordset
      Dim sConnectionString As String
      Dim sSQL As String
   
      oConnection = New ADODB.Connection
      oRst = New ADODB.Recordset
      ServerName: 127.0.0.1 (This IP Address is for locally installed databases.)
      UserName: usr_SQLServerLoginName
        Always create a new, separate Login Name for each database.  The sa Login Name should only be used for database administration.
      UserNamePassword: Use strong passwords that have upper and lower case characters, numbers and special characters such as !, #, %, & or *
      '--------------------------------------------------------------------------------------   sConnectionString = "Provider=MSOLEDBSQL;Server=ServerName ;Database=DatabaseName;User ID=UserName;Password=UserNamePassword"
   
      SELECT ID, Company, [Last Name], [First Name] FROM [Northwind].[dbo].[Customers] ORDER BY ID
      '--------------------------------------------------------------------------------------
      sSQL = "SELECT FieldName1, FieldName2, FieldName3, FieldName4 FROM MyTable ORDER BY Field1"
                          
         oConnection.Open(sConnectionString)
   
            oRst.Open(sSQL, oConnection)
             Msgbox(oRst.Fields("FieldName3").Value.ToString)
            oRst.Close
   
         oConnection.Close
   
      oRst = Nothing
      oConnection = Nothing
   
    Exit Sub
    Errorhandler:
      Msgbox(Err.Number & "-" & Err.Source & ": " & Err.Description)
   End Sub