DAO Connectionless Recordset?

[darkforcesjedi]

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.Creat eTableDef("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.OpenRecords et

r.AddNew
r.Fields("TypeI D") = 1
r.Fields("Minor _ID") = 7
r.Update

r.Close
Set r = Nothing
Set tdf = Nothing

[Lyle Fairfield]

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.Creat eTableDef("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.OpenRecords et

r.AddNew
r.Fields("TypeI D") = 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 VirtualDAORecor dSet()
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 & "(fldHolida y TEXT
CONSTRAINT AlphaHoliday UNIQUE);"
Set Rcs = .OpenRecordset( TableName, dbOpenTable)
With Rcs
.AddNew
.Fields("fldHol iday") = "Xerxes Day"
.Update
.AddNew
.Fields("fldHol iday") = "Anaximande r Day"
.Update
.AddNew
.Fields("fldHol iday") = "Plato Day"
.Update
.Index = "AlphaHolid ay"
.MoveFirst
MsgBox .Fields("fldHol iday") 'Anixamder Day
.MoveNext
MsgBox .Fields("fldHol iday") 'Plato Day
.MoveLast
MsgBox .Fields("fldHol iday") 'Xerxes Day
.Close
End With
End With
End With
VirtualDAORecor dSetExit:
Set Rcs = Nothing
DBEngine.Rollba ck
Exit Function
CreateTableErr:
With Err
If .Number = 3010 Then
Counter = Counter + 1
TableName = "tblTemp" & CStr(Counter)
Resume
Else
MsgBox .Number & " " & .Description
Resume VirtualDAORecor dSetExit
End If
End With
End Function

[David W. Fenton]

"darkforcesjedi " <an************ @pgnmail.comwro te in
news:11******** **************@ m73g2000cwd.goo glegroups.com:

Creating a connectionless recordset in ADO is simple enough, but
how do you do it in DAO?

No. Disconnected recordsets do not exist in DAO and probably never
will. You have to use a table or a transaction (on real tables that
is then rolled back).

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Tom van Stiphout]

On 17 Jul 2006 09:05:10 -0700, "Lyle Fairfield"
<ly***********@ aim.comwrote:

Anaximander? That's kind-a obscure. Scolars don't even agree on his
birth year, let alone birth date. And no women in the list (or did you
just provide a fragment of your code)?
Your transaction idea is just as obscure. It probably works, but would
you want it to?

-Tom.

>
darkforcesje di 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.Creat eTableDef("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.OpenRecords et

r.AddNew
r.Fields("TypeI D") = 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 VirtualDAORecor dSet()
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 & "(fldHolida y TEXT
CONSTRAINT AlphaHoliday UNIQUE);"
Set Rcs = .OpenRecordset( TableName, dbOpenTable)
With Rcs
.AddNew
.Fields("fldHol iday") = "Xerxes Day"
.Update
.AddNew
.Fields("fldHol iday") = "Anaximande r Day"
.Update
.AddNew
.Fields("fldHol iday") = "Plato Day"
.Update
.Index = "AlphaHolid ay"
.MoveFirst
MsgBox .Fields("fldHol iday") 'Anixamder Day
.MoveNext
MsgBox .Fields("fldHol iday") 'Plato Day
.MoveLast
MsgBox .Fields("fldHol iday") 'Xerxes Day
.Close
End With
End With
End With
VirtualDAOReco rdSetExit:
Set Rcs = Nothing
DBEngine.Rollba ck
Exit Function
CreateTableErr :
With Err
If .Number = 3010 Then
Counter = Counter + 1
TableName = "tblTemp" & CStr(Counter)
Resume
Else
MsgBox .Number & " " & .Description
Resume VirtualDAORecor dSetExit
End If
End With
End Function

[Lyle Fairfield]

Tom van Stiphout wrote:

On 17 Jul 2006 09:05:10 -0700, "Lyle Fairfield"
<ly***********@ aim.comwrote:

Anaximander? That's kind-a obscure. Scolars don't even agree on his
birth year, let alone birth date. And no women in the list (or did you
just provide a fragment of your code)?
Your transaction idea is just as obscure. It probably works, but would
you want it to?

So sorry ... it was a typo for Anixemenes.

I don't think I'd want to. I have used temporary tables within
transactions when dealing with some ghastly non-normalized genealogical
tables where the calculations were very intensive ... follwing back
parents; to simplify this I created the temp tables, indexed them and
scanned through them, writing and ftping up html files for each person
.... I do think this simplified things by giving me two or three
standard tables that were designed just for this modules needs. By
indexing these I could find a son/daughter/second wife/whatever pdq ...
even counting creating the tables and indexes the thing ran several
hundred times faster than using the disorganized data. Of course the
tables were never saved ...also of course, Access itself had no
knowledge of them, even during their existence.