I want users to be able to provide a query they made in the GUI, using a combo box, and then load that query into a recordset to do further processing on it. This fails if the query contains a user-defined function or form-based parameter.
My code looks like this:
Private Sub cmbSelectionColumn_AfterUpdate()
Dim r As DAO.Recordset
Set r = CurrentDb.OpenRecordset("SELECT DISTINCT " & EscapeSQLIdentifier(Me.cmbSelectionColumn.Value) & " FROM " & EscapeSQLIdentifier(Me.cmbSelectionTable.Value))
Do While Not r.EOF
'Do stuff
r.MoveNext
Loop
End Sub
Where cmbSelectionColumn is a user-selected column, and cmbSelectionTable is a user-selected table or query, and EscapeSQLIdentifier is a function that escapes and adds brackets to ensure the field and tablename are safe. This mostly works fine, but it fails in multiple cases, such as involving pass-through queries, user-defined functions, and form-based parameters.
Is there a way I can create a recordset from any query that works in Access, without having to worry about this?
Yes, there is, but you will have to do some trickery.
Forms support these queries just fine. And forms have a .RecordsetClone property that allows us to retrieve the recordset.
To allow us to retrieve the recordset from code, we're going to create a new blank form, and add a module to it (in fact, any form with a module will do). We'll name it frmBlank.
Then, we can adjust the code to use this form to retrieve the recordset.
Private Sub cmbSelectionColumn_AfterUpdate()
Dim r As DAO.Recordset
Dim frm As New Form_frmBlank
frm.RecordSource = "SELECT DISTINCT " & EscapeSQLIdentifier(Me.cmbSelectionColumn.Value) & " FROM " & EscapeSQLIdentifier(Me.cmbSelectionTable.Value)
Set r = frm.RecordsetClone
Do While Not r.EOF
'Do stuff
r.MoveNext
Loop
End Sub
This allows us to retrieve the recordset. The form will not pop up (since we haven't set .Visible to True), and will close once the code is done running since there is no active reference to it. I haven't yet seen any tables or queries that do work in Access, but do not work with this approach, and the performance penalty is minor. It does make for odd code and an odd blank form with blank module that will cause your database to malfunction when deleted.