Importing from Excel using INSERT INTO

[John F]

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

John

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,
adLockReadOnly

'rsTemp.Close
'Set rsTemp = Nothing

End Sub

 

[Ralph]

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 connectionstrings.com to get the
connection string for an Excel workbook/sheet. You will want to create and
adodb.Connection

Dim myCon as new adodb.connection

then execute your sql statement

myCon.execute strSql

Try this website for question 3
http://www.mvps.org/access/api/api0001.htm

 

[John F]

Ralph,

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
transferspreadsheet.

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,

John