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 (http://www.vbaexpress.com/).


Hope it helps !

thank you
gregbowers