Excel Range into Existing Access Table

Question

I have a VB6 application in which I need to insert row/columns of data from an excel file into an access database.

My connection string for the access database:

db.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\db.mdb" & ";Persist Security Info=False;Jet OLEDB:Database Password=*****"

I am trying to insert data using this SQL string:

Dim sql As String
sql = "INSERT INTO tbl_trade_tcp ("
sql = sql & "[field_A], "
sql = sql & "[field_B], "
sql = sql & "[field_C]"
sql = sql & ") "
sql = sql & "SELECT * FROM [Excel " & xlApp.Application.Version & ";HDR=NO;IMEX=1;Database=" & wb.FullName & "].[" & ws.Name & "$AH3:AJ36,B3:AG36]"

db.Execute sql

The problem now is keep getting this error message:

Cannot find installable ISAM

For reference purposes, there are the info that I used to create the sql string:

Fastest way to copy an Excel range to Access?

Using Excel VBA to Export data to Ms.Access Table

Answer 1

After looking into various posts, I have found out the following:

1. Instead of using the current machines' Excel Version, one needs to default to Excel 8.0

   SELECT * FROM [Excel " & xlApp.Application.Version & ";HDR=NO;IMEX=1;Database=" & wb.FullName & "].[" & ws.Name & "$AH3:AJ36,B3:AG36]"

   should be

   SELECT * FROM [Excel 8.0;HDR=NO;IMEX=1;Database=" & wb.FullName & "].[" & ws.Name & "$AH3:AJ36,B3:AG36]"

   Reference: here

2. Be sure that columns names matches from the excel file. If there are no column names in the excel file, then you need to declare them one by one.

   Reference: here