How to have a SQL Server stored procedure be the source for an Access report

In converting my Access back end database I came across a little gotcha that took a while to solve. I was trying to dynamically have a stored procedure on the server act as the record source for a Report. But what ever I tried, I kept getting an Error Message “Run-time error ‘32585’ This feature is only available in an ADP”.

Google searches did not exactly throw up an answer, but it gave me several avenues to explore. Eventually and with a bit of tweaking I eventually found a solution that works. Since the report I was converting had previously used a Query (“qryPromotionLetter”) as its datasource, I knew that I could rely on that name to provide a vehicle to hold a new dynamically created query. The problem was that the connection string I needed for my ADO.Connection didn’t seem to work with the DAO.queryDef that I was creating. But as you can see from the code below, I eventually found the solution.

    Dim conn As New ADODB.Connection
    Dim SQL As String, stConnect As String
    Dim db As DAO.Database
    Dim qd As DAO.QueryDef
    conn.Open getStrConn 'getStrConn  is function that returns the string to make an ADO connection
    Set db = CurrentDb()
    On Error Resume Next
    Set qd = db.QueryDefs("qryPromotionLetter")
    If Not IsNull(qd) Then
    ' So it exists, we must recreate it because the date might be different
    End If
    stConnect = "ODBC;driver=SQL Server;" & conn.Properties("Extended Properties") & ";"
    Set qd = db.CreateQueryDef("qryPromotionLetter")
    qd.Connect = stConnect
    qd.SQL = "EXEC dbo.qryPromotionLetter '" & letterDate & "';"  'letter date  can come from anywhere
    qd.ReturnsRecords = True

    Set qd = Nothing
    Set conn = Nothing
    Set db = Nothing

Author: Alan

I am Alan Chandler.

2 thoughts on “How to have a SQL Server stored procedure be the source for an Access report”

  1. At last I found somebody having my same problem.
    Your solution inspired my own, and maybe the best solution is to set the SQL parameter of the querydef without deleting and recreating it. In my accdb database it works.


    1. You are correct, I don’t always delete and recreate. I built a function which is stored in a common module to actually dynamically create pass through queries which I actually use in this case, but in many more. I pulled out the code so I could demonstrate the technique.

      In the beginning – to cater for changing databases, I deleted a recreated the querydef in case I had changed the database connection string. More recently I scan all the querydefs on start up and dynamically alter the connection string.

Leave a Reply

Your email address will not be published. Required fields are marked *