vba 使用 INSERT INTO 将数据写入 access 数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9103867/
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
Using INSERT INTO to write data into access database
提问by thebiglebowski11
I am trying to use the INSERT INTO command to write data into an Access 2007 file from a excel 2010 file. The data is contained in the "NewProj" worksheet in the Tool_Selector.xlsm excel file and it needs to be written to the "Tool_Database.mdb" file but unfortunately I have received several different errors.. This is what I currently have
我正在尝试使用 INSERT INTO 命令将数据从 excel 2010 文件写入 Access 2007 文件。数据包含在 Tool_Selector.xlsm excel 文件的“NewProj”工作表中，需要将其写入“Tool_Database.mdb”文件，但不幸的是我收到了几个不同的错误。这就是我目前所拥有的
Sub AddData()
Dim Cn As ADODB.Connection
Set Cn = New ADODB.Connection
Cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Tools_Dev\_Tool_Selector\Tool_Selector.xlsm;Extended Properties=Excel 8.0;" _
& "Persist Security Info=False"
Cn.Execute "INSERT INTO Project_Names IN 'D:\Tool_Database\Tool_Database.mdb' SELECT * FROM Worksheets("NewProj").Range("A2").Value"
Cn.Close
Set Cn = Nothing
End Sub
I got the code from this thread: Insert Data from Excel into Access using VBA
我从这个线程得到了代码：Insert Data from Excel into Access using VBA
I just need to transfer the value of individual cells.
我只需要传输单个单元格的值。
Thanks.
谢谢。
回答by Kittoes0124
The problem is that you're using VBA code IN the SQL statement. The following line will compile but fail upon execution:
问题是您在 SQL 语句中使用了 VBA 代码。以下行将编译但在执行时失败：
Cn.Execute "INSERT INTO Project_Names IN 'D:\Tool_Database\Tool_Database.mdb' SELECT * FROM Worksheets("NewProj").Range("A2").Value"
It fails because you're still in the SELECT statement when you make use the VBA functions of Worksheets(), .Range, and .Value. IIRC, you should be able to use SQL syntax to select a particular range from a sheet. The proper syntax for that should be:
它失败是因为当您使用 Worksheets()、.Range 和 .Value 的 VBA 函数时，您仍在 SELECT 语句中。IIRC，您应该能够使用 SQL 语法从工作表中选择特定范围。正确的语法应该是：
"SELECT * FROM [Sheet$]"
If you want a particular range then you'd try:
如果你想要一个特定的范围，那么你可以尝试：
"SELECT * FROM [Sheet$A1:C20]"
There's a Microsoft article on this at: http://support.microsoft.com/kb/257819if you're looking for more information on using ADO with Excel. The Scripting Guys also wrote a decent article that helped me understand it a few years back: http://technet.microsoft.com/en-us/library/ee692882.aspx.
如果您正在寻找有关在 Excel 中使用 ADO 的更多信息，请访问以下 Microsoft 文章：http: //support.microsoft.com/kb/257819。几年前，脚本专家还写了一篇不错的文章，帮助我理解了它：http: //technet.microsoft.com/en-us/library/ee692882.aspx。
Hopefully that helps you solve your problem. However, I will leave you with one word of warning. I remember running into some really weird issues when querying an already open Excel spreadsheet when I was first using this code. If I was querying an Excel spreadsheet that was already open then I would run into an issue where memory would be leaked and Excel would eventually run out of memory to use. The only way to solve the problem was to close Excel entirely. It also didn't matter whether the ADO references were properly closed/cleared or not.
希望这可以帮助您解决问题。但是，我要给你留下一句警告。我记得当我第一次使用这段代码时，在查询一个已经打开的 Excel 电子表格时遇到了一些非常奇怪的问题。如果我正在查询一个已经打开的 Excel 电子表格，那么我会遇到内存泄漏的问题，并且 Excel 最终会耗尽内存以供使用。解决问题的唯一方法是完全关闭 Excel。ADO 引用是否正确关闭/清除也无关紧要。
In fact, I just Googled it to double check and here's an article on the bug: http://support.microsoft.com/default.aspx?scid=kb;en-us;319998&Product=xlw.
事实上，我只是在谷歌上仔细检查了一下，这里有一篇关于这个错误的文章：http: //support.microsoft.com/default.aspx? scid=kb;en-us;319998&Product=xlw 。
回答by SouthL
It sounds similar to a problem I once had when upgrading from office 2003 to 2007.
这听起来类似于我从 Office 2003 升级到 2007 时曾经遇到的问题。
My solution was changing the provider in the connection string. Since you're using a modern office version I think it should be:
我的解决方案是更改连接字符串中的提供程序。由于您使用的是现代办公版本，我认为它应该是：
Provider=Microsoft.Ace.OLEDB.12.0;
You might want to look into that Extended property as well. I know that Excel 8.0 is a Excel '97 file. And Excel 2010 is 14.0
您可能还想查看该 Extended 属性。我知道 Excel 8.0 是 Excel '97 文件。而 Excel 2010 是 14.0
