Supprimer toutes les tables d'une base de données 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 toutes les tables d'une base

Avec le volet précédent, nous avons appris très simplement à supprimer une table désignée par l'utilisateur de la base de données. Avec cette nouvelle astuce, nous allons entreprendre des nettoyages de masse pour supprimer les tables et requêtes et pourquoi pas les formulaires et les états.



Sur l'exemple illustré par la capture, l'utilisateur dispose de deux boutons pour engager la suppression de toutes les tables et de toutes les requêtes de la base de données. Mais pour purger correctement cette base, l'opérateur peut émettre des exceptions à l'aide de deux zones de saisie placées respectivement au-dessus des boutons. Il y énumère les noms des tables et requêtes à conserver. Dès lors, lorsqu'il clique sur le premier puis sur le second bouton, toutes les tables et requêtes sont supprimées, sauf celles mentionnées. Et c'est ce que résume le volet de navigation sur la gauche de l'écran, à l'issue du traitement.

Base de données Access à télécharger
Pour la mise en place de la solution automatisée, nous suggérons d'appuyer les travaux sur une base de données offrant quelques tables et requêtes ainsi que ce formulaire muni de ces deux boutons et zones de saisie.

• Télécharger le fichier compressé supprimer-toutes-tables.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é,

Le volet de navigation sur la gauche de l'écran, liste tous les objets de cette base de données. Et comme vous pouvez le voir, les tables et requêtes sont nombreuses. En bout de course, apparaît l'unique formulaire de cette base.

• Double cliquer sur le formulaire fParcourir pour l'exécuter,

Nous retrouvons bien les deux boutons ainsi que les deux zones de texte pour émettre des exceptions lors des suppressions de masse. Bien sûr, ces boutons ne sont pas encore opérationnels à ce stade. Mais ils sont déjà associés à des procédures événementielles.

• A gauche du ruban Accueil, cliquer sur la flèche du bouton Affichage,
• Dans les propositions, choisir de l'ouvrir en mode Création,
• Sur le formulaire en conception, cliquer sur le bouton Supprimer les tables,
• Dès lors, activer l'onglet Evénement de sa feuille de propriétés,

Comme vous pouvez le voir, une procédure événementielle est effectivement déjà associée à son événement Au clic.

• A l'extrémité droite de la zone, cliquer sur le petit bouton qui se propose,

De cette manière, nous basculons dans l'éditeur VBA Access, entre les bornes de la procédure supTables_Click. Et à ce titre, vous pouvez aussi noter la présence de la procédure supRequetes_Click associée au second bouton pour supprimer les requêtes.

Private Sub supRequetes_Click()

End Sub

Private Sub supTables_Click()

End Sub

La collection des tables
Pour entreprendre la suppression de toutes les tables de la base de données, il faut être en mesure de les parcourir une à une. Et pour cela, il suffit de déclencher une boucle sur la collection TableDefs, celle précisément des tables de la base. Donc, des objets de base de données sont nécessaires, entre autres.

• Dans les bornes de la procédure supTables_Click, ajouter les déclarations suivantes :

...
Dim base As Database: Dim table As TableDef
Dim rel As Relation: Dim requete As String
Dim exc() As String: Dim suppr As Boolean: Dim i As Byte
...

Nous déclarons tout d'abord un objet de base de données (Database) pour pouvoir pointer sur la base de données en cours. L'objet table déclaré comme un objet TableDef représente une table de la base de données au sens large. C'est elle que nous allons utiliser pour les parcourir toutes. De plus, nous déclarons un objet (rel) représentant une relation de table. En effet, la suppression des tables ne peut être entreprise que lorsque plus aucune liaison n'existe avec une autre table. Nous allons donc exploiter cet objet pour parcourir la collection des relations afin de les supprimer toutes.

Ensuite, la variable requete doit mémoriser la syntaxe Sql permettant d'engager la suppression des tables. Le tableau exc doit accueillir tous les noms de table à ne surtout pas supprimer. C'est un test sur ces noms qui déterminera la valeur de la variable booléenne suppr. Et c'est son état qui définira si la suppression peut ou non être engagée. Enfin, nous exploiterons la variable i comme compteur de boucle pour parcourir toutes ces exceptions.

Pointer sur la base de données en cours
Maintenant que les variables sont déclarées, nous allons en profiter pour initialiser l'objet base afin de le faire pointer sur la base de données en cours. Dans le même temps, nous allons en profiter pour stocker tous les noms de table à ne pas toucher, dans le tableau des exceptions (exc).

• A la suite du code, ajouter les instructions VBA suivantes :

...
Set base = CurrentDb()

If (listeT.Value <> "") Then exc = Split(listeT.Value, ";")
...

Nous le savons désormais parfaitement, c'est la fonction CurrentDb qui désigne la base de données active. Grâce à cette affectation, l'objet base hérite instantanément des propriétés et méthodes pour piloter ses objets. C'est ainsi et grâce à lui que nous pourrons exercer les suppressions de masse sur les tables.

La zone de texte des exceptions pour les tables est nommée listeT. Si son contenu n'est pas vide (<>""), nous séparons les noms grâce à la fonction Split engageant la découpe sur le point-virgule (;). C'est ainsi que chaque nom de table à préserver est désormais stocké dans une rangée indépendante du tableau de variables (exc).

Parcourir les tables de la base
C'est désormais l'objet table qui doit entrer en jeu dans une boucle For Each pour parcourir la collection TableDefs des tables de la base de données.

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

...
For Each table In base.TableDefs
suppr = True

Next table
...

A chaque passage dans cette boucle, donc pour chaque nouvelle table analysée, nous initialisons la variable booléenne de test à True. En effet et jusqu'à preuve du contraire, nous considérons que la table doit être supprimée. C'est un autre test à engager qui nous permettra de basculer son état si d'aventure le nom de la table analysée fait partie de la liste des exceptions.

Ignorer les tables du système
Le code VBA a la faculté de déceler les tables système qui sont naturellement masquées pour l'utilisateur Lambda. Ces tables ne doivent surtout pas être supprimées. Elles sont faciles à reconnaître. Elles sont soit préfixées du terme MSys, soit du symbole du tilda (~).

• A la suite du code de la boucle, créer l'instruction conditionnelle suivante :

...
If Left(table.Name, 4) <> "MSys" And Left(table.Name, 1) <> "~" Then

End If
...

Nous décidons donc d'envisager le traitement à suivre, si et seulement si, la table en cours d'analyse n'est pas une table système.

Supprimer les relations de la table
Donc lorsqu'il ne s'agit pas d'une table système et comme nous l'avons annoncé précédemment, pour pouvoir envisager sa suppression, nous devons éliminer toute liaison l'engageant. Et pour cela, nous devons parcourir la collection des relations à la recherche de celles qui impliquent ce nom de table.

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

...
For Each rel In base.Relations
If rel.Table = table.Name Or rel.ForeignTable = table.Name Then
base.Relations.Delete (rel.Name)
End If
Next rel
...

Pour chaque relation, nous cherchons à savoir si la table (table.Name) engage une clé primaire (rel.table) ou au moins une clé étrangère (rel.ForeignTable). Si l'un ou l'autre critère est vérifié, nous engageons la suppression de cette relation grâce à notre objet base, qui décidément permet bien de manipuler chaque élément de la base de données.

Tables à ne pas supprimer
Nous avons déjà vérifié que la table en cours n'était pas une table système. De fait, nous avons supprimé les potentielles relations l'engageant. Avant d'enclencher sa suppression, nous devons maintenant vérifier qu'il ne s'agit pas d'une table à préserver. Et pour cela, nous devons parcourir le tableau de variables des exceptions pour vérifier que son nom ne figure pas dans la liste.

• Toujours dans l'instruction conditionnelle et après la boucle For Each, créer la boucle suivante :

...
For i = 0 To UBound(exc)
If (table.Name = exc(i)) Then
suppr = False
Exit For
End If
Next i
...

Nous parcourons donc chaque élément du tableau des exceptions jusqu'au dernier (UBound(exc)). Dans l'énumération, si l'un des noms correspond à la table en cours d'analyse (table.Name = exc(i)), nous basculons la variable booléenne à False. Nous utiliserons ensuite cet indicateur pour que le code VBA n'influe pas sur cette table. Ensuite, nous sortons de la boucle (Exit For), puisqu'il n'est pas nécessaire de la poursuivre.

Supprimer la table en cours d'analyse
C'est maintenant que nous devons engager la suppression de toutes les tables parcourues, dans la mesure où la variable suppr est bien réglée sur la valeur True, indiquant qu'il ne s'agit pas d'une table à préserver. Et pour cela, nous allons répliquer la technique du volet précédent qui avait consisté à engager une requête Sql de suppression de table.

• Après la boucle et toujours dans l'instruction conditionnelle, ajouter cette nouvelle condition :

...
If (suppr = True) Then
requete = "DROP TABLE [" & table.Name & "];"
base.Execute requete
Application.RefreshDatabaseWindow
End If
...

Lorsque l'autorisation est donnée (suppr = True), nous construisons la syntaxe de la requête de suppression de table(DROP TABLE) sur le nom de la table en cours d'analyse (table.Name). Dès lors, nous engageons l'exécution de cette requête grâce à la méthode Execute de l'objet base. Puis, nous commandons le rafraîchissement des fenêtres de l'application (Application.RefreshDatabaseWindow) pour notamment actualiser l'état du volet de navigation. C'est ainsi que nous allons pouvoir constater en temps réel l'élimination des tables de la base de données.

• Enregistrer les modifications (CTRL + S) et basculer sur le formulaire (ALT + Tab),
• L'exécuter par exemple avec la touche F5 du clavier,
• Dans la première zone de saisie, taper quelques noms de table à préserver comme :

Clients;Communes;Produits

Il ne faut pas oublier les points-virgules (;) utilisés comme délimiteurs.

• Dès lors, cliquer sur le bouton Supprimer les tables,

Comme vous pouvez l'apprécier en consultant le volet de navigation sur la gauche de l'écran, toutes les tables de la base de données sont automatiquement supprimées. Seules subsistent celles dont les noms ont été mentionnés dans l'énumération de la première zone de saisie.

Supprimer toutes les requêtes de la base
Maintenant, pour supprimer toutes les requêtes de la base de données, tout en gérant les exceptions, le principe est le même. Il est même encore plus simple. En effet, il n'est pas nécessaire de supprimer les relations au préalable. Elles n'existent pas entre les requêtes. Par contre, il n'est plus question de parcourir la collection des tables de la base. Nous devons désormais parcourir la collection des requêtes de la base. Et comme une requête est une table spécifique, la syntaxe de la requête Sql de suppression est strictement identique.

• Revenir dans l'éditeur VBA Access,
• Dans la procédure supRequetes_Click, adapter le précédent code comme suit :

Private Sub supRequetes_Click()
Dim base As Database: Dim req As QueryDef
Dim requete As String: Dim exc() As String
Dim suppr As Boolean: Dim i As Byte

Set base = CurrentDb()

If (listeReq.Value <> "") Then exc = Split(listeReq.Value, ";")

For Each req In base.QueryDefs
suppr = True
For i = 0 To UBound(exc)
If (req.Name = exc(i)) Then
suppr = False
Exit For
End If
Next i

If (suppr = True) Then
requete = "DROP TABLE [" & req.Name & "];"
base.Execute requete
Application.RefreshDatabaseWindow
End If
Next req

base.Close

Set req = Nothing
Set base = Nothing
End Sub

La boucle sur les relations disparaît. De fait, la déclaration de sa variable disparaît aussi. La variable objet change pour définir un objet représentant une requête au sens large (Dim req As QueryDef). La gestion des exceptions est adaptée sur le nom de la seconde zone de texte (listeReq). La boucle est désormais engagée sur la collection des requêtes (For Each req In base.QueryDefs). La requête de suppression est maintenant déclenchée sur le nom de la requête en cours d'analyse (requete = "DROP TABLE [" & req.Name & "];).

Notez et même si nous ne l'avons pas précisé dans la procédure des tables qu'il est toujours important de fermer et de détruire les objets à l'issue du traitement :

base.Close
Set req = Nothing
Set base = Nothing

• Enregistrer les modifications et basculer sur le formulaire en exécution,
• Dans la seconde zone de saisie, taper quelques requêtes à préserver, comme :

R_Clients;R_Dep_Uniques

• Puis, cliquer sur le bouton Supprimer les requêtes,

Là encore en consultant le volet de navigation, vous constatez que seules les requêtes désignées sont conservées. Toutes les autres sont automatiquement supprimées.