Using the Recordset with MS Access and ADO

Introduction

In "Connecting to MS Access with ADO," the Connection object was described with some details. The Open and Close methods of a connection object were described. Also described were the ADO Version, Connection timeout, Connection string and Connection State properties and their usage. The Properties collection related to the Connection revealed a great deal of information on the connection for the source used. The properties and methods discussed so far are only a small part of all the methods and properties; a future tutorial will describe some of the other properties.

This tutorial deals with the next important object, the Recordset object. If connection gave you access to the database, the recordset will give you access to the data. The result of a query run against a database is stored in a recordset. What you must do is to examine the recordset to find out what information is contained in your data. The recordset does a little more than that; you can sort a recordset, you can filter it and you can even update it. Now let's go back and take a look at the ADO Object model presented in the first tutorial, Part 1.

Starting from ADO 2.5, the ADO object model consists of five separate parts, each of which concentrates on a single high level object. The five objects of this model are the Connection, Command, Recordset, Record and the Stream. The last of these gives I/O access to data stored on a local machine. In the next couple of tutorials I will be exploring each of these objects in greater detail, providing concrete and full examples of their usage.

This next picture shows the ADO object model from my earlier article. In this tutorial I will be concentrating on the Recordset, because the recordset is our main link to the data on the database. You may also notice the Record object's intimate association with the recordset object.

As you can see from the above diagram, the recordset object exposes a Fields collection with a number of fields. The item field would correspond to a column in a table or a query. Hence the recordset is a collection of all the columns for that row or the set you requested in your query. This object has some 30 properties and an equally impressive 25 or so methods. This tutorial at this level of presentation will only look at some of the properties and a couple of methods.

In order to understand the properties, methods and events, I have no better recommendation than the Object Browser. This is even better than a book, because the object browser directly relates to the version you are using. This picture shows the Recordset object as revealed by the Object Browser. We will be describing only some of the properties and methods in this tutorial and several others in forthcoming tutorials.

We will be following step-by-step the line items shown here for this tutorial. Basically, you'll be opening a connection and opening a recordset. The data is represented by rows of information --  the recordset. The rows have columns of information for each row, called the fields. Each column represents a field in the fields collection. The summary of steps you will be following are as follows.

• Create a MDB file, add a form, a button, etc.
• Establish a reference to ADO.
• Write code to open ADODB connection, recordset and close open objects.
• Review recordset properties using code.
• Take a look at the Recordset object and the User interface in MS Access.
• Summary.

Please follow the steps indicated in the first part of this tutorial. Just open your MS Access application and choose to Create a New File... which prompts you for a number of options. Choose to create a blank database. For this tutorial, I used the same AdoTest.mdb file as I did in the earlier article. This time I will be writing code to the Form's load event.

Open the form in design view, by first highlighting Form 3 (in addition to Form1, I also have a Form2 which is not used in this tutorial) and then choosing Design View. Right click on the form on the little square at the top left corner of the form in the design view (which darkens when you click). From the drop-down menu click on Build Event... and in the Choose Builder pop-up that comes up click on Code Builder.

You will get to the Microsoft Visual Basic Editor screens consisting of Properties - Form3, Project AdoTest, and the Form_Form3(code). If some of them are not visible you can display them using the View menu in the Microsoft Visual Basic Editor. If you want to go back to the form just click on the MS Access icon on the tool bar. The code page for Form3 is called AdoTest - Form_Form1(code). This is where the code for the form and for any controls on the form are written. Presently there is no code except an empty Form_Load() event.

Establish a reference to ADO

This is an important step where you will establish a reference to the Microsoft ActiveX Data objects library, which allows you to use the methods, properties and events related to these objects. In the Microsoft Visual Basic screen go to Tools, click on it (do not right click), and from the drop down click References (it is usually the first one in the list). This brings up the window References - AdoTest as shown. You will see references to a whole lot of ADOs. Here the ADO 2.8 library is chosen; click OK. With this we can use the msado15.dll.

Write code to Open ADODB connection, Recordset and close open objects

I will be showing you two ways to create recordsets, one explicit and the other implicit. The following discussion should make you comfortable with seeing the differences (notice the highlighted statements in the code).

Remember that intellisense will guide you along the way. Just type in the code window, ADODB. (after the period pause to see the drop-down, do not be hasty and type away) and you will get a drop-down as shown, giving the choice of the ADODB object type you want to create. It could be a connection, it could be a command, and so forth. Insert the following code as shown in the next paragraph in the form's Load event.

Now as discussed in my earlier article, declare a string and assign the connection string you copied from the UDL file. With this you can now open the connection. Now you need to give a source for the recordset (declared with the name rst) which in this case is chosen to be an SQL query (statement run against the Northwind database's table 'Employee'). Now you can type rst. (and pause here after the period) and you get immediate help as a tool tip as shown in this picture.

What the tool-tip saying is that you need specify a number of arguments for the recordset to open, the first of which is the source of the data (in this case the SQL Statement), the second is the Connection which is used, the third is the CursorType (these are pointers to the record will be discussed later). The fourth - LockType, deals with the way data is to be made available from the point of view of whether others can make changes to the data or not while it is being requested by you, etc. The fifth is really the options for how you may choose the source of data. Here an SQL statement has been chosen as the source, whereas it could be a table, or a stored procedure in the database under study. In order to open a recordset you may not need all of the parameters, as you shall shortly see.

At present do not concern yourself with all of these different arguments and the variety of values each of them can assume. These will be dealt with in detail in future tutorials. In the code that is shown only the first two arguments are shown, which means the others will assume their defaults. In this explicit way of opening the recordset, you are providing the source of the data as well as the connection object.

Again the code is written without any frills so that you may focus on the essentials. You may notice towards the end of the code, you are setting the two objects to nothing in the shown order. This will remove the objects from the memory. Alternately you may also close the connections but they will remain in memory.

Private Sub Form_Load()
Dim conn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim strg As String
strg = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:

Program FilesMicrosoft OfficeOFFICE11SAMPLES

Northwind.mdb;Persist Security Info=False"
conn.Open strg
Dim strsql As String
strsql = "Select EmployeeID, LastName, FirstName, City from Employees"
rst.ActiveConnection = conn
'to open a recordset a source (strsql) and an ActiveConnection 

(conn) are provided.  rst.Open strsql, conn MsgBox ("open")
Set rst = Nothing
Set conn = Nothing
End Sub

Use the connection string directly to open the recordset 

Do you always need to create a connection object and then a recordset object? No. You could open a recordset without creating the connection object. This is the way to open the recordset implicitly, as the following code shows in the click event of a button on the same form. Although this works, the performance is not as good as in the previous case, since each time a recordset is opened a new connection is made; in the explicit method of opening, the same connection can be used for another recordset.

Private Sub Command0_Click()
Dim rst1 As New ADODB.Recordset
Dim strg As String
strg = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:

Program FilesMicrosoft OfficeOFFICE11SAMPLES

Northwind.mdb;Persist Security Info=False"
Dim strsql As String
strsql = "Select EmployeeID, LastName, FirstName, City from Employees"
'to open a recordset a source (strsql) and a connections string 

(strg) are provided.  rst1.Open strsql, strg MsgBox 

("rst1 is open")
Set rst1 = Nothing
End Sub 

You saw earlier in the Object Browser the driver specific recordset properties for the referenced ADO library. We will look at the same using code since we have been able to open a recordset.

Add some code to modify the earlier code as follows. We will find certain other properties of the recordset object, such as the following:

• State
• Status
• CursorType
• CursorLocation
• ActiveConnection
• AbsolutePosition
• LockType
• RecordCount

You can get a great deal of help from intellisense. The variable strr just gathers up all the strings and finally places them in the text box. Some design time properties have been set for the text box in formatting the output.

Option Compare Database
Private Sub Form_Load()
Dim conn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim strg As String
strg = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:

Program FilesMicrosoft OfficeOFFICE11SAMPLES

Northwind.mdb;Persist Security Info=False"
conn.Open strg
Dim strsql As String
strsql = "Select EmployeeID, LastName, FirstName, City from Employees"
rst.ActiveConnection = conn
rst.Open strsql, conn
Dim strr As String
strr = ""
strr = strr + "Recordset Status is: " & rst.Status & vbCrLf
strr = strr + "Recordset's Cursor location is:  " & 

rst.CursorLocation & vbCrLf
strr = strr + "Recordset's LockType is: " & rst.LockType & vbCrLf & 

vbCrLf
strr = strr + "Recordset's RecordCount is: " & rst.RecordCount & vbCrLf
strr = strr + "Recordset's State is: " & rst.State & vbCrLf
strr = strr + "Recordset's CursorType is: " & rst.CursorType & vbCrLf
strr = strr + "Recordset's AbsolutePosition is:  " & 

rst.AbsolutePosition & vbCrLf & vbCrLf
strr = strr + "Recordset's ActiveConnection is: " & 

rst.ActiveConnection & vbCrLf
Text1.SetFocus
Text1.Text = strr
Set rst = Nothing
Set conn = Nothing
End Sub

When you run this code you will see the following window.  You will see that the properties returned numerical values. That is because each of these numbers refers to what is called enumerated constants, which are equivalents of the verbose description of what they are. For example, Cursor Location:2 stands for the default which is the enumerated constant, adUseServer. Similarly Cursor Type: 0 stands for the enumerated constant, adOpenForwardOnly; it's a way of opening the records wherein you can only proceed in the forward direction. This brief discussion should suffice, as these will be treated in some detail in other tutorials. The message of this tutorial is to show the mechanics of getting these details by code.

The properties shown above are only a small subset of the properties. The results returned from the query will be accessed by the fields collection. While you return a single row as a result, you may also return multiple rows depending on the complexity of the query that you are running against the database. In case you are running a query to modify the data in some way, such as through the action query, the recordset object may show how many rows you modified through the query. These will be considered in a future tutorial.

You might have wondered why some connection was not made between MS Access's User Interface and the ADO objects while the major advantage of using MS Access is its UI, and most of all the title of the tutorial is related to MS Access. Well, starting from Access 2000, the MS Access form has an extra property called the Recordset. You may assign the ADODB.Recordset as the source of data to the form. You may even assign the same value to multiple forms, and they all get synchronized to the same record. The following figure shows how you may bind the from to a recordset. It's simple except that you have to choose a proper cursor type for this to be effective.

The following code shows how you may bind the adodb recordset to the form, which becomes the source behind the form.

Option Compare Database
Private Sub Form_Load()
Dim strsql As String
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
With cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open "C:Program FilesMicrosoft OfficeOFFICE11SAMPLESNorthwind.mdb"
End With
strsql = "select * from customers"
With rst
Set  .ActiveConnection = cnn
 .CursorType = adOpenKeyset .LockType = adLockOptimistic
.Open strsql
End With

'Here Me refers to the form under consideration
Set Me.Recordset = rst
Set rst = Nothing
Set cnn = Nothing
End Sub

This is the property of the form in the design view of the form. Notice there is no Record Source for the data.

Now after opening the form in the run mode, the properties of the form are as shown in this picture. Notice the Record Source of Data.

In this tutorial we have seen some of the most basic features of ADO's Recordset object. Most importantly, we saw the two ways you can open a recordset and their differences. A few of the properties have been introduced that are related to the recordset object. Also explained was how the ADODB.Recordset binds with the form in MS Access. The Recordset object is very rich; it would be futile to try to cover all of it in one tutorial. Hence only some basic features have been addressed. This will be remedied in future tutorials.