使用 VBA Access 一次性插入多条记录
声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow
原文地址: http://stackoverflow.com/questions/12012547/
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
Insert multiple records in a single time using VBA Access
提问by ApplePie
I am trying to avoid using multiple INSERT SQL queries but if I must I will. I have read over therethat you can simply use INSERT INTO table(field) SELECT 'value' UNION ALL SELECT ...
however I am having trouble with the syntax of it under MS Access 2007. I have tried a few variants but none works: I always get either "incorrect syntax" or "missing operator" error. I have tried with and without the ALL
qualifier, with and without the SELECT
keyword, etc. Here's how I am creating the query using VBA:
我试图避免使用多个 INSERT SQL 查询,但如果必须的话,我会这样做。我在那里读到你可以简单地使用,INSERT INTO table(field) SELECT 'value' UNION ALL SELECT ...
但是我在 MS Access 2007 下遇到了它的语法问题。我尝试了一些变体,但没有一个有效:我总是得到“不正确的语法”或“缺少运算符”的错误。我尝试过使用和不使用ALL
限定符,使用和不使用SELECT
关键字等。这是我使用 VBA 创建查询的方法:
sql_query = "INSERT INTO " & tmp_tbl_name & " (transit)"
For Each xlCell In xlRange
sql_query = sql_query & " SELECT '" & xlCell.Value & "' "
sql_query = sql_query & "UNION"
Next xlCell
sql_query = Mid(sql_query, 1, Len(sql_query) - 6)
DoCmd.RunSQL (sql_query)
Here's a shortened sample of the query I am generating:
这是我生成的查询的缩短示例:
INSERT INTO tmp_tbl_2012_08_17_15_44_03 (transit)
SELECT 'L02_NA010001'
UNION
SELECT 'L13_PB010001'
UNION
SELECT 'L31_US020001'
UNION
SELECT 'L33_EX020010'
...
The table has only one VARCHAR(255)
field and no relations to any other table (it's a temporary table to process a few things and keep only some elements of it).
该表只有一个VARCHAR(255)
字段,与任何其他表都没有关系(它是一个临时表,用于处理一些事情并只保留其中的一些元素)。
Many thanks !
非常感谢 !
回答by Jim
I think you can use good ol' DAO to do this rather quickly:
我认为你可以使用好的 ol' DAO 来相当快地做到这一点:
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("tmp_tbl_2012_08_17_15_44_03", dbOpenDynaset)
For Each xlCell In xlRange
With rs
.AddNew
.Fields("transit") = xlCell.Value
.Update
End With
Next xlCell
rs.Close
db.Close
回答by Fionnuala
It will probably be easier to run the statements one at a time in MS Access, the UNION will require FROM Table for each UNION statement, which means the FROM table will have to contain just one row, or a little fancy footwork.
在 MS Access 中一次运行一个语句可能会更容易,UNION 将要求每个 UNION 语句的 FROM 表,这意味着 FROM 表将只包含一行,或者一些花哨的步法。
INSERT INTO tmp_tbl_2012_08_17_15_44_03 (transit)
SELECT Transit FROM (
SELECT DISTINCT 'L02_NA010001' As Transit FROM tablename
UNION
SELECT DISTINCT 'L13_PB010001' As Transit FROM tablename
UNION ... )