creating recordset in memory?

creating recordset in memory?

Post by H » Sat, 15 Sep 2001 02:09:12



VB doesn't allow to create a method or public variable within a class that
would return array or user defined type.

Is it possible to create a "virtual" recordset in memory (not in a database)
and to gave method (or variable) return that recordset instead of an array.
How do you make such recordset?

Thanks alot,

Hrvoje

PS Just remembered that this actually belonged here instead of
...visual.misc

 
 
 

creating recordset in memory?

Post by James Harge » Sat, 15 Sep 2001 03:09:46


Every recordset is a "virtual" recordset in that it may contain a subset or
superset (in the case of joined tables) of the actual data in the recordset.

Jim


Quote:> VB doesn't allow to create a method or public variable within a class that
> would return array or user defined type.

> Is it possible to create a "virtual" recordset in memory (not in a
database)
> and to gave method (or variable) return that recordset instead of an
array.
> How do you make such recordset?

> Thanks alot,

> Hrvoje

> PS Just remembered that this actually belonged here instead of
> ...visual.misc


 
 
 

creating recordset in memory?

Post by Mike Labos » Sat, 15 Sep 2001 04:08:54


There are two approaches which are pretty easy.  Use a dynamic recordset, or
use a collection of custom objects.  For this example, I made a new standard
exe called Project1 and added a class module and named it CStuff.  I also
set a reference to the ADO 2.5 library.

Code in the CStuff class module:

Option Explicit

Public ID As Long
Public Description As String

Code in the Form1 module demonstrates both approaches:

Option Explicit

Public Function getStuffRS() As ADODB.Recordset

    Dim i As Integer 'for the loop

    'Create the recordset object
    Dim oRS As ADODB.Recordset
    Set oRS = New ADODB.Recordset

    'Configure it
    oRS.CursorLocation = adUseClient
    oRS.CursorType = adOpenDynamic
    oRS.LockType = adLockOptimistic

    'Add some fields
    oRS.Fields.Append "ID", adInteger
    oRS.Fields.Append "Description", adBSTR

    'Open it
    oRS.Open

    'Add some data
    For i = 1 To 10
        oRS.AddNew
        oRS("ID") = i
        oRS("Description") = String$(i, CStr(i))
        oRS.Update
    Next i

    'return the dynamic recordset & cleanup
    Set getStuffRS = oRS
    Set oRS = Nothing

End Function

Public Function getStuffCol() As VBA.Collection

    Dim oCol As VBA.Collection
    Dim oStuff As Project1.CStuff

    'create an instance of a generic collection
    Set oCol = New VBA.Collection

    For i = 1 To 10
        'create a new instance of whatever you want
        Set oStuff = New Project1.CStuff

        'populate its values
        oStuff.ID = i
        oStuff.Description = String$(i, CStr(i))

        'add it to the collection
        oCol.Add oStuff
    Next i

    'return the collection & cleanup
    Set getStuffCol = oCol
    Set oStuff = Nothing
    Set oCol = Nothing

End Function

Private Sub Form_Load()

    'note that this code doesn't care if the
    'RS is from a database or not.

    Dim oRS As ADODB.Recordset
    Dim oCol As VBA.Collection
    Dim oStuff As Project1.CStuff

    'play with a recordset return==========
    Set oRS = getStuffRS()

    Debug.Print "Dynamic Recordset:"

    While Not oRS.EOF
        Debug.Print oRS("ID"), oRS("Description")
        oRS.MoveNext
    Wend

    'Even save it as a file if you want
    oRS.Save "c:\DynamicRecordset.Xml", adPersistXML

    oRS.Close
    Set oRS = Nothing

    'play with a collection return==========
    Set oCol = getStuffCol()

    For Each oStuff In oCol
        Debug.Print oStuff.ID, oStuff.Description
    Next

    Set oStuff = Nothing
    Set oCol = Nothing

End Sub

--
Peace and happy computing,

Mike Labosh, MCP, MCT, MCSD
"Hit any user to continue."

 
 
 

1. Create RecordSet in memory ONLY?

Is it possible to create a temporary recordset in memory
ONLY?  I don't want it tied to a table on disk at all.. I
just need to be able to put some data into it and do a a
quick query.   If someone could tell me how to do this I'd
appreciate it.

Blake

Please remove the 454 from my email address if responding
by email (SPAM protection):

2. ORACLE 7 to ORACLE 8 Conversion/Testing Contract Assignment - Toronto

3. Has anyone seen SQLCODE 19703 ?

4. Create a Recordset in Memory

5. Help with profiler....

6. how to create a recordset in memory?

7. Xml For Analysis Bug

8. Connected Recordset adPersistXML differs from Disconnected Recordset built in-memory

9. CREATE DATABASE creates "memory leak"

10. Creating a new recordset from an existing recordset

11. How do I create a recordset based on another recordset

12. How do I create a recordset based on an existing recordset