这是一个有趣的问题，当他们接触到更有经验的开发人员的工作时，往往会让新的数据库开发人员感到困惑。我记得在我早期有一个Access顾问处理特定项目的时候，我自己就碰到了这个。


在很多情况下，可以在项目中使用QueryDef（保存访问查询） SQL字符串可以在哪里。事实上，有一些地方（例如在Domain Aggragate函数调用中 - DLookup（），DMax（），DAvg等），其中SQL字符串根本不适用。


更多虽然经验丰富的数据库开发人员更熟悉SQL并倾向于在S​​QL中思考，但他们通常更愿意在SQL中考虑查询，然后在VBA代码中创建它。发生这种情况的时间和地点的平衡通常取决于开发人员自己，但需要考虑的问题是：

This is an interesting question, that often confuses newer database developers when they come into contact with the work of more experienced developers. I remember bumping into this myself in my early days when we had an Access consultant in to handle a particular project.

In many cases a QueryDef (Saved Access Query) can be used in projects wherever a SQL string can. In fact, there are some places (within Domain Aggragate function calls for instance - DLookup(), DMax(), DAvg, etc) where SQL strings are not applicable at all.

More experienced db developers though, being more conversant with SQL and tending to think in SQL, would often prefer to think of a query in SQL and simply create it in the VBA code there and then. The balance of where and when this occurs is generally down to the developer themself, but issues to consider are :

1. 使用频率。
2. 它有多复杂。

如果它是从多个地方调用的 - 这是相同的查询 - 那么QueryDef的权重更大。如果它是一个很短的字符串，那么SQL字符串就有了。 QueryDefs是预优化的，因此从多个地方调用的大型复杂查询（本质上是项目的基本部分）甚至可以由经验丰富的开发人员设计为QueryDef，因为他们将看到避免重做优化阶段的好处。这是一个判断调用。


如果SQL字符串真的变得必要而不是首选，那么查询的SQL依赖于仅在运行时可用的值。考虑一个表单，允许您选择要在报表中看到的表格和字段。 SELECT和FROM子句都取决于所选择的内容，因此将它们设计为QueryDef（在设计时）是不可能的。但是，使用VBA中的SQL字符串处理它是完全合理的。我现在会说，在代码中对QueryDef进行更改实际上也是可行的，当然也可以使用MDB或ACCDB文件。尽管开发人员通常想知道他们正在使用什么，并且他们的主要对象的设计是其中的重要部分，但这是非常可疑的。可能的话，但几乎在所有情况下都要避免。


过滤完全是另一回事。报表和表单肯定有一个 WhereCondition （过滤）参数，可以在打开时传递，允许甚至为QueryDef指定过滤器。这意味着RecordSource参数（表单或报表绑定的源）很容易成为QueryDef，即使需要过滤也是如此。


我希望这给你一些考虑。其他人可能会为讨论提供自己独特的视角。

If it''s called from more than one place - that is the same query - then a QueryDef has more weight. If it''s a very short string with little to it then a SQL string has. QueryDefs are pre-optimised, so a large complex query that is called from multiple places (essentially a fundamental part of the project) may even be designed as a QueryDef by very experienced developers as they will see the benefit of avoiding redoing the optimisation phase. It''s a judgement call.

Where a SQL string really becomes necessary rather than just preferred, is where the SQL of the query depends on values available only at run-time. Consider a form that allows you to select the table and fields of what you want to see in a report. The SELECT and FROM clauses are both dependent an what is selected, so designing them into a QueryDef (at design-time) would be impossible. Handling that with a SQL string from VBA however, is perfectly reasonable. I''ll say at this time that making changes to a QueryDef in code is actually possible too, certainly with an MDB or ACCDB file. It''s highly dubious though as a developer typically wants to know what they''re working with and the designs of their main objects are an important part of that. Possible then, but to be avoided in almost all situations.

Filtering is another matter entirely of course. Reports and forms certainly, have a WhereCondition (filtering) parameter, which can be passed on opening, that allows a filter to be specified even for a QueryDef. This means that the RecordSource parameter (The source that the form or report is bound to) can easily be a QueryDef, even if filtering is required.

I hope this gives you something to consider. Others may offer their own unique perspective to the discussion.

谢谢你，NeoPa。

我很高兴你理解我的英语，你把讨论指向右边方式。

我完全明白你的意思。另外我明白我的技能还不足以让你尽可能地看到。

当然我希望其他帖子能够（对我自己）做出尽可能好的想法。

再次感谢您！

Thank you, NeoPa.
I am happy you understand my English and you point the discussion to the right way.
I fully understand what you say. Also I understand that my skill is not enough to see as far as you can.
Of course I expect other posts in order to make (for myself) as good idea as is possible.
Thank you again !

我从未学过如何将参数查询与VBA结合起来。这就是我不使用它们的一个原因。我b $ bfa查询不需要参数，我可能会设计并将其保存为查询对象，然后从VBA运行它，但我常常也喜欢存储代码的SQL与代码。这使得阅读和查看我的代码变得更加容易，我不必打开第二个对象。


这真的是我在主题上的2美分。

I have never learnt how to combine parameter queries with VBA. So thats one reason for me not using them. I

f a query does not require parameters, I might design and save it as a query object, and then run it from VBA, but often I also like that the SQL for the code is stored with the code. It makes it easier when reading and reviewing my code that I don''t have to open a second object.

Thats really my 2 cents on the subject.