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 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93
| Option Compare Database
Public Sub ClientDeces()
Dim oRst As DAO.Recordset
Dim oDB As DAO.Database
Dim sql As Variant
sql = "SELECT Table1.client, Table1.critere "
sql = sql & "FROM Table1 "
sql = sql & "WHERE (((Table1.critere)='Déces'))"
'Exécution de la requete
Set oDB = CurrentDb
Set oRst = oDB.OpenRecordset(sql, dbOpenSnapshot)
oRst.MoveFirst
While Not oRst.EOF
Call BoucleTable1(oRst.Fields("client").Value)
Call SuppressionTable1(oRst.Fields("client").Value)
oRst.MoveNext
Wend
'On Libération des objets
'Libération des objets
oRst.Close
oDB.Close
Set oRst = Nothing
Set oDB = Nothing
End Sub
Public Sub SuppressionTable1(client As String)
Dim oRst As DAO.Recordset
Dim oDB As DAO.Database
Dim sql As Variant
sql = "SELECT Table1.client "
sql = sql & "FROM Table1 "
sql = sql & "WHERE (((Table1.client)='" & client & "'))"
'Exécution de la requete
Set oDB = CurrentDb
Set oRst = oDB.OpenRecordset(sql, dbOpenDynaset)
oRst.MoveFirst
While Not oRst.EOF
'suppresion de l'enregistrement
oRst.Delete
oRst.MoveNext
Wend
'Libération des objets
oRst.Close
oDB.Close
Set oRst = Nothing
Set oDB = Nothing
End Sub
Public Sub BoucleTable1(client As String)
Dim oRst As DAO.Recordset
Dim oDB As DAO.Database
Dim sql As Variant
sql = "SELECT Table1.client, Table1.critere, Table1.dateNaissance, Table1.dateVisite "
sql = sql & "FROM Table1 "
sql = sql & "WHERE (((Table1.client)='" & client & "'))"
'Exécution de la requete
Set oDB = CurrentDb
Set oRst = oDB.OpenRecordset(sql, dbOpenSnapshot)
oRst.MoveFirst
While Not oRst.EOF
Call ecritureTable2(oRst.Fields("client").Value, oRst.Fields("critere").Value, oRst.Fields("dateNaissance").Value, oRst.Fields("dateVisite").Value)
oRst.MoveNext
Wend
'On Libération des objets
'Libération des objets
oRst.Close
oDB.Close
Set oRst = Nothing
Set oDB = Nothing
End Sub
Public Sub ecritureTable2(client As String, critere As String, dateNaissance As Date, dateVisite As Date)
Dim oRst As DAO.Recordset
Dim oDB As DAO.Database
Dim sql As Variant
Set oDB = CurrentDb
Set oRst = oDB.OpenRecordset("Table2", dbOpenDynaset)
'Passage en mode ajout
oRst.AddNew
'Ajout des données
oRst.Fields("client").Value = client
oRst.Fields("critere").Value = critere
oRst.Fields("dateNaissance").Value = dateNaissance
oRst.Fields("dateVisite").Value = dateVisite
'Mise à jour
oRst.Update
'Libération des objets
oRst.Close
oDB.Close
Set oRst = Nothing
Set oDB = Nothing
End Sub |
Partager