Using the Recordset with MS Access and ADO - Review recordset properties using code

(Page 4 of 4 )

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.

The Recordset object and the User interface in MS Access

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.

Summary

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.

DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware.

Comments On: Using the Recordset with MS Access and ADO

· ADO and ADO.NET will be the technologies you may need to pursue in the future....    · Hi Jay,I was working with a form. The example you have given here perfectly...   >>> Post your comment now!