vba 如何使用日期提示创建传递查询，然后将其加载到本地表（制作表）中？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10988225/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I create a Pass Through query with a date prompt and then load it into a local table (make table)?
提问by daniellopez46
How do I create a Pass Through query with a date prompt and then load it into a local table (make table)?
如何使用日期提示创建传递查询，然后将其加载到本地表（制作表）中？
I want to run this in access and before it goes to the server I want the query to prompt me for a date to enter as criteria and then when the results are returned I want them stored in a local table for further processing and joining on other local tables and analyis.
我想在访问中运行它，在它进入服务器之前我希望查询提示我输入一个日期作为条件，然后当结果返回时我希望它们存储在本地表中以供进一步处理和加入其他本地表和分析。
I know there is a way to do this through VBA. Below is some sample code but I am not seeing the part where I can store this into a local table in MS Access.
我知道有一种方法可以通过 VBA 做到这一点。下面是一些示例代码，但我没有看到可以将其存储到 MS Access 本地表中的部分。
/* SAMPLE CODE...not mine*/
Dim db As DAO.Database
Dim qdExtData As QueryDef
Dim strSQL As String
Set db = CurrentDb
strSQL = "SELECT * FROM TBL WHERE FIELD1 = " &
[Forms]![Form1]![Combo6]
Set qdExtData = db.CreateQueryDef("QRY_PASS_THROUGH")
qdExtData.Connect = "ODBC;DSN=???;UID=???;PWD=???;DBQ=???;"
qdExtData.SQL = strSQL
qdExtData.Close
db.Close
Set db = Nothing
/* SAMPLE CODE...not mine*/
BACKGROUND INFO: The reason I am using a Pass through query was because I copied a large amount of SQL from Peoplesoft query which includes some CASE statements and other SQL components that the Jet engine can't process but the Oracle server I am sending it to can.
背景信息：我使用传递查询的原因是因为我从 Peoplesoft 查询中复制了大量 SQL，其中包括一些 CASE 语句和其他 Jet 引擎无法处理的 SQL 组件，但我将其发送到 Oracle 服务器能够。
采纳答案by daniellopez46
Why don't you try
你为什么不试试
Dim db As DAO.Database
Dim qdExtData As QueryDef
Dim strSQL As String
Set db = CurrentDb
strSQL = "SELECT * FROM TBL WHERE FIELD1 = " &
[Forms]![Form1]![Combo6]
Set qdExtData = db.CreateQueryDef("QRY_PASS_THROUGH")
qdExtData.Connect = "ODBC;DSN=???;UID=???;PWD=???;DBQ=???;"
qdExtData.SQL = strSQL
qdExtData.Close
'Now use the Pass Thru query above as the data source for the Make table query below
strSQL = "Select QRY_PASS_THROUGH.* INTO tblPassThruResults FROM QRY_PASS_THROUGH"
db.Execute strSQL
db.Close
Set db = Nothing
However, this will work once and then you'll have to deal with deleting the previous query and table since the code above creates new ones.
但是，这将起作用一次，然后您将不得不处理删除以前的查询和表，因为上面的代码创建了新的查询和表。
A better solution is to create both the PassThruQuery and the table in development and then just reset them when you run your code:
更好的解决方案是在开发中创建 PassThruQuery 和表，然后在运行代码时重置它们：
Dim db As DAO.Database
Dim qdExtData As QueryDef
Dim strSQL As String
Set db = CurrentDb
strSQL = "SELECT * FROM TBL WHERE FIELD1 = " &
[Forms]![Form1]![Combo6]
'Reset the SQL statment for the query
Set qdExtData = db.QueryDefs("QRY_PASS_THROUGH")
qdExtData.SQL = strSQL
'Empty the table
strSQL = "DELETE * FROM tblPassThruResults"
db.Execute strSQL
'Now use the Pass Thru query above to reload the table
strSQL = "INSERT INTO tblPassThruResults Select QRY_PASS_THROUGH.* FROM QRY_PASS_THROUGH"
db.Execute strSQL
db.Close
Set db = Nothing
Watch out for your date string in the query. You may need to add delimiters. Top
回答by ardochhigh
Another approach would be to wrap the SQL in a view in the PeopleSoft system, and thereby simplify your Access code.
另一种方法是将 SQL 包装在 PeopleSoft 系统的视图中，从而简化您的访问代码。
PeopleSoft can also generate COM API's over PeopleSoft components. This should simplify the development and also protects your external application if there is a PeopleSoft upgrade (usually very 1-2 years). The raw SQL in PeopleSoft is getting more complex; an alternativethe approach is to interact with the system via component interfaces.
PeopleSoft 还可以通过 PeopleSoft 组件生成 COM API。如果有 PeopleSoft 升级（通常是 1-2 年），这应该会简化开发并保护您的外部应用程序。PeopleSoft 中的原始 SQL 变得越来越复杂；另一种方法是通过组件接口与系统交互。
There's an example on p79 of this Oracle manual: http://docs.oracle.com/cd/E28461_01/psft/acrobat/pt852tcpi-b0212.pdf
本 Oracle 手册的 p79 上有一个示例：http://docs.oracle.com/cd/E28461_01/psft/acrobat/pt852tcpi-b0212.pdf
