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 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146
|
Public Function maj_table_faire_savoir_2ch(champ, table, table_origine, table_faire_savoir, champAccess1, champAccess2)
Dim db As Database
Dim rdcChamp As Recordset
Dim Flchamp, FlId As Field
Dim nb, i, PosDollar, DebDollar, PosZQ1 As Integer
Dim Valchamp, ValId, Newchamp, Newch1, Newch2, Sql As String
On Error GoTo Err_Conquenation
DoCmd.SetWarnings False
Set db = CurrentDb
If table Like "loi" & "*" Then
table = remplace(table, "loi", "asc")
Else
Sql = "DELETE DISTINCTROW " & table & ".* FROM [" & table & "];"
DoCmd.RunSQL Sql, -1
End If
Sql = "SELECT [" & table_faire_savoir & "].* FROM [" & table_faire_savoir & "] WHERE ((rien([" & table_faire_savoir & "]." & champ & ") =false) and (([" & table_faire_savoir & "]." & champ & ") <>'ZZ')) order by ID;"
Set rdcChamp = db.OpenRecordset(Sql)
nb = rdcChamp.RecordCount
Select Case nb
Case Is = 0
Case Else
rdcChamp.MoveLast
nb = rdcChamp.RecordCount
rdcChamp.MoveFirst
i = 0
Set Flchamp = rdcChamp(champ)
If Valchamp Like "LOIZQ" & "*" Then
Valchamp = Replace(Replace([Flchamp], " $ ", "$LOIZQ"), "'", "''")
Else
Valchamp = Replace(Replace([Flchamp], " $ ", "$"), "'", "''")
End If
Set FlId = rdcChamp("id")
ValId = [FlId]
DebDollar = 0
Do While InStr(DebDollar + 1, Valchamp, "$") > 0
PosDollar = InStr(DebDollar + 1, Valchamp, "$")
Newchamp = Right(Left(Valchamp, PosDollar - 1), PosDollar - (DebDollar + 1))
PosZQ1 = InStr(1, Newchamp, "ZQ")
If PosZQ1 > 0 Then
Newch1 = Left(Newchamp, PosZQ1 - 1)
Newch2 = Right(Newchamp, Len(Newchamp) - (PosZQ1 + 1))
Sql = "INSERT INTO [" & table & "] ( id, [" & champAccess1 & "],[" & champAccess2 & "] ) VALUES ('" & ValId & "', '" & Newch1 & "', '" & Newch2 & "');"
DoCmd.RunSQL Sql, -1
End If
DebDollar = PosDollar
Loop
Newchamp = Right(Left(Valchamp, Len(Valchamp)), Len(Valchamp) - DebDollar)
PosZQ1 = InStr(1, Newchamp, "ZQ")
If PosZQ1 > 0 Then
Newch1 = Left(Newchamp, PosZQ1 - 1)
Newch2 = Right(Newchamp, Len(Newchamp) - (PosZQ1 + 1))
Sql = "INSERT INTO [" & table & "] ( id, [" & champAccess1 & "],[" & champAccess2 & "] ) VALUES ('" & ValId & "', '" & Newch1 & "', '" & Newch2 & "');"
DoCmd.RunSQL Sql, -1
End If
For i = 1 To nb - 1
If Int(i / 100) = i Then
Sleep (300)
End If
rdcChamp.MoveNext
Set Flchamp = rdcChamp(champ)
If Valchamp Like "LOIZQ" & "*" Then
Valchamp = Replace(Replace([Flchamp], " $ ", "$LOIZQ"), "'", "''")
Else
Valchamp = Replace(Replace([Flchamp], " $ ", "$"), "'", "''")
End If
Set FlId = rdcChamp("id")
ValId = [FlId]
DebDollar = 0
Do While InStr(DebDollar + 1, Valchamp, "$") > 0
PosDollar = InStr(DebDollar + 1, Valchamp, "$")
Newchamp = Right(Left(Valchamp, PosDollar - 1), PosDollar - (DebDollar + 1))
PosZQ1 = InStr(1, Newchamp, "ZQ")
If PosZQ1 > 0 Then
Newch1 = Left(Newchamp, PosZQ1 - 1)
Newch2 = Right(Newchamp, Len(Newchamp) - (PosZQ1 + 1))
Sql = "INSERT INTO [" & table & "] ( id, [" & champAccess1 & "],[" & champAccess2 & "] ) VALUES ('" & ValId & "', '" & Newch1 & "', '" & Newch2 & "');"
DoCmd.RunSQL Sql, -1
End If
DebDollar = PosDollar
Loop
Newchamp = Right(Left(Valchamp, Len(Valchamp)), Len(Valchamp) - (DebDollar))
PosZQ1 = InStr(1, Newchamp, "ZQ")
If PosZQ1 > 0 Then
Newch1 = Left(Newchamp, PosZQ1 - 1)
Newch2 = Right(Newchamp, Len(Newchamp) - (PosZQ1 + 1))
Sql = "INSERT INTO [" & table & "] ( id, [" & champAccess1 & "],[" & champAccess2 & "] ) VALUES ('" & ValId & "', '" & Newch1 & "', '" & Newch2 & "');"
DoCmd.RunSQL Sql, -1
End If
Next i
End Select
rdcChamp.Close
Set rdcChamp = Nothing
Set Flchamp = Nothing
Set FlId = Nothing
Sql = "UPDATE FICHIER_TS SET FICHIER_TS.fait = No WHERE (((FICHIER_TS.Fichier)='" & table_origine & "'));"
DoCmd.RunSQL Sql, -1
db.Close
Set db = Nothing
Exit Function
Err_Conquenation:
Debug.Print "Table=" & table_origine & " / ID=" & ValId & " / SQL=" & Sql & " / ERREUR=" & Err.Description
Sql_bug = "INSERT INTO [Bug_MAJ_TS] ( ID,ligne,table_bug,sql,erreur,date_bug )VALUES ('" & ValId & "'," & i & ",'" & table_origine & "','" & remplace_m(Sql, "'", "") & "','" & remplace_m(Err.Description, "'", "") & "',#" & Now & "#);"
DoCmd.RunSQL Sql_bug, -1
If Err.Description <> "L'opération doit utiliser une requête qui peut être mise à jour." Then
MsgBox Err.Description
End If
rdcChamp.Close
Set rdcChamp = Nothing
Set Flchamp = Nothing
Set FlId = Nothing
db.Close
Set db = Nothing
Sleep (3000)
End Function |
Partager