Parcourir 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 :


Parcourir les tables d'une base

A l'occasion des astuces VBA Access précédentes, nous avons démontré plusieurs techniques pour parcourir les éléments d'une base de données, comme les enregistrements et les champs d'une table, mais aussi les contrôles d'un formulaire. Ici, nous intensifions nos efforts pour démontrer comment parcourir toutes les tables et requêtes d'une base de données. Ce procédé est fort utile à bien des égards. Dans l'application d'évaluation par QCM, nous l'avions appelé à la rescousse pour charger une liste déroulante de tous les questionnaires. En effet, chaque questionnaire était inscrit dans une table indépendante. Et bien sûr, de nouveaux questionnaires, donc de nouvelles tables, sont susceptibles d'être ajoutées à tout moment.



Dans l'exemple illustré par la capture, l'utilisateur clique sur un bouton (Lister) de formulaire. Et aussitôt, tous les noms des tables et requêtes de la base de données en cours, sont énumérés dans une zone de texte multiligne. Vous notez certaines spécificités de mise en forme rendues possibles grâce au format de texte enrichi appliqué à cette zone de saisie.

Base de données Access à télécharger
Pour la découverte de cette nouvelle astuce VBA Access, nous suggérons d'appuyer l'étude sur une base de données offrant d'assez nombreuses tables et requêtes.

• Télécharger le fichier compressé lister-toutes-les-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é,

En consultant le volet de navigation sur la gauche de l'écran, vous remarquez que cette base est composée de quelques tables et de quelques requêtes de différentes natures pour certaines d'ailleurs. En queue de liste, vous notez la présence d'un formulaire nommé fParcourir.

• Double cliquer sur ce formulaire fParcourir pour l'exécuter,
• Puis, cliquer sur le bouton intitulé Lister,

Bien sûr, rien ne se produit encore à ce stade. C'est tout l'enjeu de cette astuce.

• 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 la zone de texte pour la sélectionner,
• Puis, activer l'onglet Toutes de sa feuille de propriétés,

Comme l'indique sa propriété Nom, cette zone de saisie est reconnue sous l'intitulé listeTables. C'est par ce nom que nous pourrons la piloter en VBA pour la remplir des noms des tables et requêtes de la base de données.

• Maintenant, activer l'onglet Données de sa feuille de propriétés,

Comme l'indique son attribut Format du texte, cette zone est capable de recevoir des attributs de mise en forme (Texte enrichi). Ceux-ci doivent se paramétrer avec des balises Html à construire par le code VBA, comme nous l'avons déjà appris.

Au clic sur le bouton
Pour débuter la conception, nous devons déclencher un code VBA au clic sur le bouton Lister. C'est lui qui sera en charge de parcourir toutes les tables et requêtes de la base de données pour restituer leurs noms dans la zone de texte multiligne.

• Sur le formulaire en conception, cliquer sur le bouton Lister pour le sélectionner,
• Dès lors, activer l'onglet Evénement de sa feuille de propriétés,
• Cliquer sur le petit bouton à l'extrémité droite de son événement Au clic,
• Dans la boîte de dialogue qui suit, choisir le Générateur de code et valider par Ok,

Nous basculons ainsi dans l'éditeur de code VBA Access entre les bornes de la procédure événementielle lister_Click. Son code se déclenchera au clic sur ce bouton :

Private Sub lister_Click()

End Sub

Les variables de base de données
Pour manipuler les objets d'une base, nous avons besoin d'une variable représentant la base de données en cours. Dès lors, elle offrira des propriétés et méthodes pour accéder à ses tables et requêtes entre autres.

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

Private Sub lister_Click()
Dim base As Database
Dim table As TableDef: Dim requete As QueryDef
Dim debut

End Sub

L'objet base de type Database doit pointer sur la base de données en cours par instanciation. Un objet TableDef représente la définition stockée d'une table, soit une table pour faire simple. Et de la même façon l'objet requete doit permettre de manipuler les requêtes. Enfin, la variable debut non typée, n'est pas absolument nécessaire. Nous l'utiliserons pour créer une boucle de temps afin de faire apparaître les noms des tables et requêtes progressivement.

Pointer sur la base de données active
Il est maintenant question d'instancier la classe permettant de livrer à l'objet base la faculté de prendre le contrôle des éléments de la base de données en cours. Et pour cela, le VBA Access offre la fonction CurrentDb.

• Après la déclaration des variables, ajouter les deux instructions VBA suivantes :

...
listeTables.Value = "<u><strong>LES TABLES:</strong></u>"
Set base = CurrentDb()
...

Tout d'abord, nous inscrivons un titre dans la zone de texte multiligne. Nous l'avons dit, elle est paramétrée en texte enrichi. Et ce sont des balises Html qui permettent de le souligner et de le mettre en gras.

Ensuite, nous procédons à l'instanciation (Set) de la base de données en cours grâce à la fonction CurrentDb. Notre objet nommé base est désormais en mesure de la piloter.

Parcourir toutes les tables
C'est une collection particulière dont dispose désormais l'objet base fraîchement créé qui va maintenant permettre de parcourir toutes les tables de la base de données en cours.

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

...
For Each table In base.TableDefs
listeTables.Value = listeTables.Value & "<br />" & table.Name
Next table
...

La propriété TableDefs de l'objet base renvoie la collection des tables de la bdd. Grâce à une boucle For Each et à notre objet table, nous les parcourrons toutes tour à tour. C'est ainsi, à chaque passage dans cette boucle que nous restituons les noms de chacune (propriété Name), les unes en dessous des autres (balise Html<br />).

• Enregistrer les modifications (CTRL + S) et basculer sur le formulaire (ALT + Tab),
• L'enregistrer à son tour et l'exécuter avec la touche F5 du clavier,
• Puis, cliquer sur le bouton Lister,

Comme vous pouvez le voir, toutes les tables de la base de données sont aussitôt listées. Cependant, elles sont beaucoup plus nombreuses que prévu, la faute aux tables système. Elles sont masquées pour l'utilisateur mais pas pour le code VBA.

Ne pas lister les tables système
Nous devons donc ignorer ces tables particulières. Elles ont toutes le même préfixe de quatre lettres : MSys. Grâce à la fonction Left dans une instruction conditionnelle, nous allons pouvoir tester ces premiers caractères pour déterminer si le nom de la table doit apparaître ou non.

• Revenir dans l'éditeur VBA Access,
• Puis, encadrer la restitution des noms avec l'instruction conditionnelle suivante :

...
For Each table In base.TableDefs
If Left(table.Name, 4) <> "MSys" Then
listeTables.Value = listeTables.Value & "<br />" & table.Name
End If
Next table
...

Nous nous assurons que les quatre premières lettres (Left(table.Name, 4)) du nom de la table en cours d'analyse sont bien différentes (<>) de la séquence de caractères définissant une table système. Grâce à l'instruction conditionnelle, c'est seulement dans ce contexte que nous restituons l'intitulé dans la zone de texte multiligne.

Désormais, après avoir enregistré les modifications, si vous cliquez de nouveau sur le bouton Lister du formulaire, vous avez le plaisir de constater que les tables système ont été exclues de l'énumération.



Seuls subsistent les huit tables construites dans cette base de données.

Parcourir les requêtes de la base de données
Maintenant, nous devons ajouter dans l'énumération, la liste des noms de requêtes. Le principe est identique à celui des tables. Il s'agit de parcourir la collection des requêtes (QueryDefs) à l'aide d'une nouvelle boucle For Each. Cette fois, aucun test n'est nécessaire car il n'existe pas de requêtes système.

• Revenir dans l'éditeur VBA Access,
• A la suite du code VBA, construire la boucle For Each suivante :

...
listeTables.Value = listeTables.Value & "<br /><br/><u><strong>LES REQUETES :</strong></u>"
For Each requete In base.QueryDefs
listeTables.Value = listeTables.Value & "<br />" & requete.Name
Next requete
...

Avant de tester et comme vous le savez, toute variable objet qui n'est plus utilisée doit être détruite afin de libérer les ressources.

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

...
base.Close
Set table = Nothing
Set requete = Nothing
Set base = Nothing
...

Nous fermons tout d'abord la base de données ouverte en mémoire grâce à la méthode Close. Puis, nous détruisons les trois objets en les réinitialisant à Nothing.

• Enregistrer les modifications et revenir sur le formulaire,
• Puis, cliquer sur le bouton Lister,

Comme vous pouvez le voir, tous les noms de requêtes sont parfaitement ajoutés à la suite de l'énumération.

Lister les noms progressivement
Pour finir et pour agrémenter cette petite solution, nous proposons de faire apparaître ces noms progressivement. Et pour cela, nous devons gérer les incréments de temps grâce à la fonction Timer et une boucle de temporisation. C'est la raison de la présence de notre variable nommée debut. C'est une solution que nous avons déjà amenée à l'occasion d'une astuce précédente.

• Pour ce faire, adapter le code en suivant les instructions en gras :

Private Sub lister_Click()
Dim base As Database
Dim table As TableDef: Dim requete As QueryDef
Dim debut

listeTables.Value = "<u><strong>LES TABLES :</strong></u>"
Set base = CurrentDb()

For Each table In base.TableDefs
If Left(table.Name, 4) <> "MSys" Then
debut = Timer
While Timer < debut + 0.2
DoEvents
Wend
listeTables.Value = listeTables.Value & "<br />" & table.Name
End If
Next table

listeTables.Value = listeTables.Value & "<br /><br/><u><strong>LES REQUETES :</strong></u>"
For Each requete In base.QueryDefs
debut = Timer
While Timer < debut + 0.2
DoEvents
Wend
listeTables.Value = listeTables.Value & "<br />" & requete.Name
Next requete

base.Close
Set table = Nothing
Set requete = Nothing
Set base = Nothing

End Sub

Si vous cliquez de nouveau sur le bouton Lister, vous obtenez la même énumération des noms de tables et de requêtes que précédemment. Mais cette fois, ils apparaissent progressivement pour avoir le temps de les consulter les uns à la suite des autres.