从访问 VBA 中的查询创建表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26892273/
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
Create a table from a query in access VBA
提问by user4095610
I have this query that I created in VBA and I want to make it into a table within VBA. Is there anyway I could do this? I have looked into a make table query but I am unsure how to create one inside VBA. Any help would be greatly appreciated!
我有一个在 VBA 中创建的查询，我想将其放入 VBA 中的表中。无论如何我可以做到这一点吗？我研究了一个制作表查询，但我不确定如何在 VBA 中创建一个。任何帮助将不胜感激！
Sub RevH()
Dim dte As String, clientQry As String, db As Database, clientQry1 As Variant
Set db = CurrentDb
dte = InputBox("What date was the Data Dump run?", "Please Input a date")
clientQry = "SELECT DISTINCT t.[CLIENT ID], t.[CLIENT NAME] " & _
"FROM FN_DataDump_ALL_" & dte & " as t WHERE " & _
" (((t.[CLIENT NAME]) Not Like ""*Test*"" ));"
If CheckQuery("NewIDs") = "Yes" Then
DoCmd.DeleteObject acQuery, "NewIDs"
End If
clientQry1 = db.CreateQueryDef("NewIDs", clientQry)
End Sub
回答by cboden
why do you want to make a new query for that? Simply execute the SQL by invoking the CurrentDb.Execute method.
为什么要为此进行新查询？只需通过调用 CurrentDb.Execute 方法来执行 SQL。
And second: the correct syntax for a query that create a new table would be:
其次：创建新表的查询的正确语法是：
SELECT YourFields INTO YourNewTable FROM YourOldTable
so simply make something like that:
所以简单地做这样的事情：
Dim clientQry as String
clientQry = "SELECT DISTINCT t.[CLIENT ID], t.[CLIENT NAME] " & _
"INTO NewTableName " & _
"FROM FN_DataDump_ALL_" & dte & " as t WHERE " & _
" (((t.[CLIENT NAME]) Not Like ""*Test*"" ));"
CurrentDb.Execute clientQry, dbFailOnError
by the way: most time it's a bad idea to make physical copies of a table. Normally you would setup ONE history table with an additional date column and dump all copies into that table. During runtime it should NEVER be requiered to add new tables / columns. Anything else is an indication of a bad data model!
顺便说一句：大多数情况下，制作表格的物理副本是一个坏主意。通常，您会设置一个带有附加日期列的历史记录表，并将所有副本转储到该表中。在运行时，永远不应该要求添加新表/列。其他任何东西都表明数据模型不好！
