Supprimer toutes les relations des tables en VBA Access

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Supprimer les relations entre les tables

Avec cette nouvelle astuce VBA Access, nous allons apprendre à automatiser la suppression des relations entre les tables, par le code VBA. Ce mécanisme est intéressant à plus d'un titre. Le plus flagrant concerne celui de la suppression des tables. Access n'autorise pas la suppression d'une table tant qu'elle est encore liée à une autre table.

Base de données Access à télécharger
Pour développer ce code VBA au demeurant trivial, nous suggérons d'appuyer les travaux sur une base de données offrant plusieurs tables reliées entre elles.

• Télécharger le fichier compressé supprimer-les-relations.rar en cliquant sur ce lien,
• Le décompresser dans le dossier de votre choix,
• Double cliquer sur le fichier résultant pour l'ouvrir dans Access,
• Puis, cliquer sur le bouton Activer le contenu du bandeau de sécurité,

Comme vous le constatez, cette petite base de données est constituée d'un formulaire et de cinq tables. Quatre d'entre elles sont impliquées dans des relations.

• En haut de la fenêtre Access, cliquer sur l'onglet Outils de base de données,
• Dans ce ruban, cliquer sur le bouton Relations,

Vous voyez ainsi l'architecture de cette petite base qui est utilisée à l'origine, pour facturer les clients et gérer les stocks. Ce sont ces liaisons qui interviennent entre chaque table parente et chaque table enfant, que nous devons supprimer automatiquement par le code VBA Access. Il s'agit d'un processus d'autant plus intéressant à mettre en place sur des bases de données volumineuses aux architectures complexes.

Le formulaire
Très simplement, c'est au clic sur un bouton du formulaire que nous devons déclencher ce code VBA.

• Dans le ruban Conception de relations, cliquer sur le bouton Fermer,
• Dans le volet de navigation, double cliquer sur le formulaire fRelations pour l'afficher,

Comme vous pouvez le voir, il est seulement doté d'un bouton.



C'est lui qui doit déclencher le code VBA Access permettant de supprimer toutes les relations entrant en jeu entre toutes les tables.

• A gauche du ruban Accueil, cliquer sur la flèche du bouton Affichage,
• Dans les propositions, choisir le mode Création,
• Sur le formulaire en conception, cliquer sur le bouton pour le sélectionner,
• Dès lors, activer l'onglet Evénement de sa feuille de propriétés,
• Cliquer ensuite sur le petit bouton associé à son événement Au clic,
• Dans la boîte de dialogue qui suit, choisir le Générateur de code et valider par Ok,

C'est ainsi que nous basculons dans l'éditeur VBA Access, entre les bornes de la procédure événementielle supRel_Click. supRel est le nom du bouton. Click est son événement déclencheur.

Les variables
Nous devons piloter les relations des tables dans cette base de données. Nous avons donc besoin de trois variables pour représenter ces objets respectifs. Et il faut bien comprendre que pour accéder au coeur de cette base, une référence précise est nécessaire. Mais nous avions anticipé, elle existe déjà. Nous proposons de le constater.

• Au-dessus de la feuille de code, cliquer sur le menu Outils,
• Dans les propositions, choisir l'option Référence,

La librairie qui nous intéresse se nomme Microsoft Office 16.0 Access database engine Object Library. Et comme vous pouvez le voir, sa case est déjà cochée.

• Cliquer sur le bouton Ok de la boîte de dialogue pour revenir sur la feuille de code,
• Dans la procédure supRel_Click, ajouter les déclarations et l'affectation suivantes :

...
Dim base As Database: Dim rel As Relation
Dim table As TableDef

Set base = CurrentDb()
...

Nous déclarons un objet de type Database (base) pour piloter la base de données en cours. Nous en déclarons un autre (table) de type TableDef pour représenter une table au sens large. Nous le verrons, nous l'utiliserons pour parcourir toutes les tables de la base de données. Puis, nous déclarons un dernier objet de type Relation pour représenter une relation entre tables au sens large. Là aussi, nous l'utiliserons pour parcourir toutes les relations qui impliquent chaque table. Enfin, nous exploitons la fonction VBA Access CurrentDb() pour initialiser (Set) l'objet base sur la base de données en cours. C'est ainsi qu'il hérite des propriétés et méthodes pour piloter cette dernière.

Parcourir toutes les tables
Pour analyser chaque table de la base de données, nous devons engager une boucle For Each sur la collection des tables, avec notre objet table.

• A la suite du code, créer la boucle For Each suivante :

...
For Each table In base.TableDefs
If Left(table.Name, 4) <> "MSys" Then

End If
Next table
...

La propriété héritée TableDefs par l'objet base, représente la collection des tables dans cette base de données. Dans cette boucle For Each, nous engageons l'objet table dans (In) cette collection, pour les analyser toutes. A chaque passage néanmoins, nous nous assurons qu'il ne s'agit pas d'une table système (MSys). Pour cela, nous testons les quatre premiers caractères du nom (Name) de la table en cours grâce à la fonction VBA Left. Si le test échoue (<> "MSys"), nous en concluons (Then) qu'il s'agit d'une table utilisateur et nous poursuivons le traitement.

Les relations de chaque table
Nous devons maintenant analyser toutes les relations en vigueur dans cette base de données. Dès que l'une d'entre elles implique la table en cours, soit par une clé primaire, soit par une clé externe, nous devons la supprimer.

• Dans l'instruction conditionnelle, créer la nouvelle boucle For Each suivante :

...
For Each rel In base.Relations
If rel.Table = table.Name Or rel.ForeignTable = table.Name Then

End If
Next rel
...

Toujours grâce à une boucle For Each donc, nous engageons cette fois l'objet rel dans (In) la collection des relations (Relations) de la base de données en cours (base). Si cette relation concerne la table en cours d'analyse (table.Name) par la première boucle, sur une clé primaire (rel.Table) ou encore (Or) sur une clé étrangère (rel.ForeignTable), alors (Then) nous poursuivons le traitement puisqu'il est question de la supprimer.

Supprimer les relations
Si le double test est concluant pour la table en cours d'analyse, nous devons supprimer la relation concernée. Et pour cela, la collection des relations héritée par l'objet base propose une méthode dédiée.

• Dans cette nouvelle instruction conditionnelle, ajouter la ligne VBA suivante :

...
base.Relations.Delete (rel.Name)
...

C'est donc la méthode Delete de cette collection, avec le nom de la relation en paramètre, qui permet de supprimer cette dernière. Comme elles sont toutes passées en revue grâce à cette double boucle, elles devraient toutes être supprimées.

Remarque : Comme la propriété Name renseigne sur le nom de la relation, il est possible de prévoir un traitement automatisé pour supprimer les relations, tout en en préservant certaines, identifiées par des noms à ne pas toucher.

Comme vous le savez, à l'issue du traitement, il convient de détruire les objets qui ne sont plus utilisés.

• Après la double boucle, ajouter les instructions VBA suivantes :

...
Set rel = Nothing
Set table = Nothing
base.Close
Set base = Nothing
...

Tester la suppression des relations
Il est temps de tester ce code VBA.

• Enregistrer les modifications (CTRL + S) et basculer sur le formulaire Access (ALT + Tab),
• Exécuter ce dernier, par exemple avec la touche F5 du clavier,
• Puis, cliquer sur son unique bouton,
• En haut de la fenêtre Access, cliquer sur l'onglet Outils de base de données,
• Dans la section Relations du ruban, cliquer sur le bouton Relations,

Comme vous pouvez le voir, les relations qui liaient autrefois ces tables, ont toutes disparu en un éclair et ce, avec un petit code VBA Access fort simple.