Re: DAO Connectionless Recordset?


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

.

Relevant Pages

  • Re: Add CommandBar WENN EditorType != Word
    ... Dim myOlApp As Outlook.Application ... Set myOlApp = CreateObject ... MsgBox "na, du inspector gadget!" ... ' End With ...
    (microsoft.public.de.outlook)
  • Re: Add CommandBar WENN EditorType != Word
    ... Dim mnuFile As Office.CommandBarPopup ... Set Bars = Application.ActiveInspector.CommandBars ... MsgBox "na, du inspector gadget!" ... ' End With ...
    (microsoft.public.de.outlook)
  • Re: Cancel Printing Except Q
    ... Dim myArr As Variant ... MsgBox "Don't print it" ... Dave Peterson wrote: ...
    (microsoft.public.excel.programming)
  • Re: VB and local area network
    ... Dim FixedInfoSize As Long ... Dim pAddrStr As Long ... MsgBox "Node type: Broadcast" ... IP_ADAPTER_INFO contains a linked list of adapter entries. ...
    (microsoft.public.vb.general.discussion)
  • Re: Reading in outside file
    ... I've put in some MsgBox "Test" values to check where exactly the process ... It opens the file, but then crashes before it gets to the next line. ... 'This method will open the external .xls file and read in the DFR Groups. ... Dim lastRow As Long 'Used to hold the value of last row on the sheet. ...
    (microsoft.public.excel.programming)