CODEWITHSUNDEEP
vbams-access
Erik A
Erik A

Reputation: 32632

Can I open a recordset using application-level features (user-defined functions, form-based parameters) in Access?

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?

Upvotes: 0

Views: 93

Answers (2)

Lee Mac
Lee Mac

Reputation: 16015

The following may present an alternative approach to opening DAO recordsets which reference form-based parameters:

Dim db As DAO.Database
Dim pr As DAO.Parameter
Set db = CurrentDb

With db.CreateQueryDef("", "SELECT DISTINCT " & EscapeSQLIdentifier(Me.cmbSelectionColumn.Value) & " FROM " & EscapeSQLIdentifier(Me.cmbSelectionTable.Value))
    For Each pr In .Parameters
        pr.Value = Eval(pr.Name)
    Next pr
    With .OpenRecordset
        If Not .EOF Then
            .MoveFirst
            Do Until .EOF
                ' Do stuff
                .MoveNext
            Loop
        End If
        .Close
    End With
End With

Here, since references to objects outside of the scope of the query (such as references to form controls) become query parameters whose parameter name matches the original reference, the parameter name is evaluated to yield the value held by the form control, and the parameter value is then updated to the result of this evaluation.

Upvotes: 1

Erik A
Erik A

Reputation: 32632

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.

Upvotes: 1

Related Questions