the musings of an ancient geek

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