In previous tutorials we only looked at some of the recordset properties and methods. However, it's useful to stop and consider how we may put some of these already considered methods and properties to use. Some of the most important aspects of data retrieval concern how the data is going to be used and how it may be manipulated. We will be looking at these issues, building upon the previous tutorials.
With the recordset open, we may want to manipulate the recordset rows to accomplish sorting, filtering and finding. Sorting refers to reordering the rows either in descending or ascending order in such a fashion that one or more of their columns (fields) gets rearranged (aka sorted). Filtering, on the other hand, involves using criteria such that only the record or records meeting the criteria are selected for display or other use. Finding is searching through the recordset to locate the rows, one of whose columns satisfies a certain criteria. Sort and Filter properties are dependent on the data provider.
For the purposes of this tutorial, a table named "Employees" has been imported into a Microsoft Access (MS Access 2003) application, manip.mdb. Some extra data (a couple of rows) has been added to the table to make the examples more useful. The table design is as shown in the next picture.
Sorting is almost an unconscious activity in daily life. We try to put things in compartments. In ADO you often sort as well. A familiar sorted list is the names of the states that you often come across when entering your address information on the Internet. If it is not sorted, you are at a loss to choose yours easily. On the other hand if it is alphabetically sorted, you just scroll down until you find yours.
In a recordset you may want to sort on any column name, or a set of column names in a chosen order. For example you may want to sort by "first name" and then you may want sort by the "last name" in the list that was sorted. When you call this property, a temporary index is created for each column specified, and the list is ordered efficiently. If the field is already indexed, it is that much more efficient since the engine does not have to create one. However for this to work you need to open the recordset after setting the CursorLocation to adUseClient. This is because Microsoft Jet 4.0 OleDB provider does not support the IViewSort Interface needed by ADO to do the sorting.
Also, if you want to revert to the original unsorted list, you may provide an empty string for the Sort property as you will see in the example. Of course it goes without saying that you should avoid using the keywords for field names. The syntax for this property usage is:
In order to illustrate the "Sort" property, create a new form in design and add a couple of controls as shown. Some cosmetic changes have been made, and the RowSourceType of the list has been set to "ValueList" as we will be assigning the return value string to this property. If this is left as the default, Table/Query, then you will not see any display because the string is neither a table nor a query.
Basically a recordset is retrieved from the database using the code that we previously discussed in other tutorials. Each of the command buttons (Unsorted, Sorted: Last Name Ascending, Sorted: LastName Ascending and Hire Date descending) can sort the list (or revert to the original in one case) using the code in their click events as shown in the snippet that follows the picture.
Option Compare Database
Private cn As ADODB.Connection
Private rst As ADODB.Recordset
Private Sub Command0_Click()
'don't sort the recordset
rst.Sort = ""
Call poplist
End Sub
Private Sub Command1_Click()
'Sort the recordset with Last Name ascending
rst.Sort = "LastName asc"
Call poplist
End Sub
Private Sub Command2_Click()
'sort with Last Name ascending and Hire Date descending
rst.Sort = "LastName asc, HireDate desc"
Call poplist
End Sub
Private Sub Form_Load()
Dim str As String
str = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:Documents and SettingsJay _
My DocumentsRetrieve.mdb;" & _
"Persist Security Info=False"
Dim strSql As String
'strSql = "Employees"
Set cn = New ADODB.Connection
cn.Open str
Set rst = New ADODB.Recordset
rst.CursorLocation = adUseClient
strSql = "Select LastName, FirstName, City,
HireDate from Employees"
rst.Open strSql, cn, adOpenStatic
MsgBox (rst.Fields.Count)
Set Me.Recordset = rst
End Sub
Sub poplist()
Dim strg
strg = ""
While Not rst.EOF
strg = strg & rst.Fields.Item(0).Value & ":" & _
rst.Fields.Item(1).Value & ":" & _
rst.Fields.Item(2).Value & ":" & _
"rst.Fields.Item(3).Value & ";"
rst.MoveNext
Wend
Me.List11.RowSource = strg
End Sub
The recordset opens with the Form's Load() event. Observe that the recordset is opened after setting the CursorLocation property to adUseClient. Some of the message boxes in this and other snippets are left in place, so that you can see what is happening. Command0's click will revert the record set to its original state. Clicking Command1 will sort the list so that the LastName will be in ascending order (it is actually the default). Command2 button's click will not only sort by LastName, it will also sort by HireDate (see Buchanan's information). The next picture shows what it looks like when the list is sorted by LastName in ascending order. Each of the click events also call the procedure poplist(), which populates the ListBox.
This next picture shows sorting by LastName ascending and HireDate descending.
The worst crime you can commit is to get all the server data to the client application. In order to minimize your call on resources it is always recommended that you get just what you need. That is, you should try to Filter your data; try to focus. The filter property does just that. For example, in our Employees table there are people coming from five different cities, and there are 11 people. This means there is more than one person coming from one or two of the cities. Let's say you just want to find those coming from "London." In this case you will filter so that, those not coming from "London" will be removed from the list.
Using the Where Clause in the query
This tutorial gives you only the basics for using the filter. There are other values such as the FilterGroupEnum enumeration that can be provided for the filter property. These will not be considered here.
In ADO you can do filtering two ways. You can do filtering using the "Where" clause in your original recordset retrieval process. This will bring in what you want, ready to use. Create a form and drop in controls as shown; the code that follows will be based on these controls.
The Combo0's click event will fill the comb with a sorted list of Cities, with no city being repeated. This is read-only and fills up the Combo Box fast. The "Distinct" keyword removes the duplicates from the list (London and Seattle would appear only once) and the Order By sorts the cities in the default ascending order. To initiate this event, double click on the Combo Box's label. Then click on the Combo Box's down arrow to reveal the cities as shown.
Now pick a city and click on the button at the bottom. This retrieves the recordset from a parametric query, which is highlighted, and uses the Combo Box's text as the argument. The rest of the code is just retrieving the recordset and stuffing it into a ListBox. Additionally a textbox (unbounded; see the design view) has been added to show the name of the (filtered) city that is chosen.
Option Compare Database
Private cn As ADODB.Connection
Private rst As ADODB.Recordset
Private Sub Combo0_DblClick(Cancel As Integer)
Dim str As String
str = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:Documents and SettingsJayMy Documents
Retrieve.mdb;" & _
"Persist Security Info=False"
Dim strSql As String
'strSql = "Employees"
Set cn = New ADODB.Connection
cn.Open str
Set rst = New ADODB.Recordset
rst.CursorLocation = adUseClient
strSql = "Select distinct City from Employees
order by City"
rst.Open strSql, cn, adOpenReadOnly
MsgBox (rst.Fields.Count)
Set Me.Recordset = rst
Dim strg
strg = ""
While Not rst.EOF
strg = strg & rst.Fields.Item(0).Value & ";"
rst.MoveNext
Wend
Me.Combo0.RowSource = strg
'rst.Close
'cn.Close
End Sub
Private Sub Command2_Click()
Combo0.SetFocus
cmbtxt = Combo0.Text
'MsgBox (Combo0.Text)
Me.List5.RowSource = ""
Text7.SetFocus
Text7.Text = cmbtxt
Dim str As String
str = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:Documents and SettingsJayMy Documents
Retrieve.mdb;" & _
"Persist Security Info=False"
Dim strSql As String
'strSql = "Employees"
Set cn = New ADODB.Connection
cn.Open str
Set rst = New ADODB.Recordset
rst.CursorLocation = adUseClient strSql =
"Select LastName, FirstName, HireDate, " & _
"City from Employees where City = " & "'" & cmbtxt & "'"
MsgBox (strSql)
rst.Open strSql, cn, adOpenDynamic
Set Me.Recordset = rst
strinfo = ""
While Not rst.EOF
strinfo = strinfo & rst.Fields.Item(0).Value & " : " & _
rst.Fields.Item(1).Value & " : " & _
"rst.Fields.Item(2).Value & " : " & rst.Fields.Item(3).
Value & ";"
rst.MoveNext
MsgBox (strinfo)
Wend
Me.List5.RowSource = strinfo
rst.Close
cn.Close
End Sub
The result of this code is shown in the next picture where the value "Seattle" was picked in the Combo Box, and the Command Button "Employee Info by City" was clicked.
In this case the Recordset's Filter property will be used. The Microsoft Jet 4.0 OleDB provider does not support the IViewFilter Interface used by ADO to filter, but the ADO engine will use the adUseClient automatically.
There is a Filter property associated with a Form, Report, or Table in MS Access; they are not the same as the Filter property of ADO's Recordset. All the forms in this tutorial are just a canvas for placing controls.
The filtering is based on a criteria such as "City='London'". The syntax of the criteria will be <FieldName> Operator<Value>. In this case, the <FieldName> will be City, the operator is the "=" sign and the value will be 'London'. The value, if it is a string, is in single quotes as shown; for dates it will be the # sign, and for the numbers it will be without any decoration. After the filtering action, the recordset will be the Current Cursor, and some of the properties such as AbsolutePosition, AbsolutePage, RecordCount, and PageCount will all be referenced to the filtered set, not the original recordset.
Create a new form and add a few controls as shown. For the ListBox set the RowSourceType property to 'ValueList'. When the form loads, you will see all the records; enter the name of a city in the textbox and click on the button. This applies the filter as shown in the code. The line where the filter property usage can be seen is highlighted. The city value is passed from the textbox to the variable 'strFilter' as shown. You can see that the record counts before and after the filter are different (just remove the comments for the message boxes). The rest of the code after the filter is used to call the poplist() procedure which populates the ListBox.
Option Compare Database
Private cn As ADODB.Connection
Private rst As ADODB.Recordset
Private Sub Command0_Click()
Dim strFilter
Text3.SetFocus
strFilter = Text3.Text
'MsgBox (rst.RecordCount)
rst.Filter = "City= '" & strFilter & "'"
'MsgBox rst.RecordCount
Call poplist
End Sub
Private Sub Form_Load()
Dim str As String
str = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:Documents and SettingsJayMy Documents
Retrieve.mdb;" & _
"Persist Security Info=False"
Dim strSql As String
'strSql = "Employees"
Set cn = New ADODB.Connection
cn.Open str
Set rst = New ADODB.Recordset
rst.CursorLocation = adUseClient
strSql = "Select LastName, FirstName, City,
HireDate from Employees"
rst.Open strSql, cn, adOpenKeyset
'MsgBox (rst.Fields.Count)
Set Me.Recordset = rst
Dim strrec
strrec = ""
While Not rst.EOF
strrec = strrec & rst.Fields.Item(0).Value & _
":" & rst.Fields.Item(1).Value & ":" & rst.Fields.Item(2).
Value & _
":" & rst.Fields.Item(3).Value & ";"
rst.MoveNext
'MsgBox (strrec)
Wend
Me.List1.RowSource = strrec
End Sub
Sub poplist()
Dim strg
strg = ""
While Not rst.EOF
strg = strg & rst.Fields.Item(0).Value & ":" & _
rst.Fields.Item(1).Value & ":" & rst.Fields.Item(2).Value & _
":" & rst.Fields.Item(3).Value & ";"
rst.MoveNext
Wend
Me.List1.RowSource = strg
End Sub
The next picture shows the result of running this form. The name 'London' has been typed into the textbox. As in the case of 'Sorting' you could also have used the Combo Box to show a sorted list of cities instead of a text box.
Provided you know the criteria, you can use the Find method of the RecordSet to find the first instance (first match) of the row that satisfies the criteria. The syntax for the Find() method, which takes the following four arguments, is as follows with the required argument:
RecordSet.Find Criteria
The other three arguments are SkipRows, SearchDirection, and Start, which are optional. This method only supports searches on a single column. The criteria is a string which has a column name followed by a comparison operator. The comparison operator includes =, <, >, <=, >=, <=, and 'Like' with pattern matching. Searching backwards and forwards is possible. The variable SkipRows (long) shows how many records to skip before the search begins and the Start (variant) argument is a Bookmark for where the search should start. The example shown uses only the criteria.
Create a new form with a couple of controls as shown in the picture. The recordset in which we will be showing the usage of the Find() method will return four columns, FirstName, LastName, HireDate, and City. We will be doing searches in these fields (columns).
From the Combo Box shown you can pick any one of these columns for searching. The field names are hard coded into the value list for the Combo Box. This can be done by accessing the property window of the Combo Box.
For example, after choosing the FirstName from the Combo Box, the name 'Nancy' was entered (we know that it exists in the table) and then the Find button was clicked. The found recordset values are dumped to the text box. This is shown in the next picture. In this case the "=" operator was used and the usage of the find() method was rst.Find strfind. The strfind was tailored out of values being picked up by the Combo Box and the textbox as strfind = strcmb1 & " = " & "'" & fndstrg & "'". As mentioned previously, other operators can also be used, including the non-relational 'Like'.
Now that you have seen how the user interface works, it is time to look at the code which is shown in the next paragraph. The form's load event creates the recordset, a process that has been carried out a number of times in this and other tutorials. After the form is visible, and when a particular column name is picked from the Combo Box, the string variable strcmb1 will be extracted. Next, when a value is inserted into the textbox, the variable fndstrg will be extracted, and these two variables will be combined to produce the variable strfind in defining the argument for the find() method. If the value you enter into the textbox is not found, then an error handler is written to indicate that the searched element is not found. The highlighted statements are all needed for the find method. For the example shown the usage of the find(0 method reduces to rst.Find "FirstName='Nancy'".
Dim cn As ADODB.Connection
Dim rst As ADODB.Recordset
Private Sub Command0_Click()
On Error Goto Notfound
Dim strcmb1
Combo1.SetFocus
strcmb1 = Combo1.Text
MsgBox ("You have a total of " & rst.RecordCount & " records")
Dim fndstrg
Text5.SetFocus
fndstrg = Text5.Text
Dim strfind
strfind = strcmb1 & " = " & "'" & fndstrg & "'"
MsgBox (strfind)
rst.Find strfind
Me.Text10.Value = rst.Fields("FirstName").
Value & vbCrLf & rst.Fields("LastName").Value & vbCrLf &
rst.Fields("HireDate").Value & vbCrLf & rst.Fields("City").Value
Exit Sub
Notfound:
MsgBox ("Record for " & fndstrg & " Not found")
Resume Next
End Sub
Private Sub Form_Load()
Dim str As String
str = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:Documents and SettingsJayMy Documents
Retrieve.mdb;" & _
"Persist Security Info=False"
Dim strSql As String
'strSql = "Employees"
Set cn = New ADODB.Connection
cn.Open str
Set rst = New ADODB.Recordset
rst.CursorLocation = adUseClient
strSql = "Select LastName, FirstName, City,
HireDate from Employees"
rst.Open strSql, cn, adOpenDynamic
MsgBox (rst.Fields.Count)
End Sub
Summary
This is an ADO related basic tutorial without any bells and whistles. The idea was just to show the basic usage of the properties and the methods used for manipulating the recordset based on the previous tutorials. In order to keep the matter simple, a very simple table with a reduced set of records was chosen. The code snippets presented were all tested on MS Access 2003 and the Windows XP Media Center Edition OS.
/ 7 
Using the Recordset with MS Access and ADO
