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
| Sub remplir_table_ecritures_analytiques()
Dim db As Database
Dim WrkSpc As Workspace
Dim recs As Recordset
Dim vUO, vMet, vRub, vProj, vRes, vSegop, vLb, vRve As String
Dim vCpt, cSte As Integer
Dim id, vID As Variant
Dim where, SQL As String
Dim tble As String
Dim i As Integer
i = 0
tble = "Donnees"
Set db = CurrentDb()
Set WrkSpc = DBEngine.Workspaces(0)
WrkSpc.BeginTrans
Set recs = db.OpenRecordset(tble, dbOpenSnapshot, dbForwardOnly)
Do While Not recs.EOF
i = 0
Do While Not recs.EOF And (i < 10000)
i = i + 1
vID = recs.Fields("ID_ecriture")
If IsNull(vID) Then
vUO = recs.Fields("UO")
vMet = recs.Fields("Met")
vRub = recs.Fields("Rub")
vCpt = recs.Fields("Cpt")
vProj = recs.Fields("Proj")
vRes = recs.Fields("Res")
vSegop = recs.Fields("Segop")
vLb = recs.Fields("Lb")
vRve = recs.Fields("Rve")
vSte = recs.Fields("Ste")
If Not IsNull(vCpt) Then
where = "UO = '" & vUO & "' AND Met = '" & vMet & "' AND Rub = '" & vRub & "' AND Cpt = " & vCpt & " AND Proj = '" & vProj & "' AND Res = '" & vRes & "' AND Segop = '" & vSegop & "' AND Lb = '" & vLb & "' AND Rve = '" & vRve & "' AND Ste = " & vSte
id = DLookup("[ID_ecriture]", "[table_ecritures_analytiques]", where)
If IsNull(id) Then
SQL = "INSERT INTO table_ecritures_analytiques (UO, Met, Rub, Cpt, Proj, Res, Segop, Lb, Rve, Ste) VALUES ('" & vUO & "', '" & vMet & "', '" & vRub & "', '" & vCpt & "', '" & vProj & "', '" & vRes & "', '" & vSegop & "', '" & vLb & "', '" & vRve & "', '" & vSte & "')"
DoCmd.SetWarnings False
DoCmd.RunSQL SQL
DoCmd.SetWarnings True
id = DLookup("[ID_ecriture]", "[table_ecritures_analytiques]", where)
End If
SQL = "UPDATE " & tble & " SET ID_ecriture = '" & id & "' WHERE " & where
DoCmd.SetWarnings False
db.Execute SQL
DoCmd.SetWarnings True
End If
End If
recs.MoveNext
Loop
WrkSpc.CommitTrans
If i = 10000 Then
WrkSpc.BeginTrans
End If
Loop
End Sub |
Partager