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.

you can use a drop-down box instead of a checkbox

Download the ADORST .mdb file.

» See All Articles by Columnist Danny J. Lesandrini

Danny Lesandrini
Danny Lesandrini
Danny J. Lesandrini currently works as the IT Director for Pharmatech Oncology Inc. at http://www.pharmatechoncology.com/. He holds Microsoft Certifications in Access, Visual Basic and SQL Server and has been programming with Microsoft development tools since 1995.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles