-
Accessing recordsets
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
-
Declare recordset object variable in module header. Google "public global variable".
-
June7,
Can I pass it like any other variable?
Module2
Code:
Public rs as adodb.recordset
Module1
Code:
Dim rs as adodb.recordset
Create recordset
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
-
If you declare as public in header then don't use Dim in procedure.
Consider DAO recordset.
Code:
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.
-
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:
Code:
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:
Code:
' 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