Contraintes avancées sous Microsoft Access

Cet article destiné aux habitués de Microsoft Access connaissant les bases du langage VBA et du langage SQL vous propose un moyen de mettre en place un système de contrôle des données avant leur enregistrement en se basant sur un mécanisme propre à quasiment tous les SGBD mais un peu caché sous Microsoft Access.

5 commentaires Donner une note à l'article (5)

Article lu   fois.

L'auteur

Profil ProSite personnel

Liens sociaux

Viadeo Twitter Facebook Share on Google+   

I. Les contraintes ValideSi et leur limite

Vous connaissez sûrement la propriété ValideSi des champs des tables Access. Cette propriété permet de définir une contrainte à valider lors de la création ou la modification de l'enregistrement. Si la contrainte n'est pas vérifiée, l'enregistrement des données est impossible.

Voici un exemple de propriété ValideSi concernant le montant total d'une facture dont l'énoncé interdit qu'il soit négatif :

Image non disponible

Si l'utilisateur tente une saisie ne correspondant pas à la règle, voici ce qu'il se passe :

Image non disponible

Dans l'exemple, l'utilisateur tente de saisir la donnée directement dans la table. N'ayez pas peur, ce mécanisme de contrainte est aussi valable lorsque l'utilisateur opère sa saisie depuis un formulaire ou que celle-ci est automatisée par une requête ou par du code VBA.

La propriété Message si Erreur permet quant à elle de définir le message qui sera affiché si la contrainte n'est pas respectée. Le but étant d'obtenir quelque chose de plus explicite que l'image ci-dessus. Toutefois, étant donné que dans bien des cas, le développeur gère lui-même ces propres messages d'alerte dans son code VBA, cette propriété dispose donc d'un intérêt limité.

Malheureusement, l'efficacité de la propriété ValideSi se limite exclusivement à tester la valeur de la donnée saisie par rapport à une règle de gestion basique. Ainsi, il est possible de vérifier la taille d'un texte, l'appartenance d'une date à un intervalle, la supériorité d'un nombre à une constante, etc. Par contre il est impossible de faire référence aux données déjà inscrites dans la base.

Prenons l'exemple d'une table tblLimite qui définirait le montant maximum autorisé pour la saisie des factures. Cette règle de gestion n'a pas vraiment de sens dans la vie réelle mais permet d'énoncer clairement mes propos.

La définition suivante de la propriété ValideSi paraît à priori juste, tout du moins du point de vue du sens :

 
Sélectionnez

[TTCFacture]<=(SELECT Limite FROM tblLimite)

Pourtant, elle est refusée par Microsoft Access.
Pourquoi ? Tout simplement, parce qu'il est impossible de faire référence à une autre donnée issue de la base dans la propriété ValideSi d'un champ.

II. ADODB et CONSTRAINT

Pour passer outre la limitation de la clause ValideSi vue plus haut, il est nécessaire de construire la contrainte manuellement à l'aide d'une instruction SQL après la création de la table à l'aide de l'instruction suivante :

Il est plus intéressant de la faire après la création de la table tout simplement car cela vous permet de bénéficier auparavant des assistants pour mettre en place vos champs,vos clés et vos index.

 
Sélectionnez

ALTER TABLE nom de la table 
  ADD CONSTRAINT nom de la contrainte 
    CHECK (clause de la contrainte)

Pour reprendre l'exemple d'une facture avec un montant exclusivement positif :

 
Sélectionnez

ALTER TABLE tblFacture 
  ADD CONSTRAINT MontantPositif 
    CHECK (TTCFacture>0)

Ou pour le cas de la limitation du montant d'une facture :

 
Sélectionnez

ALTER TABLE tblFacture 
  ADD CONSTRAINT LimiteMontant 
    CHECK   (TTCFacture<(
              SELECT limite 
              FROM tblLimite
                )
        )

Mais ce n'est pas aussi simple. Essayez de lancer cette instruction SQL depuis le générateur de requêtes et vous vous apercevrez qu'elle est refusée sous prétexte qu'une erreur de syntaxe serait présente au sein de la clause CHECK. Et pourtant, je peux vous assurer que l'instruction ci-dessus est valide.

Le problème est que le générateur de requête, tout comme l'assistant de création de tables, refuse de faire référence à des données externes à la saisie. Il en est de même pour les codes VBA basés sur DAO.

La solution consiste à lancer la requête de définition de la contrainte en utilisant un accès Jet OLE DB depuis une connexion ADODB. Pour faire simple, ADODB est un mode d'accès aux données assez proche de DAO (d'un point de vue structure) mais destiné avant tout à des moteurs de bases de données plus évolués (SQL Server idéalement). Ce n'est pas le cas ici, mais toujours est-il qu'il va permettre de faire ce que l'on souhaite. Il est inutile à ce stade de savoir comment ouvrir une connexion ADODB puisque depuis Access 2000, cette connexion est accessible aisément en VBA à l'aide de la propriété :

 
Sélectionnez

Application.CurrentProject.Connection

L'objet Connection retourné expose une méthode Execute permettant de lancer un ordre SQL. C'est donc cette méthode qui sera utilisée pour lancer la requête.

Il suffit donc de lancer le bloc d'instructions suivant depuis un module VBA :

 
Sélectionnez

Sub Contrainte()
Dim strSQL As String
strSQL = "ALTER TABLE tblFacture ADD CONSTRAINT LimiteMontant " & _
            "CHECK (TTCFacture<(SELECT limite FROM tblLimite))"
Application.CurrentProject.Connection.Execute strSQL
End Sub

Si l'utilisateur tente maintenant de saisir un montant supérieur à celui de la limite définie dans la table tblLimite, sa saisie est refusée :

Image non disponible

Il existe toutefois quelques limitations :

  • il est impossible de changer le message d'erreur affiché, contrairement à la propriété ValideSi,
  • il est déconseillé d'utiliser en même temps des contraintes CHECK et des propriétés ValideSi dans la même table.

III. Contraintes par jointure

Dans l'exemple plus haut, rien ne lie directement le montant limite au montant TTC de la facture. J'entends par là, que toutes les factures auront le même montant limite autorisé. Pourtant dans bien des cas, il est nécessaire que la contrainte évolue en fonction de la donnée saisie.

Dans le cas d'une table tblReglement, en lien avec la table tblFacture, il est fort probable qu'il soit interdit de saisir un réglement dont le montant dépasse celui de la facture.

Image non disponible

Le code SQL de la contrainte souhaitée est alors :

 
Sélectionnez

ALTER TABLE tblReglement 
  ADD CONSTRAINT ReglementMaxi  
    CHECK   (MontantReglement <= (
                  SELECT TTCFacture 
                  FROM tblFacture 
                  WHERE idFacture=tblreglement.idfacture
                  )
        )

La contrainte CHECK vérifie alors que le montant du réglement est inférieur ou égal à celui de la facture. Le lien entre la facture concernée par le règlement et l'enregistrement dans la table tblFacture est assuré par la clause WHERE idFacture=tblreglement.idFacture qui signifie :

"La ligne de la table tblFacture où l'idFacture est égal à l'idFacture de la table tblReglement en cours de modification "

IV. Contraintes calculées

Il s'agit là de la partie la plus complexe de mise en place de contraintes. Prenons comme exemple une contrainte devant répondre à la règle de gestion suivante :

La somme des règlements d'une facture ne doit pas être supérieure au montant total de la facture.

Son code SQL serait :

 
Sélectionnez

ALTER TABLE tblReglement 
  ADD CONSTRAINT SommeReglementMaxi  
        CHECK   (
          (
          SELECT Sum(MontantReglement) 
          FROM tblReglement tblReglementCalcul 
          WHERE tblReglementCalcul.idFacture=tblreglement.idfacture
          )  
          <= 
          (
          SELECT TTCFacture 
          FROM tblFacture 
          WHERE idFacture=tblreglement.idfacture
          )
        )

La clause CHECK est alors composée de deux requêtes :

 
Sélectionnez

CHECK (requete1<=requete2) 

La partie de droite est simple, il s'agit de la même que celle vue plus haut. Elle retourne le montant TTC de la facture correspondante au règlement.
Si celle de gauche a aussi un sens très simple : retourner la somme des règlements pour cette facture, son écriture est plus complexe. En effet, lors de la création ou de la modification d'un règlement, le moteur travaille sur la table tblReglement et l'enregistrement en cours est composé de IdReglement, idFacture, MontantReglement, DateReglement, CommentaireReglement. Rien n'indique les montants déjà réglés pour cette facture à cet instant. Pour connaitre ce montant, il faut accéder à une nouvelle vue de la table tblReglement (comme une photocopie). C'est sur cette vue que sera effectué le calcul. Schématiquement, c'est comme si deux tableaux étaient ouverts en même temps. Le stylo de la main droite est prêt à remplir le premier, pendant que l'oeil, aidé de l'index de la main gauche, vérifie que le montant ne dépassera pas la somme restant à payer sur le second tableau. Et comme il est impossible d'ouvrir deux fois le même objet avec le même nom, SQL impose d'utiliser un alias pour le deuxième appel, d'où l'instruction :

 
Sélectionnez

FROM tblReglement tblReglementCalcul

Qui peut aussi s'écrire :

 
Sélectionnez

FROM tblReglement AS tblReglementCalcul

La clause :

 
Sélectionnez

tblReglementCalcul.idFacture=tblreglement.idfacture

Permet ainsi de faire le lien entre les deux vues en se basant sur l'idFacture.

V. Contraintes CHECK (X=TRUE)

La forme CHECK (X=TRUE) permet de mettre en place n'importe quelle condition qui ne serait pas basée sur la valeur des données saisies. En fait, il s'agit simplement de tester si la condition à gauche (X) du signe = est vraie. Dès lors toutes les fantaisies sont possibles.

Ainsi, il est possible par exemple de limiter le nombre de lignes dans une table. Dans l'exemple suivant, il est interdit de saisir plus d'un enregistrement dans la table tblLimite.

 
Sélectionnez

ALTER TABLE tblLimite 
  ADD CONSTRAINT NbLigneMaxi  
    CHECK   (
          (
          SELECT Count(limite)<2 
          FROM tblLimite
          )
          =TRUE
        )

Et puis, pourquoi pas, ne pas interdire la modification des données tous les lundis :

 
Sélectionnez

ALTER TABLE tblLimite 
  ADD CONSTRAINT InterditLundi 
    CHECK (Weekday(date())<>2=TRUE )

Comme vous le voyez, la puissance du code SQL permet de satisfaire quasiment à toutes les exigences. Attention cependant : il s'agit de code SQL Access et non de code VBA. Vous ne pouvez donc pas faire appel aux éléments propres à VBA, tels que les fonctions personnalisées d'un module par exemple. De plus sous Access 2007, les champs à valeurs multiples ne peuvent pas intégrer la clause CHECK.

VI. Suppression des contraintes

Les contraintes ainsi créées n'étant pas répertoriées dans l'interface graphique d'Access, il est nécessaire, pour les supprimer, d'utiliser la même méthode que pour leur création, à savoir une requête SQL exécutée avec une connexion ADO DB. La syntaxe de l'ordre SQL est la suivante :

 
Sélectionnez

ALTER TABLE <nomdelatable> DROP <nomdelacontrainte>

Ce qui donne en VBA :

 
Sélectionnez

Sub SuppressionContrainte()
Dim oCnx As ADODB.Connection
Dim strSQL As String
Set oCnx = Application.CurrentProject.Connection
strSQL = "ALTER TABLE tblLimite DROP CONSTRAINT LimiteMontant"
oCnx.Execute strSQL
End Sub

Pour pouvoir supprimer une table, il est nécessaire de supprimer toutes les contraintes s'y rattachant.

VII. Modification d'une contrainte

La syntaxe :

 
Sélectionnez

ALTER TABLE <nomdelatable> ALTER  CONSTRAINT ... 

n'étant pas reconnue, il est impossible de modifier le code de définition d'une contrainte. La solution : supprimer et recréer.

VIII. Capture dans un formulaire

VIII-A. Affichage personnalisé

Comme expliqué au début de ce document, lorsque les contraintes sont définies dans une clause CHECK en SQL et non via la propriété ValideSi, il est impossible de définir le message d'erreur à afficher. Cependant lorsque la saisie est opérée depuis un formulaire, l'évènement Sur Erreur (OnError) est levé dès qu'une contrainte n'est pas respectée et le code d'erreur correspondant est le n°3317. Vous pouvez donc utiliser cet évènement pour personnaliser l'affichage.

 
Sélectionnez

Private Sub Form_Error(DataErr As Integer, Response As Integer)
Select Case DataErr
    Case 3317 ' erreur de validation de containte
        MsgBox "Une contrainte n'est pas validée"
        ' Continue sans afficher l'erreur standard
        Response = acDataErrContinue
    Case Else
        Response = acDataErrDisplay
End Select
End Sub

Malheureusement, comme vous pouvez le constater l'exemple de code ci-dessus affiche qu'une contrainte n'est pas respectée mais est incapable d'énoncer laquelle. A ce stade de l'exécution, bien que l'évènement OnError du formulaire soit déclenché aucune information concernant l'erreur n'a été envoyée au code qui s'exécute. C'est seulement quand l'événement OnError sera terminé que la gestion d'erreur en VBA va pouvoir être utilisée via un objet Err. L'astuce, provenant de Thierry Gasperment, consiste donc à démarrer la minuterie du formulaire dans l'évènement OnError de telle sorte à capturer l'erreur à sa sortie en tentant une nouvelle fois d'enregistrer les données.

 
Sélectionnez

Private Sub Form_Error(DataErr As Integer, Response As Integer)
Select Case DataErr
    Case 3317 ' erreur de validation de containte
        ' Lance la minuterie
        Me.TimerInterval = 1
        ' Continue sans afficher l'erreur standard
        Response = acDataErrContinue
    Case Else
        Response = acDataErrDisplay
End Select
End Sub



Private Sub Form_Timer()
    ' Arrête la minuterie
    Me.TimerInterval = 0

On Error GoTo gestion_erreurs
    ' Tente d'enregistrer les données
    Me.Dirty = False
    Exit Sub
    
    
gestion_erreurs:
' Si une erreur est lancée, lance la procédure GestionErreur()
GestionErreur Err.Description
End Sub

Voici le code de la procédure de gestion d'erreur :

 
Sélectionnez

Public Sub GestionErreur(pMessage As String)
Dim lPosArg1 As Long
Dim lPosArg2 As Long
Dim lLenRule As Long
Dim lRule As String
Dim lNewMessage As String
On Error GoTo gestion_erreurs
' Position des arguments dans le message standard
lPosArg2 = InStr(1, AccessError(3317), "|2")
lPosArg1 = InStr(1, AccessError(3317), "|1")
If lPosArg2 > 0 Then
    ' Taille du nom de la règle
    lLenRule = InStr(1, pMessage, Mid(AccessError(3317), lPosArg2 + 2, lPosArg1 - lPosArg2 - 2)) - lPosArg2
    ' Lit le nom de la règle en erreur
    lRule = Trim(Mid(pMessage, lPosArg2, lLenRule))
    ' Construit le message personnalisé
    lNewMessage = "La règle " & lRule & " n'est pas validée :"
    ' Ajout de texte en fonction de la règle non validée
    Select Case lRule
        Case "ReglementMaxi"
            lNewMessage = lNewMessage & vbCrLf & "Le règlement dépasse le montant de la facture"
        Case "SommeReglementMaxi"
            lNewMessage = lNewMessage & vbCrLf & "La somme des réglements dépasse le montant de la facture"
    End Select
    ' Affiche le message personnalisé
    MsgBox lNewMessage
Else
    ' Au cas où, affiche le message sans traitement
    MsgBox pMessage
End If
Exit Sub
gestion_erreurs:
MsgBox "Erreur  " & Err.Number & ", " & Err.Description
End Sub

Le principe de cette procédure est simple. Il consiste à découper le message d'erreur pour y récupérer le nom de la contrainte. Pour cela, il faut s'appuyer sur le message d'erreur d'origine défini par la fonction AccessError. Dans ce message d'origine, le nom de la contrainte est remplacé par le paramètre " |2 ". Il suffit donc de localiser ce paramètre dans le message d'erreur complet passé à la fonction pour être en mesure de définir la variable lRule qui contiendra, ainsi, le nom de la contrainte ayant levé l'erreur.

Enfin, la structure SELECT CASE permet de personnaliser le message à afficher en fonction du nom de la contrainte trouvé.

VIII-B. Afficher la définition de la contrainte

La propriété CheckConstraints du recordset d'un formulaire lié à une source de données regroupe la liste des contraintes séparées par le caractère Chr(0) alias vbNullChar. Vous pouvez utiliser la fonction ci-dessous pour accéder à la définition de la contrainte passée en paramètre :

 
Sélectionnez

Private Function GetRuleText(pRule As String) As String
On Error GoTo gestion_erreurs

Dim lCsts() As String
Dim lCpt As Long

lCsts = Split(Me.Recordset.Properties("CheckConstraints").Value, vbNullChar)
For lCpt = LBound(lCsts) To UBound(lCsts)
    If lCsts(lCpt) = pRule Then
        GetRuleText = lCsts(lCpt + 1)
    End If
Next
Exit Function
gestion_erreurs:
GetRuleText = ""
End Function

Ce qui donnerait dans la procédure vue plus haut :

 
Sélectionnez
' Construit le message personnalisé
    lNewMessage = "La règle " & lRule & " n'est pas validée :"
    lNewMessage = lNewMessage & vbCrLf & "Rappel de la règle : " & GetRuleText(lRule)
…

IX. Conclusion

La mise en place de telles contraintes est certes un peu fastidieuse car éloignée de tout assistant graphique mais elle apporte un véritable plus en terme de cohérence et d'intégrité de vos données. En outre, en étant ainsi gérée directement par le moteur de base de données, vous avez la garantie qu'aucun programme tiers ne viendra à l'encontre des règles de gestion que vous avez définies. Ceci n'est vraiment pas négligeable quand on sait à quel point l'interconnexion des applications Office est mise en jeu dans les développements professionnels.
Car si à l'heure actuelle votre code VBA présent dans vos formulaires suffit à gérer les contraintes que vous avez énoncées, qu'en sera-t-il demain pour un autre programme réalisé par un autre prestataire qui attaquera directement votre base de données ?

J'adresse un remerciement tout particulier à Thierry Gasperment pour son astuce concernant la capture de l'erreur au sein d'un formulaire.

Vous avez aimé ce tutoriel ? Alors partagez-le en cliquant sur les boutons suivants : Viadeo Twitter Facebook Share on Google+   

  

Copyright © 2009 Christophe Warin. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents, images, etc. sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts. Droits de diffusion permanents accordés à Developpez LLC.