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
| 'Macro RecordAcliveRow dans ACCESS
Dim Plage As Range
Dim Array1 As Variant
Dim x As Variant
Dim Db1 As Database
Dim Rs1 As Recordset
Dim Sql As String
Dim Exist As String
' permet la validation en cas de cellule normale ou cellule contenant une liste de validation
If ActiveCell.Row <> Target.Row Then
Set Plage = ActiveSheet.ListObjects(1).ListRows(ActiveCell.Row - 2).Range
Else
Set Plage = ActiveSheet.ListObjects(1).ListRows(ActiveCell.Row - 1).Range
End If
Array1 = Plage.Value
'Ouverture de la base de donnée BaseAccess.accdb
Set Db1 = DBEngine.OpenDatabase("\\PC-ACCUEIL-DROI\Users\Public\BaseAccess.accdb")
For x = 1 To UBound(Array1, 1)
'Requête sur la table Salarie
Sql = "SELECT * FROM Salarie where ID=" & Array1(x, 1) & ";"
Set Rs1 = Db1.OpenRecordset(Sql)
Exist = Rs1.RecordCount
If Exist = 0 Then
Set Rs1 = Db1.OpenRecordset("Salarie", dbOpenDynaset)
With Rs1
.AddNew
.Fields("ID") = Array1(x, 1)
.Fields("Nom") = Array1(x, 2)
.Fields("Prenom") = Array1(x, 3)
.Update
End With
Else
With Rs1
.Edit
.Fields("Nom") = Array1(x, 2)
.Fields("Prenom") = Array1(x, 3)
.Update
End With
End If
Next
Db1.Close
enderror:
Call Refresh 'appelle macro qui synchronise excel avec la BDD Access
End Sub |
Partager