Renvoie de donnée à une base MySQL
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.
Code:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37
| 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 |