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
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
.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
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.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
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
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.
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
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.
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 firstname.lastname@example.org and let me know
how it worked for you.
Download the ADORST .mdb file.
See All Articles by Columnist Danny J. Lesandrini