使用 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

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-11 17:21:29  来源:igfitidea点击:

Insert multiple records in a single time using VBA Access

sqlvbams-access-2007

提问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 ALLqualifier, with and without the SELECTkeyword, 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  ... )