Create In-Memory ADO Recordsets

Creating in-memory ADO recordsets may be a solution in search of a problem, but I find the process fascinating and there's been at least one occasion where it was just the right thing for my application. Maybe it's just what you need too. The download has everything you'll need to check it out.

Working with the Invisible

In a way, all data is invisible but at least when it's in a table you can click on it and "touch it", as it were. What we're proposing to create is a virtual table, in memory, in the form of an ADO recordset. Once we get it, we can manipulate it in code and more importantly, for the sake of this demo, load it into an Access form.

The code to accomplish this is simple and IntelliSense will help you extend what is described below. What follows will work fine, provided you've got a reference set to some version of Microsoft ActiveX Data Objects.

Dim rstADO As ADODB.Recordset
Dim fld As ADODB.Field

Set rstADO = New ADODB.Recordset
With rstADO
    .Fields.Append "EmployeeID", adInteger, , adFldKeyColumn
    .Fields.Append "FirstName", adVarChar, 10, adFldMayBeNull
    .Fields.Append "LastName", adVarChar, 20, adFldMayBeNull
    .Fields.Append "Email", adVarChar, 64, adFldMayBeNull
    .Fields.Append "Include", adInteger, , adFldMayBeNull
    .Fields.Append "Selected", adBoolean, , adFldMayBeNull

    .CursorType = adOpenKeyset
    .CursorLocation = adUseClient
    .LockType = adLockPessimistic
    .Open
End With

There it is. We have a virtual table that we created from nothing. Now we can load it with our own data from the Employees table. For loading the data I reference the fields by their ordinal position but one could also reference them by name.

Dim dbs As DAO.Database
Dim rstDAO As DAO.Recordset
Dim strSQL As String

Set dbs = CurrentDb
strSQL = "SELECT EmployeeID, FirstName, LastName, Email " & _
         "FROM Employees ORDER BY LastName, FirstName"
Set rstDAO = dbs.OpenRecordset(strSQL, dbOpenDynaset)

Do Until rstDAO.EOF
    rstADO.AddNew
    rstADO.Fields(0) = rstDAO!EmployeeID
    rstADO.Fields(1) = rstDAO!FirstName
    rstADO.Fields(2) = rstDAO!LastName
    rstADO.Fields(3) = rstDAO!Email
    rstADO.Fields(4) = Null
    rstADO.Fields(5) = Null
    rstADO.Update
    
    rstDAO.MoveNext
Loop

Notice that two of the columns, Include and Selected, do not correspond to our Employee's table in any way. This is the very reason to use our virtual table. We need a virtual field which we can edit and then potentially use to process the result set. I've set their initial values to NULL.

Finally, we set the Recordset property of the form to our ADO recordset. Note that this is NOT the RecordSource property, which takes the name of a table, a query or a complete SQL Statement. The Recordset property takes an object, an ADO recordset, and requires the SET keyword to make it work. Once it's been set, the ADO recordset we created can be destroyed. When the form opens, it displays our records.

Set Me.Recordset = rstADO

Set rstDAO = Nothing
Set dbs = Nothing

Editing the Recordset

This is where it gets tricky. In my experience, when an ADO recordset is created based directly on a table, the Add New function works fine. In this screen shot you see that the form looks like it supports moving to a new record, but the whole new record row is locked. I couldn't find a work-around for the user interface limitation, but it's easy enough to add a record in code. Once added, it can be edited like any other row.

I had written an article last year that discussed binding ADO recordsets to Access forms. The article link is below and the sample code shows that this limitation doesn't exist naturally. It must be the result of using a virtual table as the source for the ADO recordset.

http://www.databasejournal.com/features/msaccess/article.php/3672601/Manage-Recordsets-in-ADPs.htm

Well, there is another exception, but it's a Microsoft Access limitation. It seems that in versions prior to Access 2003 there is a checkbox bug that really screws up the editing of forms bound to an ADO recordset. In some cases, it causes the application to crash. You can't beat it through any settings, and I tried them all, but upgrading to Access 2003 fixes the issue.

Alternatively, you can use a drop-down box instead of a checkbox. It's clumsy and users tend not to like it, but it does solve the problem. As you can see in the screen shot, it's not pretty.

Why Bother With This Approach

I've used this approach a couple of times, when it was appropriate. There are definite limits to its usefulness but it's just right when you need a Datagrid-With-Checkboxes. Below is a screen shot of an interface I built for users to associate labs with sites. They would select a lab from the drop-down list and it would then be saved as a new row in the SiteLab table. The problem is, this form confused my users.

I know, it's not that complicated and the users shouldn't have been confused but consider the new, improved version below. This is similar to the Access References dialog box where every possible library is shown and the ones you have referenced show up at the top with their checkboxes checked.

This approach is naturally intuitive, though it requires more code behind the scenes to make it work. When a user checks a box, the AfterUpdate event adds or removes that Lab to the SiteLab table and rebuilds the list. My users prefer this interface and there is no visible performance hit.

One of the nice things about the checkbox interface is that the users can scan the list more easily. They may even double-click an entry and be taken to the Lab Detail page to be sure it's the one they want to add.

Conclusion

Virtual tables from in-memory ADO recordsets are an odd little tool, but one that has a place in my applications. It may or may not be of use in yours, but if you find this useful, drop me a line at dlesandrini@gmail.com and let me know how it worked for you.

Download the ADORST .mdb file.

» See All Articles by Columnist Danny J. Lesandrini