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