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