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.

[vb]
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
db.QueryDefs.Delete qd.name
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

qd.Close
Set qd = Nothing
Set conn = Nothing
Set db = Nothing

[/vb]

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.

    Ciao

    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 *