Accessing recordsets

[Chunk]

I have a general question on accessing recordsets

My VBA creates a recordset in module1.

I purposefully did not close the connection or the recordset.

I would like to be able to re-use the recordset in a couple of different modules (say 2 and 3).

Is this even possible? Im looking for any sites, literature, etc that can help. Any direction you can give is greatly appreciated.

Thanks in advance.

-Chunk

[June7]

Declare recordset object variable in module header. Google "public global variable".

[Chunk]

June7,

Can I pass it like any other variable?
Module2

Public rs as adodb.recordset

Module1

Dim rs as adodb.recordset

Create recordset

Module2.rs = rs

I tested by putting all of the code into the same module, declaring the recordset variable (rs) as Public.

Created in first sub, second sub is reporting a Rub-time Error '91': Object variable or with block variable not set.

I'll send code in a few hours, after work.

Respectfully,

-Chunk

[June7]

If you declare as public in header then don't use Dim in procedure.
Consider DAO recordset.

Public rs As DAO.Recordset

Sub MySub()
Set rs = CurrentDb.OpenRecordset("SELECT * FROM MyTable")
'do something with recordset
End Sub

Be aware that public/global variable loses value in unhandled run-time error.

[gregbowers]

Hello

Yes, you can reuse a recordset in different modules. Declare the recordset as a public variable in a standard module and initialize it in a subroutine. Then, access it from other modules. Here’s a quick example:

Declare Public Variables:
 
' In Module1
Public rs As ADODB.Recordset
Public conn As ADODB.Connection
Initialize Recordset:

Sub InitializeRecordset()
    Set conn = New ADODB.Connection
    conn.Open "Your_Connection_String"
    Set rs = New ADODB.Recordset
    rs.Open "SELECT * FROM YourTable", conn, adOpenStatic, adLockReadOnly
End Sub

Access in Other Modules:

' In Module2
Sub UseRecordsetModule2()
    If Not rs Is Nothing Then
        Do While Not rs.EOF
            Debug.Print rs.Fields("YourFieldName").Value
            rs.MoveNext
        Loop
    End If
End Sub
 
' In Module3
Sub UseRecordsetModule3()
    If Not rs Is Nothing Then
        rs.MoveLast
        rs.MoveFirst
        Debug.Print "Record count: " & rs.RecordCount
    End If
End Sub

To open the recordset in Module1 before using it in Module2 or Module3. You can check out VBA Express.


Hope it helps !

thank you
gregbowers