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
-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:
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
Sign Up For A FREE MS Access VBA Video Tutorial Subscription. You will get over 27 tutorial videos that will help you do your job better! Start Yours Now At: