VBA - SQL Import From Excel Sheet To Access DB - EXECUTION ERROR 3343

Question

I would like to build a SQL request in order to store all my sheet content into an access DB in order to do this I built the following request :

 sSQL = "INSERT INTO Archive_FP21 (Date_Histo,Caisse,Libelle,Reference_Contrat,Date_de_Nego,Date_Valeur,Echeance_Finale,Libelle_Index,Taux_Actuel,Capital_Origine,Capital_Restant_Du,Marge,Taux_du_cap,Taux_du_Floor,Derniere_Echance_INT,Derniere_Echeance_AMO,Interet,Prochaine_Echeance) " & _
    "SELECT * FROM [Feuil1$A:R] IN """ & WbPath & """"

But I am facing the following issue :

3343 unrecognized database format

[I feel like my issue is in FROM [Feuil1$A:R] IN """ & WbPath & """"]

Below is the my full code Sub :

Sub archiver()
    
    Dim ws As DAO.Workspace
    Dim db As DAO.Database
    Dim sDb As String
    Dim sSQL As String
    Dim qdf As QueryDef
    Dim WbPath As String

    
WbPath = "C:\******\Extraction FP21 Mise en Forme Auto\16102020 - Copie.xlsx"

sDb = "C:\******\BaseFp21.accdb"
Set ws = DBEngine.Workspaces(0)
Set db = ws.OpenDatabase(sDb)

sSQL = "INSERT INTO Archive_FP21 (Date_Histo,Caisse,Libelle,Reference_Contrat,Date_de_Nego,Date_Valeur,Echeance_Finale,Libelle_Index,Taux_Actuel,Capital_Origine,Capital_Restant_Du,Marge,Taux_du_cap,Taux_du_Floor,Derniere_Echance_INT,Derniere_Echeance_AMO,Interet,Prochaine_Echeance) " & _
"SELECT * FROM [Feuil1$A:R] IN """ & WbPath & """"


db.Execute sSQL

End Sub

Note The goal of this SQL request is to Add all data from the sheet 'Feui1.Range(A:R)` into my Access Table. I can't do it row By Row since I have 37K line to fill in Access.

What Am I missing ? How would you do in order to fill 37K row from excel inside Access DB with VBA ?

Where are you placing and running your VBA code? In the Excel file to be harvested or in the Access that will be the archive, or in a another file? — Pay it forward, Oct 16 '20 at 13:38
@Tech-On-the_Beach In an other file than the excel file which will be archived. And the access DB should store content of the file which need to be archived. But my VBA module is in an other workbook than the one storing data. Hope I am Clear Enough — TourEiffel, Oct 16 '20 at 13:42
@Tech-On-the_Beach I added a screenshot of the issue, hope this help ... I am stuck — TourEiffel, Oct 16 '20 at 13:51

score 2 · Accepted Answer · answered Oct 16 '20 at 14:14

To query from an Excel workbook inline with an Access connection does not use the IN operator but bracketed identifier with set parameters such as headers and workbook type. As used, IN would work if you were querying an external Access database but being an Excel workbook, the database format was not recognized.

sSQL = "INSERT INTO Archive_FP21 (Date_Histo, Caisse, Libelle, Reference_Contrat," _
        & "                       Date_de_Nego, Date_Valeur, Echeance_Finale, " _
        & "                       Libelle_Index, Taux_Actuel, Capital_Origine, " _
        & "                       Capital_Restant_Du, Marge, Taux_du_cap, Taux_du_Floor, " _ 
        & "                       Derniere_Echance_INT, Derniere_Echeance_AMO, Interet, " _
        & "                       Prochaine_Echeance) " _
        & " SELECT * FROM [Excel 12.0 Xml;HDR=Yes;Database=" & WbPath & "].[Feuil1$A:R]"

db.Execute sSQL

Also, be sure to avoid SELECT * FROM and explicitly select named columns especially in insert-select append queries for column-to-column mapping. SELECT Col1, Col2, Col3, ... FROM is more readable and maintainable in case Excel columns order should adjust or some columns are no longer present.

Thank you for your answer, should the column name of the excel file match with the DB field ? If no how would you do ? — TourEiffel, Oct 16 '20 at 14:20
No, generally in insert-select SQL queries names of inserted table do not have to match to names of select source but corresponding columns should align by data type. — Parfait, Oct 16 '20 at 14:22

VBA - SQL Import From Excel Sheet To Access DB - EXECUTION ERROR 3343

1 Answers1