modification db via excel

Sub Modif_projet_admin()

Dim conn As ADODB.Connection
Dim Rst As ADODB.Recordset
Dim new_value As String, ref As String, table As String, refe As String
Dim a As Byte

Application.ScreenUpdating = False
table = "Projet_admin"
Sheets("Calcul").Activate
Set conn = New ADODB.Connection

With conn
.Provider = "Microsoft.ACE.OLEDB.12.0"
.Open "c:\\DBmodule.accdb"
End With
Set Rst = New ADODB.Recordset
Rst.Open table, conn, adOpenKeyset, adLockOptimistic

ref = Sheets("Calcul").Range("A76").Value
For a = 2 To 45
With Rst
.MoveFirst
.Find ("reference=" & Chr(34) & (ref) & Chr(34))
If Rst.EOF Or Rst.BOF Then
Rst.MoveLast
End If
new_value = Sheets("Calcul").Cells(77, a).Value
refe = Sheets("Calcul").Cells(76, a).Value
.Fields(refe) = new_value
.Update
End With
Next a
Rst.Close
conn.Close
Application.ScreenUpdating = True
End Sub

Cliquez pour agrandir...