Je souhaite renvoyé des données issue d'un tableau Excel vers une base SQL, mais le code SQL que j'utilise ne fonctionne pas et affiche un message d'erreur sur l'incompatibilité de type pour ma dernier requête.

Sub test()

Dim I As Integer

    Const Server = "LocalHost", Port = "3306", User = "root", Password = "": Dim DataBase
With CreateObject("ADODB.Connection")
        .Open "Driver={MySQL ODBC 8.0 Unicode Driver};Server=" & Server & ";Port=" & Port & ";Database=" & DataBase & ";User=" & User & ";Password=" & Password & ";"
            requete = "CREATE DATABASE IF NOT EXISTS `vbamysql` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;"
            .Execute requete
        .Close
        DataBase = "vbamysql"
        .Open "Driver={MySQL ODBC 8.0 Unicode Driver};Server=" & Server & ";Port=" & Port & ";Database=" & DataBase & ";User=" & User & ";Password=" & Password & ";"
             requete = "CREATE TABLE IF NOT EXISTS  `voitures`" & vbCrLf & _
                       "(`id` INTEGER NOT NULL auto_increment,`marque` VARCHAR(25) NOT NULL,`modele` VARCHAR(25) NOT NULL ,`cv` INTEGER," & vbCrLf & _
                       "PRIMARY KEY (`id`),UNIQUE (`modele`)) ENGINE = InnoDB ;"
        .Execute requete
        .Close

         For I = 1 To 6
            a0 = ThisWorkbook.Sheets("traitement").Cells(I, 1).Value
            a1 = ThisWorkbook.Sheets("traitement").Cells(I, 2).Value
            a2 = ThisWorkbook.Sheets("traitement").Cells(I, 3).Value
            a3 = ThisWorkbook.Sheets("traitement").Cells(I, 4).Value

        DataBase = "vbamysql1"
        .Open "Driver={MySQL ODBC 8.0 Unicode Driver};Server=" & Server & ";Port=" & Port & ";Database=" & DataBase & ";User=" & User & ";Password=" & Password & ";"
        requete = "INSERT INTO voitures(id, marque, modele, cv) VALUES(" & _
                    a0 & ",'" & _
                    a1 & "', '" & _
                    a2 & "', " & _
                    a3 & ")"
        .Execute requete
        Next

        .Close
End With
End Sub

Je dirais qu'il prend les valeurs comme des chaines et non pas des entiers pour id et cv et qu'il faut mettre explicitement un guillemet au début et à la fin d'une chaine SQL; 

         For I = 1 To 6
            a0 = CInt(ThisWorkbook.Sheets("traitement").Cells(I, 1).Value)
            a1 = ThisWorkbook.Sheets("traitement").Cells(I, 2).Value
            a2 = ThisWorkbook.Sheets("traitement").Cells(I, 3).Value
            a3 = Cint(ThisWorkbook.Sheets("traitement").Cells(I, 4).Value)
 
        DataBase = "vbamysql1"
        .Open "Driver={MySQL ODBC 8.0 Unicode Driver};Server=" & Server & ";Port=" & Port & ";Database=" & DataBase & ";User=" & User & ";Password=" & Password & ";"
        requete = "INSERT INTO voitures(id, marque, modele, cv) VALUES(" & _
                    a0 & ",'" & _
                    a1 & "', '" & _
                    a2 & "', " & _
                    a3 & ")"
        .Execute requete
        Next

ou alors utiliser une requête préparée

         For I = 1 To 6
            a0 = CInt(ThisWorkbook.Sheets("traitement").Cells(I, 1).Value)
            a1 = ThisWorkbook.Sheets("traitement").Cells(I, 2).Value
            a2 = ThisWorkbook.Sheets("traitement").Cells(I, 3).Value
            a3 = Cint(ThisWorkbook.Sheets("traitement").Cells(I, 4).Value)
 
        DataBase = "vbamysql1"
        .Open "Driver={MySQL ODBC 8.0 Unicode Driver};Server=" & Server & ";Port=" & Port & ";Database=" & DataBase & ";User=" & User & ";Password=" & Password & ";"
        requete = "INSERT INTO voitures(id, marque, modele, cv) VALUES( ? , ? , ?, ?)
        .Execute requete, Array(a0,a1,a2,a3)
        Next