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 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65
| Sub Test_ADO()
Dim tabIndispos As Variant
Dim i As Integer, j As Integer, k As Integer
Dim dateDeb As Date, dateFin As Date
Dim motif As String, codeIntervenant As String
Dim cnx As ADODB.Connection
Dim Cmd As ADODB.Command
Dim NomUtilisateur As String, MotDePasse As String, NomServeur As String, BaseDeDonnees As String
NomUtilisateur = "root"
MotDePasse = ""
NomServeur = "localhost"
BaseDeDonnees = "bd_gestplanning"
'La connexion
Set cnx = New ADODB.Connection
'Définition de la chaîne de connexion
'- OLEDB
'cnx.ConnectionString = "Provider=MySQLProv;Server=localhost;Data Source=" & BaseDeDonnees & ";User Id=" & NomUtilisateur & ";Password=" & MotDePasse & ";"
'- ODBC
cnx.ConnectionString = " DRIVER={MySQL ODBC 5.1 Driver};SERVER=" & NomServeur & ";UID=" & NomUtilisateur & ";DATABASE=" & BaseDeDonnees & ";Password=" & MotDePasse
'Ouverture de la base de données
Debug.Print cnx.ConnectionString
cnx.Open
'Paramétrage Command
Set Cmd = New ADODB.Command
With Cmd
.ActiveConnection = cnx
.CommandText = "P_Add_Indispos"
.CommandType = adCmdStoredProc
End With
tabIndispos = ThisWorkbook.Names("Indispos").RefersToRange
k = 1
For i = LBound(tabIndispos, 1) + 3 To UBound(tabIndispos, 1)
For j = LBound(tabIndispos, 2) To UBound(tabIndispos, 2) Step 3
dateDeb = tabIndispos(i, j)
If Len(CStr(dateDeb)) <> 0 Then
dateFin = tabIndispos(i, j + 1)
motif = CStr(tabIndispos(i, j + 2))
codeIntervenant = Format(k, "0000")
'Lancement de la procédure stockée
With Cmd
.Parameters.Refresh 'plante avec OLEDB
.Parameters(0).Value = codeIntervenant
.Parameters(1).Value = dateDeb
.Parameters(2).Value = dateFin
.Parameters(3).Value = motif
.Execute
End With
End If
Next j
k = k + 1
Next i
cnx.Close
Set cnx = Nothing
Set Cmd = Nothing
End Sub |
Partager