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.
Download the ADORST .mdb file.
