ADO Recordset data not showing on form ADO Recordset data not showing on form sql sql

ADO Recordset data not showing on form

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.

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 IfExit_Sub:    pLastSeconds = intSeconds    Set RS = Nothing    DoCmd.Hourglass False    Exit FunctionQuery_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    ResumeEnd 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 SelectEnd 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 :(