如何在MS Access 2003中创建参数化查询并使用其他查询/表单来填充参数并获取结果集

I'd like to be able to create a parameterized query in MS Access 2003 and feed the values of certain form elements to that query and then get the corresponding resultset back and do some basic calculations with them. I'm coming up short in figuring out how to get the parameters of the query to be populated by the form elements. If I have to use VBA, that's fine.

我希望能够在MS Access 2003中创建参数化查询,并将某些表单元素的值提供给该查询,然后返回相应的结果集并使用它们进行一些基本计算。我想知道如何通过表单元素填充查询的参数。如果我必须使用VBA,那没关系。

5 个解决方案

#1

References to the controls on the form can be used directly in Access queries, though it's important to define them as parameters (otherwise, results in recent versions of Access can be unpredictable where they were once reliable).

对表单上的控件的引用可以直接在Access查询中使用,但将它们定义为参数非常重要(否则,最新版本的Access的结果可能无法预测,只要它们曾经可靠)。

For instance, if you want to filter a query by the LastName control on MyForm, you'd use this as your criteria:

例如,如果要通过MyForm上的LastName控件过滤查询,则将其用作标准:

LastName = Forms!MyForm!LastName

Then you'd define the form reference as a parameter. The resulting SQL might look something like this:

然后,您将表单引用定义为参数。生成的SQL可能如下所示:

PARAMETERS [[Forms]!MyForm![LastName]] Text ( 255 );
SELECT tblCustomers.*
FROM tblCustomers
WHERE tblCustomers.LastName=[Forms]![MyForm]![LastName];

I would, however, ask why you need to have a saved query for this purpose. What are you doing with the results? Displaying them in a form or report? If so, you can do this in the Recordsource of the form/report and leave your saved query untouched by the parameters, so it can be used in other contexts without popping up the prompts to fill out the parameters.

但是,我会问为什么你需要为此目的保存一个查询。你在做什么结果?在表单或报告中显示它们?如果是这样,您可以在表单/报表的Recordsource中执行此操作,并保留您保存的查询不受参数的影响,因此可以在其他上下文中使用它,而不会弹出提示以填写参数。

On the other hand, if you're doing something in code, just write the SQL on the fly and use the literal value of the form control for constructing your WHERE clause.

另一方面,如果您在代码中执行某些操作,只需动态编写SQL并使用表单控件的文字值来构造WHERE子句。

#2

Here is a snippet of code. It updates a table using the parameter txtHospital:

这是一段代码。它使用参数txtHospital更新表:

Set db = CurrentDb

Set qdf = db.QueryDefs("AddHospital")
qdf.Parameters!txtHospital = Trim(Me.HospName)
qdf.ReturnsRecords = False

qdf.Execute dbFailOnError

intResult = qdf.RecordsAffected

Here is a sample of the SQL:

以下是SQL的示例:

PARAMETERS txtHospital Text(255); 

INSERT INTO tblHospitals ( 
[Hospital] )

VALUES ( 
[txtHospital] )

#3

There are three traditional ways to get around this issue:

有三种传统方法可以解决这个问题:

Name the parameter something cleaver so that the user will be prompted to enter the value when the query is run.

将参数命名为切割器,以便在运行查询时提示用户输入值。

Reference field on a form (possibly hidden)

表单上的引用字段(可能隐藏)

Build the query on the fly, and don't use parameters.

动态构建查询,不要使用参数。

I think it's just wrong to me that you would ave to inject something like [?enter ISO code of the country] or references to fields on your form like : [Forms]![MyForm]![LastName].

我认为你只需注入类似[?输入国家的ISO代码]或引用表格中的字段,如:[Forms]![MyForm]![LastName]。

It means we can't re-use the same query in more than one place, with different fields supplying the data or have to rely on the user not to foul up the data entry when the query is run. As I recall, it may be hard to use the same value more than once with the user entered parameter.

这意味着我们不能在多个地方重复使用相同的查询,不同的字段提供数据,或者必须依赖用户在运行查询时不会弄乱数据条目。我记得,用户输入的参数可能很难多次使用相同的值。

Typically I've chosen the last option an built the query on the fly, and updated the query object as needed. However, that's rife for an SQL injection attack (accidental or on purpose knowing my users), and it's just icky.

通常我选择了最后一个选项来动态构建查询,并根据需要更新查询对象。然而,这很容易发生SQL注入攻击(意外或故意了解我的用户),而且它只是icky。

So I did some digging and I found the following here (http://forums.devarticles.com/microsoft-access-development-49/pass-parameters-from-vba-to-query-62367.html):

所以我做了一些挖掘,我在这里找到了以下内容(http://forums.devarticles.com/microsoft-access-development-49/pass-parameters-from-vba-to-query-62367.html):

'Ed. Start - for completion of the example
dim qryStartDate as date
dim qryEndDate as date
qryStartDate = #2001-01-01# 
qryEndDate = #2010-01-01#   
'Ed. End

'QUOTEING "stallyon": To pass parameters to a query in VBA 
'                     is really quite simple:

'First we'll set some variables:
Dim qdf As Querydef
Dim rst As Recordset

'then we'll open up the query:
Set qdf = CurrentDB.QueryDefs(qryname)

'Now we'll assign values to the query using the parameters option:
qdf.Parameters(0) = qryStartDate
qdf.Parameters(1) = qryEndDate

'Now we'll convert the querydef to a recordset and run it
Set rst = qdf.OpenRecordset

'Run some code on the recordset
'Close all objects
rst.Close
qdf.Close
Set rst = Nothing
Set qdf = Nothing

(I haven't tested it myself, just something I collected in my travels, because every once in a while I've wanted to do this to, but ended up using one of my previously mentioned kludges)

(我自己没有测试过,只是我在旅行中收集的东西,因为我偶尔会想要这样做,但最后使用我之前提到的一个kludges)

Edit I finally had cause to use this. Here's the actual code.

编辑我终于有理由使用它。这是实际的代码。

'...
Dim qdf As DAO.QueryDef
Dim prmOne As DAO.Parameter
Dim prmTwo As DAO.Parameter
Dim rst as recordset
    '...
    'open up the query:
    Set qdf = db.QueryDefs("my_two_param_query") 'params called param_one and 
                                                 'param_two

    'link your DAP.Parameters to the query
    Set prmOne = qdf.Parameters!param_one
    Set prmTwo = qdf.Parameters!param_two

    'set the values of the parameters
    prmOne = 1 
    prmTwo = 2

    Set rst = qdf.OpenRecordset(dbOpenDynaset, _
                                            dbSeeChanges)
    '... treat the recordset as normal

    'make sure you clean up after your self
    Set rst = Nothing
    Set prmOne = Nothing
    Set prmTwo = Nothing
    Set qdf = Nothing

#4

Let's take an example. the parameterized query looks like that:

我们来举个例子吧。参数化查询看起来像这样:

Select Tbl_Country.* From Tbl_Country WHERE id_Country = _
    [?enter ISO code of the country]

and you'd like to be able to get this value (the [?enter ... country] one) from a form, where you have your controls and some data in it. Well... this might be possible, but it requires some code normalisation.

并且您希望能够从表单中获取此值([?enter ... country]),其中包含控件和一些数据。嗯......这可能是可能的,但它需要一些代码规范化。

One solution would be to have your form controls named after a certain logic, such as fid_Country for the control that will hold an id_Country value. Your can then have your query as a string:

一种解决方案是让您的表单控件以某个逻辑命名,例如fid_Country用于将保存id_Country值的控件。然后,您可以将查询作为字符串:

qr = "Select Tbl_Country.* From Tbl_Country WHERE id_Country = [fid_country]"

Once you have entered all requested data in your form, press your "query" button. The logic will browse all controls and check if they are in the query, eventually replacing the parameter by the control's value:

在表单中输入所有请求的数据后,按“查询”按钮。逻辑将浏览所有控件并检查它们是否在查询中,最终用控件的值替换参数:

Dim ctl as Control
For each ctl in Me.controls
    If instr(qr,"[" & ctl.name & "]") > 0 Then
         qr = replace(qr,"[" & ctl.name & "]",ctl.value)
    End if
Next i

Doing so, you will have a fully updated query, where parameters have been replaced by real data. Depending on the type of fid_country (string, GUID, date, etc), you could have to add some extra double quotes or not, to get a final query such as:

这样,您将获得一个完全更新的查询,其中参数已被实际数据替换。根据fid_country的类型(字符串,GUID,日期等),您可能需要添加一些额外的双引号,以获得最终查询,例如:

qr = "Select Tbl_Country.* From Tbl_Country WHERE id_Country = ""GB"""

Which is a fully Access compatible query you can use to open a recordset:

这是一个完全Access兼容的查询,可用于打开记录集:

Set rsQuery = currentDb.openRecordset(qr)

I think you are done here.

我想你在这里完成了。

This subject is critical when your objective is to developp Access applications. You have to offer users a standard way to query data from their GUI, not only to launch queries, but also to filter continuous forms (just in the way Excel do it with its "autofilter" option) and manage reports parameters. Good luck!

当您的目标是开发Access应用程序时,此主题至关重要。您必须为用户提供一种从GUI查询数据的标准方法,不仅可以启动查询,还可以过滤连续表单(就像Excel使用“自动过滤”选项一样)并管理报表参数。祝好运!

#5

the easy method is here Microsoft 'setparameter' info page

简单的方法是微软'setparameter'信息页面

DoCmd.SetParameter "frontMthOffset", -3
DoCmd.SetParameter "endMthOffset", -2
DoCmd.OpenQuery "QryShowDifference_ValuesChangedBetweenSELECTEDMonths"

where the SQL of the Access query includes [frontMthOffset] actually in the SQL. e.g.

Access查询的SQL实际上在SQL中包含[frontMthOffset]。例如

"select blah from mytable where dateoffset=[frontMthOffset]"

It all just works!

一切正常!

#1