Consulting

Results 1 to 5 of 5

Thread: Accessing recordsets

  1. #1
    VBAX Regular
    Joined
    Feb 2015
    Posts
    81
    Location

    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

  2. #2
    VBAX Mentor
    Joined
    Nov 2022
    Location
    The Great Land
    Posts
    371
    Location
    Declare recordset object variable in module header. Google "public global variable".
    How to attach file: Reading and Posting Messages (vbaexpress.com), click Go Advanced below post edit window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    VBAX Regular
    Joined
    Feb 2015
    Posts
    81
    Location
    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

  4. #4
    VBAX Mentor
    Joined
    Nov 2022
    Location
    The Great Land
    Posts
    371
    Location
    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.
    How to attach file: Reading and Posting Messages (vbaexpress.com), click Go Advanced below post edit window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    VBAX Regular
    Joined
    Jun 2024
    Location
    US
    Posts
    8
    Location
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •