How do I execute a saved query in MS Access 2007 in VBA?
如何在VBA中的MS Access 2007中执行已保存的查询？
I do not want to copy and paste the SQL into VBA. I rather just execute the name of the query.
我不想将SQL复制并粘贴到VBA中。我宁愿只执行查询的名称。
This doesn't work ... VBA can't find the query.
这不起作用... VBA无法找到查询。
CurrentDb.Execute queryname
3 个解决方案
#1
34
You can do it the following way:
您可以通过以下方式执行此操作：
DoCmd.OpenQuery "yourQueryName", acViewNormal, acEdit
OR
要么
CurrentDb.OpenRecordset("yourQueryName")
#2
8
You should investigate why VBA can't find queryname.
您应该调查VBA无法找到queryname的原因。
I have a saved query named qryAddLoginfoRow. It inserts a row with the current time into my loginfo table. That query runs successfully when called by name by CurrentDb.Execute.
我有一个名为qryAddLoginfoRow的已保存查询。它将当前时间的一行插入到我的loginfo表中。当由CurrentDb.Execute按名称调用时，该查询成功运行。
CurrentDb.Execute "qryAddLoginfoRow"
My guess is that either queryname is a variable holding the name of a query which doesn't exist in the current database's QueryDefs collection, or queryname is the literal name of an existing query but you didn't enclose it in quotes.
我的猜测是，queryname是一个变量，它包含当前数据库的QueryDefs集合中不存在的查询名称，或者queryname是现有查询的文字名称，但您没有将其括在引号中。
Edit: You need to find a way to accept that queryname does not exist in the current db's QueryDefs collection. Add these 2 lines to your VBA code just before the CurrentDb.Execute line.
编辑：您需要找到一种方法来接受当前db的QueryDefs集合中不存在queryname。在CurrentDb.Execute行之前将这两行添加到您的VBA代码中。
Debug.Print "queryname = '" & queryname & "'"
Debug.Print CurrentDb.QueryDefs(queryname).Name
The second of those 2 lines will trigger run-time error 3265, "Item not found in this collection." Then go to the Immediate window to verify the name of the query you're asking CurrentDb to Execute.
这两行中的第二行将触发运行时错误3265，“此集合中找不到项目”。然后转到立即窗口以验证您要求CurrentDb执行的查询的名称。
#3
3
To use CurrentDb.Execute, your query must be an action query, AND in quotes.
要使用CurrentDb.Execute，您的查询必须是动作查询，并且在引号中为AND。
CurrentDb.Execute "queryname"
#1
34
You can do it the following way:
您可以通过以下方式执行此操作：
DoCmd.OpenQuery "yourQueryName", acViewNormal, acEdit
OR
要么
CurrentDb.OpenRecordset("yourQueryName")
#2
8
You should investigate why VBA can't find queryname.
您应该调查VBA无法找到queryname的原因。
I have a saved query named qryAddLoginfoRow. It inserts a row with the current time into my loginfo table. That query runs successfully when called by name by CurrentDb.Execute.
我有一个名为qryAddLoginfoRow的已保存查询。它将当前时间的一行插入到我的loginfo表中。当由CurrentDb.Execute按名称调用时，该查询成功运行。
CurrentDb.Execute "qryAddLoginfoRow"
My guess is that either queryname is a variable holding the name of a query which doesn't exist in the current database's QueryDefs collection, or queryname is the literal name of an existing query but you didn't enclose it in quotes.
我的猜测是，queryname是一个变量，它包含当前数据库的QueryDefs集合中不存在的查询名称，或者queryname是现有查询的文字名称，但您没有将其括在引号中。
Edit: You need to find a way to accept that queryname does not exist in the current db's QueryDefs collection. Add these 2 lines to your VBA code just before the CurrentDb.Execute line.
编辑：您需要找到一种方法来接受当前db的QueryDefs集合中不存在queryname。在CurrentDb.Execute行之前将这两行添加到您的VBA代码中。
Debug.Print "queryname = '" & queryname & "'"
Debug.Print CurrentDb.QueryDefs(queryname).Name
The second of those 2 lines will trigger run-time error 3265, "Item not found in this collection." Then go to the Immediate window to verify the name of the query you're asking CurrentDb to Execute.
这两行中的第二行将触发运行时错误3265，“此集合中找不到项目”。然后转到立即窗口以验证您要求CurrentDb执行的查询的名称。
#3
3
To use CurrentDb.Execute, your query must be an action query, AND in quotes.
要使用CurrentDb.Execute，您的查询必须是动作查询，并且在引号中为AND。
CurrentDb.Execute "queryname"