Importing from Excel using INSERT INTO


John F

These questions pertain to the code below. Thanks in advance.


1) Does not work with Sheet1! But works without putting Sheet1 in clause.
With actual spreadsheets I use, there are several sheets, so need to specify
which sheet

2) Requires first row in range to have field names corresponding to fields
in Access table. Can I eliminate that requirement and have columns read into
the fields listed after the INSERT INTO?

3) Instead of having path and file name either hardcoded, or included via a
string parameter entered by user, can I call a function to browse and return
path and filename in a string?

4) Get error when rsTemp.Close not commented indicating already closed.
Confused as I did not close rsTemp.

5) Surprised that it will work with adOpenForwardOnly, adLockReadOnly in
the rsTemp.Open statement. Normally I use adOpenDynamic, adLockOptimistic
for recordsets that I write to.

Private Sub cmdImport_Click()

Dim strSQL As String

Dim rsTemp As New ADODB.Recordset

strSQL = "INSERT INTO tblTableInput (txtField1, txtField2,
lngzField3, lngzField4) " _
& "SELECT * FROM Sheet1!C3:Sheet1!F6 IN " &
"""c:\test\testinput.xls"" " & _
"""Excel 5.0;"""

rsTemp.Open strSQL, CurrentProject.Connection, adOpenForwardOnly,

'Set rsTemp = Nothing

End Sub




An action query (Append, Delete, Make Table etc) cannot be a recordset. That
is why you get the rstTemp.close error. The recordset is never created. Are
you linked to the worksheet? I am not sure why you want to use a query to
import records rather than use Transferspreadsheet. However if this is the
route you want to go then check out to get the
connection string for an Excel workbook/sheet. You will want to create and

Dim myCon as new adodb.connection

then execute your sql statement

myCon.execute strSql

Try this website for question 3



John F


Thanks for your response. The INSERT INTO does work and creates a
recordset. I have used it in other situations, and it also works importing
from Excel but I had some specific issues that prompted my questions. That
said, I will do further research to see if that is not a good practice for
other reasons.

I did not use transferspreadsheet because I felt the use of INSERT INTO
..... SELECT ...WHERE ... gave me some flexibility I would not get with
transferspreadsheet. But it does cause problems with Excel columns that are
text but also have numeric data, so I may need to reconsider

Regarding my original questions: 1) found using named range in Excel would
import from the desired worksheet , and 3) found a class providing needed
functionality to browse for files.

Thanks again,


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question