1. ADODB.Recordset Object
This section assumes a basic working knowledge in developing database applications using ActiveX Data Objects.
ADODB Recordset object has an important property known as “Filter”. It’s been neglected by some of the developers treating it as less important one while developing applications.
Let us see the importance of this property with a simple code snippet,
Sub RetrieveDeptDetails()
cn.Open "specify connection string to connect to appropriate database"
cn.CursorLocation = adUseClient
'open emp_mstr recordset and fetch emp_no, emp_name field values
'assume table will have below values
'emp_no emp_name
'-----------------------------
'100 dxyz1
'200 dxyz2
'300 dxyz3
rsEmp.Open "select emp_no, emp_name from emp_mstr", cn, adOpenStatic
'open dept master recordset and fetch emp_no, dept_no & dept_name field values
'assume table will have below values
'emp_no dept_no dept_name
'-------------------------------------------
'100 D100 d1xyz1
'100 D101 d2xyz1
'100 D102 d3xyz1
'200 D200 d1xyz2
'200 D201 d2xyz2
'300 D300 d1xyz3
'300 D301 d2xyz3
'300 D302 d3xyz3
'300 D303 d4xyz3
Conventional Looping Approach #1
If rsEmp.RecordCount > 0 Then
rsEmp.MoveFirst
While Not rsEmp.EOF
'Assume you want to fetch dept details from dept_mstr based on the
'Emp_no fetched from rsEmp recordset, the normal way is to
'open rsDept recordset as below,
rsDept.Open "select emp_no,dept_no,dept_name from dept_mstr where
emp_no =" _
& rsEmp.Fields("emp_no"), cn, adOpenStatic
If rsDept.RecordCount > 0 Then
'process dept details in whatever way you want
MsgBox rsDept.Fields("dept_no")
MsgBox rsDept.Fields("dept_name")
End If
'close the recordset if its open
If rsDept.State = adStateOpen Then rsDept.Close
rsEmp.MoveNext
Wend
End If
The above code snippet is one way of filtering the recordset values based on some pre-defined values(Emp_no in this case).
The above logic can be re-written using Filter property and it will definitely makes the code simple and reduces the overhead of the application.
Approach #2 using Filter Property
'instead of opening rsDept everytime inside the loop, open it outside the while loop
rsDept.Open "select emp_no,dept_no,dept_name from dept_mstr ", cn, adOpenStatic
If rsEmp.RecordCount > 0 Then
rsEmp.MoveFirst
While Not rsEmp.EOF
'apply the filter condition here for rsDept recordset,
'emp_no is the field selected from rsDept recordset
'and we are filtering the recordset based on rsemp.emp_no field value
'incase if the rsEmp.Fields("emp_no") is 300
'initial value of rsDept.recordcount before applying the filter will be 9
'according to above sample values
rsDept.Filter = " emp_no =" & rsEmp.Fields("emp_no")
'after applying the filter value i.e, rsDept.Filter = " emp_no =300"
'rsdept.recordcount will become 4
If rsDept.RecordCount > 0 Then
'process dept details in whatever way you want
MsgBox rsDept.Fields("dept_no")
MsgBox rsDept.Fields("dept_name")
End If
'refresh the filter to bring to initial state i.e, make the coount as 9
rsDept.Filter = ""
rsEmp.MoveNext
Wend
End If
End Sub
The major disadvantage of using above Approach #1 is,
Incase if the rsDept recordset is going to fetch lot of values in real-time environment based on the EmpNo parameter value, opening the Recordset everytime and closing it inside the While Loop will definitely bring down the performance of the Application. To avoid this Approach #2 can be very well used which will make the developer as well as the application life easy.
2. Scripting.Dictionary Object:
Dictionary Object is used to store information in name/value pairs (referred to as key and item). We are going to see in a comparative basis “How Dictionary Object is better than Collection Object”. Collection Object is also used to store information in name/value pairs. Collection/Dictionary Objects are used whenever there’s a need for dynamic array handling. But Dictionary Object provides extra methods & properties which makes it more powerful compared to Collection Object.
Let us see the importance of Dictionary Object with an below example,
Dim itm
Dim objCol As Collection
Set objCol = New Collection
'Add Method takes parameter Item, key, before, after
'Count Property returns Collection Count
'Item(index) - retrieve value based on index/key
'Remove(index) - remove value from object based on index/key
'Let's add some values to this object
'and associate keys to each values
objCol.Add "100", "a"
objCol.Add "200", "b"
objCol.Add "300", "c"
objCol.Add "400", "d"
objCol.Add "500", "e"
objCol.Add "600", "f"
objCol.Add "700", "g"
'let's assume you want to find a value 700 inside the collection
'we will be writing a loop to find that value whether it exists in the collection
For Each itm In objCol
If itm = "700" Then
MsgBox "Found 700"
End If
Next
To find an Item 700, the above For loop has to search the collection object 7 times assume your collection object holds a count of 1000 records and you want to find a value which is at the end position ie., 1000th position, your looping logic might induce a delay in application performance.
Also another disadvantage is that we don’t have a way list all the possible Key Values added to the Collection Object.
To avoid all these problems, we can use Scripting.Dictionary Object,
Collection Object provides only 4 Methods/Properties,
But Scripting.Dictionary Object provides a lot more (please see below the properties & methods provided by Dictionary Object) which it makes it more powerful compared to Collection,
-
Add
-
CompareMode
-
Count
-
Exists
-
Item
-
Items
-
Key
-
Keys
-
Remove
-
RemoveAll
Let us take the same sample values, which we have used for building the Collection Object,
Dim itm
Dim objDict As Scripting.Dictionary
Set objDict = New Scripting.Dictionary
'Let's add some values to this object
'and associate keys to each values
'in Collection Object add method takes parameter item, key
'but here Add Method takes parameter key, item
objDict.Add "100", "a"
objDict.Add "200", "b"
objDict.Add "300", "c"
objDict.Add "400", "d"
objDict.Add "500", "e"
objDict.Add "600", "f"
objDict.Add "700", "g"
'let's assume you want to find a key 700 inside the dictionary object
'you can use a exists method instead of looping the Dictionary Object on the
'whole to find a single element
'Exists Method returns TRUE if match is made , else returns FALSE
MsgBox objDict.Exists("700")
We will also be able to list all the KEYS associated with each values in the Dictionary Object using Keys Method of the Dictionary Object.
Please note that inorder to use this Dictionary Object, we need to create Project -> Reference to Microsoft Scripting Runtime Library.
References:
Please refer to MSDN for a detailed description about the various properties and methods of Collection & Dictionary Objects.