Using INSERT INTO to write data into access database

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.

I know that this Cn.Execute "INSERT INTO Project_Names IN 'D:\Tool_Database\Tool_Database.mdb' SELECT * FROM Worksheets("NewProj").Range("A2").Value" is where is breaks... How can I select a specific cell or row of cells? — thebiglebowski11
– thebiglebowski11, Commented Feb 1, 2012 at 21:55
When I run that code, I get runtime error 424 Object Required On this line: strSQL = "INSERT INTO Project_Names (Proj_Num) " & "VALUES (" & NewProj.[A1] & ")" — thebiglebowski11
– thebiglebowski11, Commented Feb 1, 2012 at 22:16
Try ?worksheets("newproj").[a1] I do not think you can refer to a worksheet by name as you have done. If that works, you can use that in oace of the existing reference, without the question mark, of course. BTW in Stackoverflow, if you prefix a person's name with @, they will get a message, otherwise they may miss it, unless they are the author of the post. — Fionnuala
– Fionnuala, Commented Feb 2, 2012 at 18:21

Kittoes0124 · Accepted Answer · 2012-02-06 22:19:00Z

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.

SouthL · Accepted Answer · 2012-02-02 07:13:34Z

0

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

answered Feb 2, 2012 at 7:13

SouthL

1752 silver badges12 bronze badges

Collectives™ on Stack Overflow

Using INSERT INTO to write data into access database

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related