如何从 Access VBA 中的 SELECT 查询创建临时表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8827394/
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 to create a temporary table from a SELECT query in Access VBA?
提问by maxko87
I'm trying to get some results (just for viewing) from a query in a small functional method in Access VBA. The query looks like this:
我正在尝试从 Access VBA 中的一个小型功能方法中的查询中获得一些结果（仅供查看）。查询如下所示：
sqlstr = "TRANSFORM Count([Deal Data ECM].[Disclosed Fees (Y/N)]) AS [CountOfDisclosed Fees (Y/N)]" & _
"SELECT [Deal Data ECM].[Deal Type]" & _
"FROM [Deal Data ECM]" & _
"WHERE ((([Deal Data ECM].[Disclosed Fees (Y/N)]) = 'Y'))" & _
"GROUP BY [Deal Data ECM].[Deal Type]" & _
"PIVOT [Deal Data ECM].[Deal Region];"
None of these work:
这些都不起作用：
DoCmd.OpenQuery sqlstr
CurrentDb.OpenRecordset sqlstr
DoCmd.RunSQL sqlstr
How can I get it to work? There's gotta be an easy way for this simple task. I'm using Access 2003.
我怎样才能让它工作？必须有一个简单的方法来完成这个简单的任务。我正在使用 Access 2003。
回答by Conrad Frix
If you took a look at the string you produced using Debug.Print sqlstrin code or ?sqlstrfrom the command window you'd see
如果您查看Debug.Print sqlstr在代码中或?sqlstr从命令窗口中使用生成的字符串，您会看到
TRANSFORM Count([Deal Data ECM].[Disclosed Fees (Y/N)]) AS [CountOfDisclosed Fees (Y/N)]SELECT [Deal Data ECM].[Deal Type]FROM [Deal Data ECM]WHERE ((([Deal Data ECM].[Disclosed Fees (Y/N)]) = 'Y'))GROUP BY [Deal Data ECM].[Deal Type]PIVOT [Deal Data ECM].[Deal Region];
Then you might notice there's no spaces between one clause and the next.
然后您可能会注意到一个子句和下一个子句之间没有空格。
adding returns where there are spaces makes it a little eaiser to see
在有空格的地方添加回报使它更容易看到
TRANSFORM Count([Deal Data ECM].[Disclosed Fees (Y/N)])
AS [CountOfDisclosed Fees (Y/N)]SELECT
[Deal Data ECM].[Deal Type]FROM
[Deal Data ECM]WHERE
((([Deal Data ECM].[Disclosed Fees (Y/N)]) = 'Y'))GROUP
BY [Deal Data ECM].[Deal Type]PIVOT
[Deal Data ECM].[Deal Region];
Try adding a space before each ending "
尝试在每个结尾前添加一个空格 "
sqlstr = "TRANSFORM Count([Deal Data ECM].[Disclosed Fees (Y/N)]) AS [CountOfDisclosed Fees (Y/N)] " & _
"SELECT [Deal Data ECM].[Deal Type] " & _
"FROM [Deal Data ECM] " & _
"WHERE ((([Deal Data ECM].[Disclosed Fees (Y/N)]) = 'Y')) " & _
"GROUP BY [Deal Data ECM].[Deal Type] " & _
"PIVOT [Deal Data ECM].[Deal Region]; "
However since you want to create a table you'll need to add a target. By adding an INTOclause between the select and from
但是，由于您要创建一个表，因此您需要添加一个目标。通过INTO在 select 和 from 之间添加一个子句
sqlstr = "TRANSFORM Count([Deal Data ECM].[Disclosed Fees (Y/N)]) AS [CountOfDisclosed Fees (Y/N)] " & _
"SELECT [Deal Data ECM].[Deal Type] " & _
"INTO YourNewTable " & _
"FROM [Deal Data ECM] " & _
"WHERE ((([Deal Data ECM].[Disclosed Fees (Y/N)]) = 'Y')) " & _
"GROUP BY [Deal Data ECM].[Deal Type] " & _
"PIVOT [Deal Data ECM].[Deal Region]; "
Note: Everytime you execute this you'll be deleting the old table. Also note you should use DoCmd.RunSQL
注意：每次执行此操作时，您都会删除旧表。另请注意，您应该使用 DoCmd.RunSQL
回答by Tony
DoCmd.RunSQLcannot be used with SELECTqueries, you need to perform an INSERT, UPDATEor DELETE
DoCmd.RunSQL不能与SELECT查询一起使用，您需要执行INSERT,UPDATE或DELETE
One option would be to create a table in your database which matches the query output and perform an INSERT ... SELECT .... You can then display the table to the user.
一种选择是在您的数据库中创建一个与查询输出匹配的表并执行INSERT ... SELECT .... 然后，您可以向用户显示该表。
The problem with this approach is needing to delete the table contents each time before running the query again.
这种方法的问题是每次再次运行查询之前都需要删除表内容。
回答by Todd
You can use a nested query to create the table. I wasn't able to nest the sql but if you save your query as say deal_timethen you can use docmd.runSQL "select * into mytable from deal_time"
您可以使用嵌套查询来创建表。我无法嵌套 sql 但如果您将查询保存为 saydeal_time那么您可以使用docmd.runSQL "select * into mytable from deal_time"
