UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Bind A Report To Adodb.recordset From Mysql, Access 2007    
 
   
Akudey
post Jul 15 2017, 09:28 AM
Post#1



Posts: 25
Joined: 17-August 13



Hi all
Am using a disconnected Recordset that are bound to Forms throughout the project, no ODBC table at the client side. I use ADODB Command object to execute all queries against MySQL Server.

I know of the fact that MS Access don't support ADODB Recordset as RecordSource for Reports from Choosing between DAO and ADO, though the link is not available any more.



Please, is there any work around to this. Because I don't want to use DAO Recordset.

Thanks for any thoughts.
Go to the top of the page
 
LPurvis
post Jul 16 2017, 10:30 PM
Post#2


UtterAccess Editor
Posts: 16,071
Joined: 27-June 06
From: England (North East / South Yorks)


Hi

I'm afraid a DAO recordset would get you no further forward.
Binding to a recordset is (/was) only possible in an ADP application. And even then, it was only supported for pretty simple reports. (i.e. more or less straight forward lists without aggregation.)
If that is the type of list report you have then you can use an unbound report to achieve your goal. (See "Report Unbound" in the examples page linked to in my sig below.)

Anything more than that, and you'll need to use that recordset to (iteratively) insert the rows into a local (temp) table and bind the report that that.
Or, you could persist the recordset as XML and attempt to import that, but ADO persisted XML isn't pretty - it's all attributes.

Cheers

--------------------
Go to the top of the page
 
Akudey
post Jul 17 2017, 06:42 AM
Post#3



Posts: 25
Joined: 17-August 13



Thank you very much Mr. Purvis,
Every thing is straight forward, all aggregation is done at the Server Side,
So is kind of a list Report. I have downloaded the sample of the Unbound Report and it look promising.

But Sir, do you know anything of PassThroughQuery to reach the Server? So that I can have more leverage on the Recordset if possible.
Go to the top of the page
 
Akudey
post Jul 17 2017, 11:25 AM
Post#4



Posts: 25
Joined: 17-August 13



I convert the DAO to ADO specifics and it works

CODE
'MySQL  connection from a general Modul
Public Function cnxn() As ADODB.Connection
' Create connection to database server
    Dim cn As New ADODB.Connection
    Dim strCnxn As String
      
    ' Set connection string variables
    strCnxn = "Provider=MSDASQL;" & _
        "DRIVER={MySQL ODBC 5.3 Unicode Driver};" & _
        "SERVER=localhost;" & _
        "PORT=3306"& _
        "OPTION=32;" & _
        "DATABASE=ssms_old;" & _
        "UID=ssmsu;" & _
        "PWD=xxx"
    
    cn.CursorLocation = adUseClient
    cn.ConnectionTimeout = mintCnxnTimeout
    cn.Open strCnxn
    Set cnxn = cn
End Function
'
'
'
'From Report Class Module
Private rst As New ADODB.Recordset

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    
    Dim ctl As Access.Control
    
    If Not rst.EOF Then
        For Each ctl In Me.Detail.Controls
            If ctl.ControlType = acTextBox Then
                If ctl.ControlSource = "" Then
                    ctl.Value = rst(ctl.Name).Value
                End If
            End If
        Next
        
        rst.MoveNext
        Me.NextRecord = rst.EOF
    End If
    
End Sub

Private Sub PageHeaderSection_Format(Cancel As Integer, FormatCount As Integer)
    Dim cmd As New ADODB.Command
    Dim strSQL As String
      
    strSQL = "SELECT `ProductID`, `ProductName` FROM `tblProducts`;"
    cmd.CommandText = strSQL
    cmd.CommandType = adCmdText
    Set cmd.ActiveConnection = cnxn()
    
    Set rst = cmd.Execute
End Sub

Private Sub PageFooterSection_Format(Cancel As Integer, FormatCount As Integer)
    rst.Close
    Set rst = Nothing
End Sub


Thanks for the resource you point me to. cheers.gif
Go to the top of the page
 
LPurvis
post Jul 18 2017, 08:23 AM
Post#5


UtterAccess Editor
Posts: 16,071
Joined: 27-June 06
From: England (North East / South Yorks)


Hi

Ah right, yes you mean changing the DAO recordset example code in the demo to ADO recordsets... yes that's essentially the same concept-wise.
As for moving to a passthrough, yes that's a perfectly valid way for reporting too.
Reports can tend to load a recordset twice (and to view the report in design mode requires executing the passthrough too, which is a bit of a time lag when you're just wanting to change something). So those are occasionally downsides to working with a PT as the source. But it works just fine. (Though sub-reporting is an issue, but you don't seem to have any need for that.) You can always bind to nothing or a local table, and execute, import or assign the PT as the source at runtime when the report loads.

Cheers

--------------------
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    21st October 2017 - 09:40 PM