VBA ADODB multirow插入不起作用 [英] VBA ADODB multirow insert not working

问题描述

我试图找出如何使用ADODB在VBA excel中做多重插入。
我的问题似乎是，我无法确定正确的语法用于这个简单的任务，即使在搜索后我仍然失去了为什么它不起作用。

我没有问题，使用语句

  INSERT INTO test.dbf（'field1' ，'field3'）VALUES（'test1'，'test11'）
  

但一旦我尝试

  INSERT INTO test.dbf（'field1'，'field3'）VALUES（'test1'，'test11'），（ 'test2'，'test22'）
  

它给我以下错误

  [Microsoft] [ODBC dBase驱动程序]在SQL语句结尾处缺少分号（;）。 
  

我当然在语句末尾添加了分号，根本没有帮助，我也尝试运行该语句，指定列，全部无效。

有关我做错了什么的建议？我想避免做8,000个单独插入。

提前感谢

解决方案

多行的SQL语法插入是比ADODB / ODBC接口更新，所以他们的SQL解析器不能识别它。因此，您的选择是：

1. 使用VBA将其指定为直通查询。只要您正在执行的DBMS识别新的插入语法，这应该可以工作。但是，如果您的插入内容基于用户输入，那么您几乎肯定会将数据库暴露于注入中。 （注意：这与pass-thru无关，而是使用字符串组合来做插入，而不是使用ADODB表对象）。




2. 只需使用VBA / ADODB表对象来插入多行。这不使用较新的多行插入SQL语法，但仍然可以正常工作。尝试利用多行语法的唯一原因是，如果您有性能问题，并且还有许多其他性能选项可用。

I am trying to figure out how to do multirow inserts using ADODB in VBA excel. My problem seems to be that i can`t figure out the correct syntax to use for this simple task, even after searching I am still lost as to why it does not work.

I have no problem doing a single insert using the statement

INSERT INTO test.dbf ('field1','field3') VALUES ('test1','test11')

But as soon as I try

INSERT INTO test.dbf ('field1','field3') VALUES ('test1','test11'), ('test2','test22')

It gives me the following error

[Microsoft][ODBC dBase Driver] Missing semicolon (;) at end of SQL statement.

I of course tried adding the semicolon at the end of the statement and it was no help at all, I also tried running the statement with out specifying the columns, all to no avail.

Any suggestions as to what I am doing wrong? I would like to avoid doing 8,000 individual inserts.

Thanks in advance

解决方案

The SQL syntax for multi-row inserts is newer than the ADODB/ODBC interfaces and so their SQL parsers do not recognize it. Consequently your choices are:

1. Use VBA to specify this as a pass-thru query. This should work so long as the DBMS you are executing in recognizes the new insert syntax. However, this has the significant downside that you are almost certainly exposing your database to injection if your insert content is based on user input. (Note: this has nothing to do with pass-thru, but rather with the fact that you are using string composition to do your inserts, rather than using the ADODB table objects).

2. Just use the VBA/ADODB table objects to insert multiple rows. This does not use the newer multi-row insert SQL syntax under-the-hood, but still works fine. The only reason to try to leverage the multi-row syntax is if you have a performance problem, and there are many other performance options available.

这篇关于VBA ADODB multirow插入不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！