Using INSERT INTO to write data into access databa

Question

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

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.

Thanks.

Answer 1

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"

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]"

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.

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.

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.

Answer 2

It sounds similar to a problem I once had when upgrading from office 2003 to 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