Option Explicit
Public oConnect As ADODB.Connection
Private Sub ConnectionDB()
Dim S As String
Set oConnect = New ADODB.Connection
S = "DRIVER={MySQL ODBC 5.1 Driver};" & _
"SERVER=" & Sheets("config").Range("B1").Text & ";" & _
"DATABASE=" & Sheets("config").Range("B2").Text & ";" & _
"USER=" & Sheets("config").Range("B3").Text & ";" & _
"PASSWORD=" & Sheets("config").Range("B4").Text & ";" & _
"Option=3"
oConnect.Open S
End Sub
Sub InsertData()
Dim Rs As ADODB.Recordset
Dim Derligne As Integer, i As Integer
Dim Requete As String
Set Rs = New ADODB.Recordset
Call ConnectionDB
On Error GoTo errhdlr
With Sheets(1)
Derligne = .Range("A65000").End(xlUp).Row
For i = 2 To Derligne
Requete = "SELECT * FROM voitures WHERE id=" & .Cells(i, 1)
Rs.Open Requete, oConnect
If Rs.EOF And Rs.BOF Then
Rs.Close
Requete = "INSERT INTO voitures(id, marque, modele, cv) VALUES(" & _
.Cells(i, 1) & ", '" & _
.Cells(i, 2) & "', '" & _
.Cells(i, 3) & "', " & _
.Cells(i, 4) & ")"
Else
Requete = "UPDATE voitures SET " & _
" marque='" & .Cells(i, 2) & "', " & _
" modele='" & .Cells(i, 3) & "', " & _
" cv=" & .Cells(i, 4) & _
" WHERE id=" & .Cells(i, 1)
End If
Rs.Open Requete, oConnect, adOpenDynamic, adLockOptimistic
Rs.Close
Next i
End With
oConnect.Close
Set Rs = Nothing
Exit Sub
errhdlr:
oConnect.Close
MsgBox "Erreur " & Err.Number & vbCrLf & Err.Description
End Sub