我可以在 Access 中使用应用程序级功能(用户定义的函数、基于表单的参数)打开记录集吗? [英] Can I open a recordset using application-level features (user-defined functions, form-based parameters) in Access?

问题描述

我希望用户能够使用组合框提供他们在 GUI 中进行的查询，然后将该查询加载到记录集中以对其进行进一步处理.如果查询包含用户定义的函数或基于表单的参数，则会失败.

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.

我的代码如下所示:

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

其中 cmbSelectionColumn 是用户选择的列，cmbSelectionTable 是用户选择的表或查询，EscapeSQLIdentifier 是转义并添加括号以确保字段和表名安全的函数.这在大多数情况下工作正常，但在多种情况下会失败，例如涉及传递查询、用户定义函数和基于表单的参数.

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.

有没有一种方法可以从任何适用于 Access 的查询中创建记录集，而不必担心这个问题?

Is there a way I can create a recordset from any query that works in Access, without having to worry about this?

推荐答案

以下内容可能会提供另一种打开引用基于表单参数的 DAO 记录集的方法:

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.

这篇关于我可以在 Access 中使用应用程序级功能(用户定义的函数、基于表单的参数)打开记录集吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！