Recordset In VBA – PT2

There are times when you will need to access your data programatically and you will have to create the recordset in VBA rather than from the built in data connection Access provides.

ADO is a superset of DAO in functionality point of view.

The primary benefit of using the ADO recordset object is:
-Ease of use
-High speed
-Low memory overhead.
-Small disk footprint

In this video we are talking about the ADO recordset In VBA and how to formulate one easily.

If you really want some more detailed explanation of DAO, ADO, OLEDB, and ODBC look at this link, http://www.ozgrid.com/forum/showthread.php?t=28221.

The default recordset object, if you don’t choose one is ADO. You would do well to be specific in your declarations, like:
ADO.Connection
DAO.Connection

Here is a good flow for data access with ADO:
-Connect to the data source
-Specify a command to get access to the data source.
-Execute the command.
-Store the rows returned in a recordset that can be easily navigated, managed, and updated.
-Update the data source with any changes made to the data.
-Detect any errors which happen as a result of making a connection or command.

There are 3 main components of the ADO Object Model: The Connection, Recordset, and Command. We are going to specifically focus on the Connection and Recorset objects.

For ADO connection string syntax, I suggest you visit this link: http://connectionstrings.com/

Public Sub ADORecordset()
    'Must set a reference to Microsoft ActiveX Data Objects 2.5 Library
    
    'Late Binding
    cnn = CreateObject("ADODB.Connection")
   
    'Early Binding
    'Dim cnn As ADODB.Connection
    'Dim rst As ADODB.Recordset
    
    'Set your connection equal to the current one...
    'Since Access gives you the connection, you don't have to specify the special & _
        parameters.  Use http://connectionstrings.com as an aid for specific connection params.
        
    Set cnn = CurrentProject.Connection
    
    'Set your recordset...
    Set rst = CreateObject("ADODB.Recordset")
    'Set rst = New ADODB.Recordset
    
    'Open your recordset...
    rst.Open "Customers", cnn
    
    rst.MoveFirst
    
    Do Until rst.EOF
        Debug.Print rst.Fields("CompanyName")
        rst.MoveNext
    Loop
    
    'Garbage collection
    rst.Close
    cnn.Close
    
    Set rst = Nothing
    Set cnn = Nothing
    
End Sub

<< Recordset In VBA – PT1 | Recordset In VBA – PT3 >>

By the way, if you got or are getting value from the VBA information, please give me a tip, thanks!