darkforcesjedi wrote:
Creating a connectionless recordset in ADO is simple enough, but how do
you do it in DAO? I want a recordset stored in memory so I can
filter/sort it easily. If I create a table I can make it work, but I
don't want to have to read/write everything from/to disk every time I
need to use the data.
I tried:
Dim r As DAO.Recordset, tdf As DAO.TableDef
Set tdf = CurrentDb.CreateTableDef("tmp", dbHiddenObject)
With tdf.Fields
.Append tdf.CreateField("TypeID", dbLong)
.Append tdf.CreateField("Minor_ID", dbLong)
End With
'ERROR 3420 "Object invalid or no longer set"
Set r = tdf.OpenRecordset
r.AddNew
r.Fields("TypeID") = 1
r.Fields("Minor_ID") = 7
r.Update
r.Close
Set r = Nothing
Set tdf = Nothing
>From many years ago (the notion is that Transactions will make this
recordset never saved to disk):
Function VirtualDAORecordSet()
Dim TableName As String
Dim Counter As Long
Dim Rcs As DAO.Recordset
With DBEngine
.BeginTrans
With .Workspaces(0)(0)
On Error GoTo CreateTableErr:
TableName = "tblTemp" & CStr(Counter)
.Execute "CREATE TABLE " & TableName & "(fldHoliday TEXT
CONSTRAINT AlphaHoliday UNIQUE);"
Set Rcs = .OpenRecordset(TableName, dbOpenTable)
With Rcs
.AddNew
.Fields("fldHoliday") = "Xerxes Day"
.Update
.AddNew
.Fields("fldHoliday") = "Anaximander Day"
.Update
.AddNew
.Fields("fldHoliday") = "Plato Day"
.Update
.Index = "AlphaHoliday"
.MoveFirst
MsgBox .Fields("fldHoliday") 'Anixamder Day
.MoveNext
MsgBox .Fields("fldHoliday") 'Plato Day
.MoveLast
MsgBox .Fields("fldHoliday") 'Xerxes Day
.Close
End With
End With
End With
VirtualDAORecordSetExit:
Set Rcs = Nothing
DBEngine.Rollback
Exit Function
CreateTableErr:
With Err
If .Number = 3010 Then
Counter = Counter + 1
TableName = "tblTemp" & CStr(Counter)
Resume
Else
MsgBox .Number & " " & .Description
Resume VirtualDAORecordSetExit
End If
End With
End Function
