CODEWITHSUNDEEP
mysqlms-accessstored-procedures
Sonia Hamilton
Sonia Hamilton

Reputation: 4409

Bind Access Form/Report to results from MySQL Stored Procedure

I'm trying to change the recordset of a Report (or Form) to be the results of a dynamically called MySQL stored procedure, in the Report_Load event. Specifically, how would I set up the connection?

I've looked at Bind Access form to the results from a Stored Procedure, as well as How to bind Microsoft Access forms to ADO recordsets. I'm already successfully connecting to the stored procedure using hard-coded values in a pass-thru query, as detailed in Calling Stored Procedures and other SQL statements from MS Access 2003 (Pass Through).

Here's the example code from Bind Access form to the results from a Stored Procedure - I want to setup the connection to use MySQL instead of SQL Server:

With cn
    .Provider = "Microsoft.Access.OLEDB.10.0"
    .Properties("Data Provider").Value = "SQLOLEDB"
    .Properties("Data Source").Value = "Server"
    .Properties("Integrated Security").Value = "SSPI"
    .Properties("Initial Catalog").Value = "Test"
    .Open
End With

I'm using Access 2007.

Out of interest, here is the code I was using to try to modify the sp call, giving an error "32585 this feature is only available in an ADP". Gord Thompson's suggestion of modifying the actual query works, so I'm using that.

    If Not CurrentProject.AllForms("foo_frm").IsLoaded Then
        'use hard-coded query (stored procedure) for dev work
        Exit Sub
    End If

    Dim action, startdate, enddate As String
    action = Forms![foo_frm].txtAction

    If action = "cmdDaily" Then
        startdate = Forms![foo_frm].txtYesterday
        enddate = Forms![foo_frm].txtToday
    Else
        startdate = Forms![foo_frm].cboStartDate
        enddate = Forms![foo_frm].cboEndDate
    End If

    Dim cn As New ADODB.Connection
    Dim strConnection As String

    strConnection = "ODBC;DSN=Foo01;UID=root;PWD=Secret;DATABASE=bar"
    With cn
       .Provider = "MSDASQL"
       .Properties("Data Source").Value = strConnection
       .Open
    End With

    Dim prmStartDate, prmEndDate As New ADODB.Parameter
    Dim cmd As New ADODB.Command

    Set prmStartDate = cmd.CreateParameter("startdate", adDate, adParamInput)
    prmStartDate.Value = CDate(startdate)
    cmd.Parameters.Append (prmStartDate)

    Set prmEndDate = cmd.CreateParameter("enddate", adDate, adParamInput)
    prmEndDate.Value = CDate(enddate)
    cmd.Parameters.Append (prmEndDate)

    With cmd
        .ActiveConnection = cn
        .CommandText = "qux_sp"
        .CommandType = adCmdStoredProc
        Set Me.Recordset = .Execute
    End With

Upvotes: 2

Views: 2559

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123419

You already have the report working with a pass-through query, so rather than using code to fiddle with the row source of the report why not just leave the report bound to the pass-through query and use code to tweak its SQL statement and call the stored procedure with the parameters you want?

For example, the following VBA code...

Sub TweakPassThroughQuery()
Dim testID As Long
Dim qdf As DAO.QueryDef, rst As DAO.Recordset
testID = 2  ' this is the parameter we really want to pass to the stored procedure
Set qdf = CurrentDb.QueryDefs("ptq_myproc")
Debug.Print "Existing pass-through query..."
Debug.Print "    " & qdf.SQL
Set rst = qdf.OpenRecordset
Debug.Print "...returns a value like this:"
Debug.Print "    " & rst!col1
rst.Close
Set rst = Nothing
' now tweak the stored procedure call
qdf.SQL = "CALL myproc(" & testID & ")"
Debug.Print "Modified pass-through query..."
Debug.Print "    " & qdf.SQL
Set rst = qdf.OpenRecordset
Debug.Print "...returns a value like this:"
Debug.Print "    " & rst!col1
rst.Close
Set rst = Nothing
Set qdf = Nothing
End Sub

...displays the following output:

Existing pass-through query...
    CALL myproc(1)
...returns a value like this:
    foo
Modified pass-through query...
    CALL myproc(2)
...returns a value like this:
    bar

Upvotes: 1

Related Questions