how to check if the recordset is opened or close with DAO

Posted on 2005-05-04
Last Modified: 2010-05-02


I searched on this web site, and some people suggest to compare it with Nothing, but I am not quite sure about it...

Is there an attribute or something for DAO recordset...for example...state, status, or something

Question by:TungVan
    LVL 6

    Expert Comment

    for ADO it's .State. I am not sure for DAO though. Might be the same, try it.
    LVL 38

    Accepted Solution

    The only way to be really certain is to use error trapping:

    Private Sub Form_Load()
        Dim db As Database
        Set db = OpenDatabase("C:\temp\test2.mdb")
        Dim rs As Recordset
        Set rs = db.OpenRecordset("Table1", dbOpenSnapshot)
        Do While Not rs.EOF
            Debug.Print rs(1)
        MsgBox IsRecordsetOpen(rs)
        MsgBox IsRecordsetOpen(rs)
    End Sub

    Private Function IsRecordsetOpen(rs As Recordset) As Boolean
        Dim bRet As Boolean
        On Error Resume Next
        If rs.BOF Then
            'do nothing
        End If
        bRet = Not (Err.Number = 3420)
        On Error GoTo 0
        IsRecordsetOpen = bRet
    End Function
    LVL 38

    Expert Comment

    Testing Is Nothing only works if the object is actually set to nothing in the code.  If someone closes the recordset without setting it to nothing, you wouldn't know if it was open or not.  You can test that yourself.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
    Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
    This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

    758 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now