Thomas G Thomas G - 3 months ago 13
SQL Question

ADO Recordset data not showing on form

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

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