Search code examples
sqlexcelvbams-accessms-access-2016

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

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 enter image description here

[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 ?

Solution

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