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