在DAO和ADOX中创建查询 [英] Creating Queries in DAO vs ADOX
问题描述
Hello All，
最好在DAO中创建一个查询，其中一个报告有4个子报告
每个记录源都是在运行时创建的查询和
所有内容都在1个MDB文件中？
从我读过和体验过的内容看来DAO是进入的方式
这种情况，那么什么时候使用ADOX来创建查询呢？
为什么我会问这个问题？我创建了一个使用DAO的MDB文件，但是
想要将所有内容移植到ADO。我在报告底层的查询上遇到了问题。在生成报告之前，似乎查询不会反映任何数据。我最初在以下代码行中得到了一个
错误：
catDB.Views.Append" qryTempRptOrderMTD"，cmd1
错误内容如下：
运行时错误：对象或提供程序无法执行
请求的操作。 />
在VB中退出行后，似乎代码运行到
完成停止在运行报告的表单中的end if中，但
报告中没有数据。当我查看数据库窗口时，
查询是不可见的，但当我选择表后跟查询
时，临时查询现在就在那里。如果我点击一个，那么数据
显示。毋庸置疑，问题似乎记录在
http://msdn.microsoft.com/library/de...adocreateq.asp 。
因此我原来的问题。
对于原始错误，我已经创建了一个新工作组
http://support.microsoft.com/kb/286376/ 消除了第一个
错误。我看到一个错误，表示报告在第一个子报告中看不到第一个
查询，但是在预览时报告仍然有
没有它&在将焦点更改为
另一个对象选项卡&之前，查询是不可见的。返回查询选项卡。所以我仍然回到
方形和我的问题。
TIA！
-
问候，
Greg Strong
Hello All,
Is it better to create a query in DAO where a report has 4 sub-reports
each of whose record source is a query created at runtime and
everything is in 1 MDB file?
From what I''ve read and experienced it appears DAO is the way to go in
this situation, so when is it good to use ADOX to create queries?
Why do I ask the question? I''ve created a MDB file which uses DAO, but
wanted to port everything to ADO. I kind of ran up against the wall on
the queries underlying the report. It seems like the queries do NOT
reflect any data until after the report is generated. I am getting an
error initially on the following line of code:
catDB.Views.Append "qryTempRptOrderMTD", cmd1
The error reads as follows:
Run-time error: Object or provider is not capable of performing
requested operation.
After stepping out of the line in VB it appears the code runs to
completion stopping in the ''end if'' in the form to run the report, but
the report has no data in it. When I look at the database window the
queries are not visible, but when I select tables followed by queries
the temporary queries are now there. If I click on one, then data
shows. Needless to say the problem appears to be documented at
http://msdn.microsoft.com/library/de...adocreateq.asp.
Hence my original question.
For the original error I''ve created a new workgroup per
http://support.microsoft.com/kb/286376/ which eliminated the first
error. I''m see an error which says the report can not see the 1st
query in the 1st sub-report, but the report when in preview still has
nothing in it & the queries are not visible until changing focus to
another object tab & back to the query tab. So I''m still back to
square one and my question.
TIA!
--
Regards,
Greg Strong
推荐答案
我从不使用ADOX创建查询（我从不使用ADOX）。
Private Sub CreateQuery（ByVal Name As Variant，ByVal SQL As String）
With CurrentProject.Connection
On Error Resume Next
.Execute" DROP PROCEDURE" &安培;姓名
On Error GoTo 0
。执行创建程序 &安培;姓名& " AS &安培; SQL
结束
结束子
子测试（）
CreateQuery" QueryTemp" ，SELECT * FROM Table1
End Sub
当然，也可以在（从帮助文件中）指定参数：
CREATE PROCEDURE程序
[param1 datatype [，param2 datatype [，...]] AS sqlstatement
** ***
CREATE VIEW视图[（field1 [，field2 [，...]]）] AS选择语句
（TTBOMK这个具有相同的结果
CREATE程序程序[（field1 [，field2 [，...]]）] AS
selectstatement
*****
我当然有一个ADO的最新版本的参考。
I never use ADOX for creating queries (I never use ADOX for anything).
Private Sub CreateQuery(ByVal Name As Variant, ByVal SQL As String)
With CurrentProject.Connection
On Error Resume Next
.Execute "DROP PROCEDURE " & Name
On Error GoTo 0
.Execute "Create Procedure " & Name & " AS " & SQL
End With
End Sub
Sub test()
CreateQuery "QueryTemp", "SELECT * FROM Table1"
End Sub
Of course, one can also specify parameters as in (from the help file):
CREATE PROCEDURE procedure
[param1 datatype[, param2 datatype[, ...]] AS sqlstatement
*****
CREATE VIEW view [(field1[, field2[, ...]])] AS selectstatement
(TTBOMK this has the same result as
CREATE Procedure procedure [(field1[, field2[, ...]])] AS
selectstatement
*****
I have, of course, a reference to an up-to-date version of ADO.
也许你可以在一个MDB文件中解释为什么你想把所有内容都移植到ADO上.DAO优化用于喷气发动机，所以
为什么要使用其他东西？
另外 -
你的查询真的需要在运行时创建吗？那不是
参数查询可以解决这个问题吗？
" Greg Strong" < NoJunk@NoJunk4U2.com>在消息中写道
news：qn ******************************** @ 4ax.com ...
Perhaps you can explain why you wanted to port everything to ADO when it''s
all within one MDB file. DAO is optimized for use with the Jet engine, so
why use anything else?
Also -
Do your queries really need to be created at runtime? Wouldn''t
parameter queries do the trick?
"Greg Strong" <NoJunk@NoJunk4U2.com> wrote in message
news:qn********************************@4ax.com...
大家好，
在DAO中创建一个查询更好，报告有4个子报告
每个记录源都是在运行时创建的查询和
所有内容都在1个MDB文件中？
从我所阅读和体验到的内容看来，DAO是进入这种情况的方式，所以什么时候使用ADOX来创建查询？
为什么我会问这个问题？我创建了一个使用DAO的MDB文件，但
希望将所有内容移植到ADO。我在报告的基础上查询了一些问题。在生成报告之前，似乎查询不会反映任何数据。我最初在以下代码行中出现了
错误：
catDB.Views.Append" qryTempRptOrderMTD"，cmd1
错误内容如下：
运行时错误：对象或提供程序无法执行请求的操作。
在VB中退出行后，代码运行到
完成停止在''end if''表单中运行报告，但报告中没有数据。当我查看数据库窗口时，
查询不可见，但当我选择表后跟查询时，临时查询现在就在那里。如果我点击一个，那么数据显示。毋庸置疑，问题似乎记录在
http://msdn.microsoft.com/library/de...adocreateq.asp 。因此我的原始问题。
对于原始错误，我已经创建了一个新的工作组
http://support.microsoft.com/kb/286376/ 消除了第一个
错误。我看到一个错误，表示报告无法在第一个子报告中看到第一个查询，但是在预览时的报告仍然没有任何内容&在将焦点更改为另一个对象选项卡&之前，查询不可见。返回查询选项卡。所以我还是回到了原点和我的问题。
TIA！
-
问候，
Greg Strong
Hello All,
Is it better to create a query in DAO where a report has 4 sub-reports
each of whose record source is a query created at runtime and
everything is in 1 MDB file?
From what I''ve read and experienced it appears DAO is the way to go in
this situation, so when is it good to use ADOX to create queries?
Why do I ask the question? I''ve created a MDB file which uses DAO, but
wanted to port everything to ADO. I kind of ran up against the wall on
the queries underlying the report. It seems like the queries do NOT
reflect any data until after the report is generated. I am getting an
error initially on the following line of code:
catDB.Views.Append "qryTempRptOrderMTD", cmd1
The error reads as follows:
Run-time error: Object or provider is not capable of performing
requested operation.
After stepping out of the line in VB it appears the code runs to
completion stopping in the ''end if'' in the form to run the report, but
the report has no data in it. When I look at the database window the
queries are not visible, but when I select tables followed by queries
the temporary queries are now there. If I click on one, then data
shows. Needless to say the problem appears to be documented at
http://msdn.microsoft.com/library/de...adocreateq.asp. Hence my original question.
For the original error I''ve created a new workgroup per
http://support.microsoft.com/kb/286376/ which eliminated the first
error. I''m see an error which says the report can not see the 1st
query in the 1st sub-report, but the report when in preview still has
nothing in it & the queries are not visible until changing focus to
another object tab & back to the query tab. So I''m still back to
square one and my question.
TIA!
--
Regards,
Greg Strong
On Sun，2005年11月6日00:08:13 GMT，MacDermott < ma ******** @ nospam.com>
写道：
On Sun, 06 Nov 2005 00:08:13 GMT, "MacDermott" <ma********@nospam.com>
wrote:
也许您可以解释为什么要将所有内容移植到ADO什么时候它都在一个MDB文件中。 DAO优化用于Jet引擎，所以
为什么要使用其他东西？
Perhaps you can explain why you wanted to port everything to ADO when it''s
all within one MDB file. DAO is optimized for use with the Jet engine, so
why use anything else?
了解差异。我和DAO一起工作MDB所以决定
make switch。无论如何，从我读过的DAO开始不再是由MS开发的，而ADO是首选的方式。至少这是我记得
阅读。
-
问候，
Greg Strong
To learn of the differences. I had working MDB with DAO so decided to
make switch. Anyhow from what I''ve read DAO is no longer being develop
by MS with ADO being the preferred way. At least that is what I recall
reading.
--
Regards,
Greg Strong
这篇关于在DAO和ADOX中创建查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
