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 ?

Nippon

Do you have a Form_Load event for these Forms? Odd question I know, but at one point I had almost the same issue and simply adding a Form_Load event to the Form fixed it. No code in the event just a blank Form_Load... – Butz

Usually the display messes up in two cases. #1, you have columns with null bits, and #2, you don’t have a row version column. I would look into both. You also want to ensure in all cases you have a PK column. – Strange

@ThomasG, I noticed that you said the frontend is compiled. Debug -->> Compile VBA Project can really cause some strange behavior. Yes, it checks syntax, but it also creates a lot of junk code that can cause your application to behave strangely. Check out this answer for more info. I suggest exporting all of your modules, forms, objects, etc. into a clean access file, and be sure to not compile. – Martian

@JosephWood it happens even on accdb/not compiled version – Nippon

@ThomasG, have you ever tried exporting all modules, forms, etc. and subsequently import them into a new file? Also, not sure what you mean about the accdb/not compiled version. Are you saying that this file has never had the VBA compiler run on it? Just because you make some changes, don't run the VBA compiler, and save the file, doesn't mean it isn't completely "compiled" (i.e. there are still OpCodes and ExCodes lurking). – Martian

@JosephWood: Never using "Compile" is hardly practical. The correct way to clean up compiled VBA code in Access is to run a full Decompile cycle as explained here: https://mcmap.net/q/389763/-how-does-one-decompile-and-recompile-a-database-application -- Doing this may be worth a try, though. – Annitaanniversary

@Andre, I think you misunderstand me. I compile my projects at least once an hour, but when I'm sure my code is correct and everything is working fine, I immediately export all of my code, import it into a brand new file, attach any required libraries, and save it (without compiling as there is no need, the code is correct). If you were to compile after this, all you would be doing is creating useless junk code that as I've said many times, can cause strange behavior. This practice is endorsed by Rob Bovey as well as Chip Pearson. And yes, you can do this in Access as well. – Martian

I tried this as specified in the question : Reimporting all objects in a fresh accdb doesn't solve the problem either. The issue isn't the code, it's something in the form object. – Nippon

In the meantime. I elegantly bypassed the issue: I added a .QueryDAORS method to my class abstracting ADO. It takes also a SQL query in input and return a recordset, but using DAO by creating a temp QUeryDef. I just had to redeclare my temp recordsets as DAO instead of ADODB and change the method called. That was fast. I noticed that most of the forms that had the ADO display issue still refused to show the data. I had to recreate the forms from scratch prior to use the DAO method, and now they all work perfectly. I am sticking with this. The issue was at the form level for sure. – Nippon

Answer 1

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.

Butz

To add to the weirdness: On the same form I had 4 subforms with this problem. I added a Load event to the first one. Recompiled, All subforms are magically working. I am telling to myself, "ok let’s be smart and add a Load event to all the subforms", I proceed, recompile, and there NO ONE were working again ! I then removed the Load event to all of them but the first one, recompiled, and all of them are working. I just don't know what the heck is this. MS Access is definitely not an exact science. – Nippon

Although, this saved me for a few days...the problem is back :-) I tried every possible combination of OnLoad/OnOpen events in various forms but nothing works and I am back at the problem root with 4 or 5 subforms not showing data. So I obviously keep the upvote on your proposal, but remove the answered mark because I want this question alive and I am willing to set a bounty on it. – Nippon

Answer 2

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 :(

Nippon