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
| Option Compare Database
'-------------------------------------------------------------------------
' Amorce du comptage
'-------------------------------------------------------------------------
Function AilesRivetsRecursonsAmorce()
Dim MaBase As DAO.Database
Dim Amorce As Integer, LeComposé As String, LeComposant As String, TotalOut As Integer
Set MaBase = CurrentDb
Amorce = 1
LeComposé = InputBox("Composé : ")
LeComposant = InputBox("Composant : ")
' Initialisation de la pile utilisée pour les récursions
MaBase.Execute "DELETE FROM PILE ;"
'C'est parti pour compter les rivets !
Call AilesRivetsRecursonsJoyeusement(Amorce, LeComposé, LeComposant)
Set MaBase = Nothing
End Function
'-------------------------------------------------------------------------
' Routine récursive
'-------------------------------------------------------------------------
Sub AilesRivetsRecursonsJoyeusement(ByRef Amorce As Integer, ByVal Composé As String, ByVal Composant As String)
Dim MaBase As DAO.Database, Sqlresult As DAO.Recordset
Dim Kount As Integer, requete As String, theNiveau As Integer
Set MaBase = CurrentDb
requete = "SELECT COUNT(*) AS Kount FROM PILE ;"
Set Sqlresult = MaBase.OpenRecordset(requete, dbOpenDynaset)
Kount = Sqlresult.Fields("Kount")
If Kount = 0 Then
theNiveau = 1
Else
requete = "SELECT DISTINCT MAX(Niveau) AS MaxNiveau FROM PILE ;"
Set Sqlresult = MaBase.OpenRecordset(requete, dbOpenDynaset)
theNiveau = Sqlresult.Fields("MaxNiveau").Value + 1
End If
If Amorce = 1 Then
requete = "INSERT INTO PILE (EnsembleId, EnsembleParent, Quantite, Niveau) VALUES ('" & Composé & "', 0, 0, 1) ;"
MaBase.Execute requete
requete = "INSERT INTO PILE (EnsembleId, Ensembleparent, Quantite, Niveau)" _
& " SELECT EnsembleId, Ensembleparent, Quantite, " & 2 _
& " FROM COMPOSANT" _
& " WHERE Ensembleparent = '" & Composé & "' ;"
MaBase.Execute requete
'' Allez petit ! on récurse !
Call AilesRivetsRecursonsJoyeusement(0, Composé, Composant)
Else
requete = "SELECT COUNT(*) AS Kount " _
& "FROM (SELECT x.EnsembleId " _
& "FROM COMPOSANT AS x INNER JOIN PILE AS y ON x.Ensembleparent = y.EnsembleId " _
& "WHERE Niveau = " & theNiveau - 1 & ") as truc ; "
Set Sqlresult = MaBase.OpenRecordset(requete, dbOpenDynaset)
Kount = Sqlresult.Fields("Kount")
'' Tant que Kount > 0 c'est qu'on n'a pas fini d'empiler
If Kount > 0 Then
requete = "INSERT INTO PILE (EnsembleId, Quantite, Niveau, Ensembleparent) " _
& " SELECT x.EnsembleId, SUM(x.Quantite * y.Quantite), " & theNiveau & ", x.Ensembleparent " _
& " FROM COMPOSANT AS x INNER JOIN PILE AS y ON x.Ensembleparent = y.EnsembleId " _
& " WHERE Niveau = " & theNiveau - 1 _
& " GROUP BY x.EnsembleId, x.Ensembleparent, " & theNiveau & " ;"
MaBase.Execute requete
Call AilesRivetsRecursonsJoyeusement(0, Composé, Composant)
Sqlresult.Close
End If
End If
End Sub |
Partager