ADO Recordset data not showing on form

Question

I've got a frustrating issue on MS Access 2010 that I would at this stage qualify as a bug. And after having tried all possible workarounds, I am out of ideas and rely on you.

---

Context

Huge Ms Access 2010 application with 25k lines of VBA and >50 forms. It has a client server architecture with a frontend compiled and an Access backend on the network. It makes connections to a twentish of different databases (Oracle/SQL Server/Sybase IQ).

---

The problem

Sometimes when I assign an ADODB recordset to a subform, its data isn't shown in bound fields. I've got #Name? everywhere

The data is there. I can debug.print it, I can see it in the Watches browser, I can read or manipulate it while looping on the recordset object with code. It just not appear in the subform.

It can work flawlessly during months, and suddenly one form will start having this issue without any apparent reason (it might happen even on forms that I have not changed). When it happens, it does for all users, so this is really something wrong in the frontend accdb/accde.

The issue is not related to a specific DBMS/Driver. It can happen with Oracle or Sybase data.

I have created my own class abstracting everything related to ADO connections and queries, and use the same technique everywhere. I've got several tenth of forms based on it and most of them works perfectly.

I have this issue in several parts of my application, and especially in a highly complicated form with lots of subforms and code. On this Main form, a few subforms have the issue, while others don't. And they have the exact same parameters.

---

The Code

This is how I populate a form's recordset :

        Set RST = Nothing
        Set RST = New ADODB.Recordset
        Set RST = Oracle_CON.QueryRS(SQL)

        If Not RST Is Nothing Then
            Set RST.ActiveConnection = Nothing
            Set Form_the_form_name.Recordset = RST
        End If

The code called with Oracle_CON.QueryRS(SQL) is

Public Function QueryRS(ByVal SQL As String, Optional strTitle As String) As ADODB.Recordset

    Dim dbQuery As ADODB.Command
    Dim Output As ADODB.Recordset
    Dim dtTemp As Date
    Dim strErrNumber As Long
    Dim strErrDesc As String
    Dim intSeconds As Long

    Dim Param As Variant

    If DBcon.state <> adStateOpen Then
        Set QueryRS = Nothing
    Else
        DoCmd.Hourglass True

        pLastRows = 0
        pLastSQL = SQL
        pLastError = ""
        pLastSeconds = 0

        Set dbQuery = New ADODB.Command
        dbQuery.ActiveConnection = DBcon
        dbQuery.CommandText = SQL
        dbQuery.CommandTimeout = pTimeOut

        Set Output = New ADODB.Recordset

        LogIt SQL, strTitle

        dtTemp = Now

        On Error GoTo Query_Error

        With Output
            .LockType = adLockPessimistic
            .CursorType = adUseClient
            .CursorLocation = adUseClient
            .Open dbQuery
        End With

        intSeconds = DateDiff("s", dtTemp, Now)

        If Output.EOF Then
            LogIt "-- " & Format(Now, "hh:nn:ss") & " | Executed in " & intSeconds & " second" & IIf(intSeconds = 1, "", "s") & " | Now rows returned."
            Set QueryRS = Nothing
        Else
            Output.MoveLast
            pLastRows = Output.RecordCount
            LogIt "-- " & Format(Now, "hh:nn:ss") & " | Executed in " & intSeconds & " second" & IIf(intSeconds = 1, "", "s") & " | " & Output.RecordCount & " row" & IIf(Output.RecordCount = 1, "", "s") & " returned."
            Output.MoveFirst
            Set QueryRS = Output
        End If

    End If

Exit_Sub:
    pLastSeconds = intSeconds
    Set Output = Nothing
    Set Parameter = Nothing
    Set dbQuery = Nothing

    DoCmd.Hourglass False

    Exit Function

Query_Error:

    intSeconds = DateDiff("s", dtTemp, Now)

    strErrNumber = Err.Number
    strErrDesc = Err.DESCRIPTION
    pLastError = strErrDesc

    MsgBox strErrDesc, vbCritical, "Error " & pDSN

    LogIt strErrDesc, , "ERROR"

    Set QueryRS = Nothing
    Resume Exit_Sub
    Resume
End Function

---

Things I tried so far

For the recordsets I tried every possible variation of

            .LockType = adLockPessimistic
            .CursorType = adUseClient
            .CursorLocation = adUseClient

The subforms handling the recordsets have all a Snapshot recordsettype, problem remains if I try dynaset. Dataentry, Addition, deletion, edits are all disabled. It's pure read-only.

I have a habit of disconnecting the recordsets using RST.ActiveConnection = Nothing so I can manipulate them afterwards, but this doesn't impact the problem either.

It can happens with very simple queries with only one field in the SELECT clause and only one field bound to it on a subform.

Reimporting all objects in a fresh accdb doesn't solve the problem either.

The solution proposed by random_answer_guy worked at first glance, which accreditate the bug hypothesis. Unfortunately my problems reappeared after some (totaly unrelated) changes in the main form. I am back with 4 or 5 subforms not showing data and adding/removing a Load event on all or part of them doesn't make any difference anymore

If you want more information about how weird is this issue, I advise you to read my comment on random_answer_guy's answer.

---

To conclude

What is extremely frustrating is that I can have 2 different forms with exactly the same properties and same fields, same SQL instruction over the same DB, same recordset management code: One is showing the data and the other doesn't !

When the problem happens, I have no other choice than erasing all objects manipulated and reimporting them from an older version or recreate them from scratch.

If this is not a bug, I am still looking for the proper word to qualify it.

Does anyone ever experienced the issue and has an explanation and/or a workaround to propose ?

Answer 1

So nobody could give at this stage a clear answer to the main question :

Why is this bug happens ?

In the meantime I have "elegantly" bypassed the issue by changing the method used for the subforms encountering the bug, from ADO to DAO.

I have created a new method in my ADO abstracting class, that actually use DAO to return a recordset (not logical, but hey...).

The code where I pass data to the form becomes :

        Set RST = Nothing
        Set RST = Oracle_CON.QueryDAORS(SQL)

        If Not RST Is Nothing Then
            Set Form_the_form_name.Recordset = RST
        End If

And here's the method QueryDAORS called :

Public Function QueryDAORS(ByVal SQL As String, Optional strTitle As String) As DAO.Recordset

    Dim RS As DAO.Recordset
    Dim dtTemp As Date
    Dim strErrNumber As Long
    Dim strErrDesc As String
    Dim intSeconds As Long

    Dim Param As Variant

    On Error GoTo Query_Error

    dtTemp = Now

    If DBcon.state <> adStateOpen Then
        Set QueryDAORS = Nothing
    Else
        DoCmd.Hourglass True

        Set pQDEF = CurrentDb.CreateQueryDef("")

        pQDEF.Connect = pPassThroughString
        pQDEF.ODBCTimeout = pTimeOut
        pQDEF.SQL = SQL

        pLastRows = 0
        pLastSQL = SQL
        pLastError = ""
        pLastSeconds = 0


        LogIt SQL, strTitle, , True


        Set RS = pQDEF.OpenRecordset(dbOpenSnapshot)

        intSeconds = DateDiff("s", dtTemp, Now)

        If RS.EOF Then
            LogIt "-- " & Format(Now, "hh:nn:ss") & " | Executed in " & intSeconds & " second" & IIf(intSeconds = 1, "", "s") & " | Now rows returned."
            Set QueryDAORS = Nothing
        Else
            RS.MoveLast
            pLastRows = RS.RecordCount
            LogIt "-- " & Format(Now, "hh:nn:ss") & " | Executed in " & intSeconds & " second" & IIf(intSeconds = 1, "", "s") & " | " & RS.RecordCount & " row" & IIf(RS.RecordCount = 1, "", "s") & " returned."
            RS.MoveFirst
            Set QueryDAORS = RS
        End If

    End If

Exit_Sub:
    pLastSeconds = intSeconds
    Set RS = Nothing

    DoCmd.Hourglass False

    Exit Function

Query_Error:

    intSeconds = DateDiff("s", dtTemp, Now)

    strErrNumber = Err.Number
    strErrDesc = Err.DESCRIPTION
    pLastError = strErrDesc

    MsgBox strErrDesc, vbCritical, "Error " & pDSN

    LogIt strErrDesc, , "ERROR"

    Set QueryDAORS = Nothing
    Resume Exit_Sub
    Resume
End Function

The property pPassThroughString is defined with another Method using the properties that I already had at my disposal in the class, because they were neccessary to open an ADO connection to the database :

Private Function pPassThroughString() As String

    Select Case pRDBMS

        Case "Oracle"
            pPassThroughString = "ODBC;DSN=" & pDSN & ";UID=" & pUsername & ";Pwd=" & XorC(pXPassword, CYPHER_KEY)

        Case "MS SQL"
            pPassThroughString = "ODBC;DSN=" & pDSN & ";DATABASE=" & pDBname & ";Trusted_Connection=Yes"

        Case "Sybase"
            pPassThroughString = "ODBC;DSN=" & pDSN & ";"

        Case Else
            MsgBox "RDBMS empty ! ", vbExclamation
            LogIt "RDBMS empty ! ", , "ERROR"

    End Select

End Function

So the issue was solved rapidly by just changing the recordset assigned to the forms from ADODB.Recordset to DAO.recordset and adapting the method called from .OpenRS to .OpenDAORS.

The only con is that with DAO I can't use this anymore to disconnect the recordset:

Set RST.ActiveConnection = Nothing

Still, I would have prefered to get an explanation and fix :(

Answer 2

I've had this same issue before and simply adding a blank Form_Load event solved the problem. No code needs to be with the Form_Load it just needs to be present.