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
| Function factorisation(tableSource As String, tableDestination As String, champFacteur As String, champConcatenation As String, separateur As String)
typechampfacteur = CurrentDb.TableDefs(tableSource).Fields(champFacteur).Type
If Not ((typechampfacteur = 4) Or (typechampfacteur = 10)) Then
MsgBox "type du champ facteur inconnu"
Exit Function
End If
instruction = Array( _
"drop table [" & tableDestination & "]", _
"create table [" & tableDestination & "]", _
"alter table [" & tableDestination & "] add column [" & champFacteur & "] " & Switch(typechampfacteur = 10, "text", typechampfacteur = 4, "numeric"), _
"alter table [" & tableDestination & "] add column [" & champConcatenation & "] text" _
)
On Error Resume Next
For i = LBound(instruction) To UBound(instruction)
DoCmd.RunSQL instruction(i)
Next i
On Error GoTo 0
Dim rs2 As Recordset
Set rs1 = CurrentDb.OpenRecordset(tableSource, dbOpenForwardOnly)
Set rs2 = CurrentDb.OpenRecordset(tableDestination, dbOpenDynaset)
While Not rs1.EOF
critere = "[" & champFacteur & "] = " & IIf(typechampfacteur = 10, "'" & rs1.Fields(champFacteur) & "'", rs1.Fields(champFacteur))
rs2.FindFirst critere
If rs2.NoMatch Then
rs2.AddNew
rs2.Fields(champFacteur) = rs1.Fields(champFacteur)
Else
rs2.Edit
End If
If rs2.Fields(champConcatenation) <> "" Then rs2.Fields(champConcatenation) = rs2.Fields(champConcatenation) & separateur
rs2.Fields(champConcatenation) = rs2.Fields(champConcatenation) & rs1.Fields(champConcatenation)
rs2.Update
rs1.MoveNext
Wend
End Function |
Partager