使用INSERT INTO将数据写入Access数据库 [英] Using INSERT INTO to write data into access database

问题描述

我正在尝试使用INSERT INTO命令将数据从Excel 2010文件写入Access 2007文件中.数据包含在Tool_Selector.xlsm excel文件的"NewProj"工作表中，需要将其写入"Tool_Database.mdb"文件，但不幸的是，我收到了几个不同的错误.这是我目前遇到的问题

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

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

我从此线程获得了代码:将数据从Excel插入到使用VBA访问

I got the code from this thread: Insert Data from Excel into Access using VBA

我只需要转移单个单元格的值.

I just need to transfer the value of individual cells.

谢谢.

推荐答案

问题是您正在SQL语句中使用VBA代码.以下行将编译，但执行失败:

The problem is that you're using VBA code IN the SQL statement. The following line will compile but fail upon execution:

Cn.Execute "INSERT INTO Project_Names IN 'D:\Tool_Database\Tool_Database.mdb' SELECT * FROM Worksheets("NewProj").Range("A2").Value"

它失败，因为当您使用Worksheets()，.Range和.Value的VBA函数时，您仍然在SELECT语句中. IIRC，您应该能够使用SQL语法从工作表中选择特定范围.正确的语法应该是:

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:

"SELECT * FROM [Sheet$]"

如果您想要一个特定范围，则可以尝试:

If you want a particular range then you'd try:

"SELECT * FROM [Sheet$A1:C20]"

对此有一篇Microsoft文章: http://support.microsoft.com/kb/257819 如果您正在寻找有关将ADO与Excel结合使用的更多信息.脚本专家还写了一篇不错的文章，几年前帮助我理解了它: http ://technet.microsoft.com/zh-CN/library/ee692882.aspx .

There's a Microsoft article on this at: http://support.microsoft.com/kb/257819 if 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电子表格时遇到了一些非常奇怪的问题.如果我要查询已经打开的Excel电子表格，那么我会遇到一个问题，即内存泄漏，而Excel最终将耗尽内存以供使用.解决此问题的唯一方法是完全关闭Excel. ADO参考是否已正确关闭/清除也没关系.

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.

实际上，我只是对其进行了Google搜索以进行仔细检查，这是有关该错误的文章:

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.

这篇关于使用INSERT INTO将数据写入Access数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！