Using ADO Stand-Alone Recordsets as an Alternative to Arrays or DictionariesBy Roman Koch
If you need to deal with a large amount of dynamic data in a VB-type program, you often need to work with arrays or with the Dictionary object. While both methods have their merits, I often felt that I would prefer a more flexible approach to deal with data in memory. Then I learned about ADO stand-alone recordsets - giving you all the power of the Recordset object when dealing with data in memory.
Simply speaking, a stand-alone recordset has no source - no Command object, no SQL statement, no table name. It's an in-memory collection of records and fields. You create and use a stand-alone recordset in the same way you do with sourced recordsets:
The above code includes the
adovbs.inc constants, hence the use of the
adChar. To learn more about
adovbs.inc constants be sure
ADOVBS.inc - Use It!
What makes stand-alone recordsets so attractive? You can use all
the ADO recordset methods and properties to work with your in-memory
data. You can sort and filter the records, you can use the
AddNew method, you can clone the thing and even
save it to disk - even in XML format, if you have ADO 2.1! (For more information on XML, be
sure to check out the XML Article Index.)
Additionally, when you are working with ActiveX objects, for example the ADO Data Control, you can set the RecordSource property of the control to your stand-alone recordset and then use the power of a linked DataGrid control to work with your in-memory data!
On the other hand, stand-alone recordsets are slower than arrays or Dictionary objects, and they also eat more memory. You will need to balance the power of in-memory recordsets against the higher price in speed and memory consumption. For more information on using stand alone Recordsets be sure to read Ryan S.'s article: Using Custom Recordsets.
DirList.ASP Sample Program
DirList.ASP demonstrates how you can use a stand-alone recordset.
DirList.ASP reads a directory and displays the files and some file
attributes in a table. On subsequent calls to
DirList.ASP, the table
is sorted and filtered. DirList achieves this by modifying the
properties of the underlying stand-alone recordset. Here is what
DirList.ASP does in detail:
DirList.ASPis called without any parameters, it creates the stand-alone recordset and fills it with information about the files in the directory where
DirList.ASPresides (the FileSystemObject is used to get information about the files).
DirList.ASP is called with a parameter, it modifies the
properties of the stand-alone recordset as indicated by
the parameter command.
3. It then processes the the recordset to display the directory
listing in a table format. It also provides a couple of "command"
links to call
DirList.ASP with sorting and filtering parameters.
Keep in mind that
DirList.ASP is just a demonstration program - it does
little error checking and is not intended for productive use. There's also a
live demo that you can play with before
DirList.aspin ZIP format