Extraire depuis une base de données externe selon critères

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


Sujets que vous pourriez aussi aimer :


Requête Sql dans autre Bdd Access

Avec ce nouveau développement VBA Access, nous allons apprendre à ponctionner des données à partir d'une base externe en y exécutant des requêtes Sql distantes et chirurgicales.



Sur l'exemple illustré par la capture, ce sont trois listes déroulantes que nous relions entre elles. Lorsque l'utilisateur choisit un département avec la première liste déroulante, la deuxième se remplit des activités de sorties recensées dans ce département. Lorsqu'il choisit une activité avec cette deuxième liste déroulante, la troisième et dernière liste se remplit des villes proposant cette activité dans ce département. Pourtant, la base de données de ce formulaire n'héberge aucune information. Toutes ces actions de l'utilisateur déclenchent des requêtes Sql sur une base de données externe pour la questionner et importer les données concordantes, répondant aux choix entonnoirs.

Base de données Access à télécharger
Pour développer cette solution, nous suggérons d'appuyer l'étude sur deux fichiers Access à relier entre eux.

• Télécharger le fichier compressé requete-dans-autre-bdd.rar en cliquant sur ce lien,
• Le décompresser dans le dossier de votre choix,

Comme vous pouvez le voir, la décompression livre deux bases de données. Elles sont respectivement nommées id2sorties.accdb et requete-dans-autre-bdd.accdb. La première est la base externe à partir de laquelle il s'agit de récupérer les données par requêtes Sql pour articuler les trois listes déroulantes sur le formulaire de la seconde.

• Double cliquer sur le fichier id2sorties.accdb 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, vous notez que cette base de données n'est constituée que d'une seule table. Sa vocation et nous l'avons dit en effet, est de servir de source de données externe, un peu comme une base de données pour un site Web.

• Dans le volet de navigation, double cliquer sur la table societes,

Nous affichons ainsi son contenu en mode feuille de données. La barre de navigation en bas de la fenêtre, indique que cette table est faite de plus de 1000 enregistrements. Elle recense des activités de sorties.



Les champs que nous allons devoir questionner par requêtes Sql se nomment respectivement societes_departement, societes_activite et societes_ville. Il s'agit des informations que doivent respectivement réceptionner les trois listes déroulantes, en respectant les choix en cascade.

• Fermer la table puis fermer la base de données en cliquant sur la croix de sa fenêtre,
• Double cliquer sur le fichier requete-dans-autre-bdd.accdb pour l'ouvrir dans Access,
• Cliquer sur le bouton Activer le contenu du bandeau de sécurité,
• Dans le volet de navigation, double cliquer sur le formulaire f_recup pour l'exécuter,

Nous retrouvons bien le formulaire avec ses trois listes déroulantes. Bien sûr elles sont vides pour l'instant. L'objectif de ce volet est de les articuler entre elles sur la base des informations détenues dans l'autre base de données. La grille du dessous est un sous-formulaire. Il nous sera utile dans le volet suivant pour produire l'extraction des données correspondant aux choix entonnoirs réalisés avec ces listes déroulantes.

Découvrir le code VBA
Nous allons le découvrir, des fragments de code VBA sont déjà en place.

• A gauche du ruban Accueil, cliquer sur la flèche du bouton Affichage,
• Dans les propositions, choisir le mode Création,

Maintenant, si vous cliquez tour à tour sur les listes déroulantes et que vous consultez la feuille de propriétés, vous constatez qu'elles sont respectivement nommées : dep, activites et villes.

• Réaliser le raccourci clavier ALT + F11 pour basculer dans l'éditeur VBA Access,

Vous découvrez la présence de cinq procédures événementielles :

Private Sub villes_Change()

End Sub

Private Sub activites_Change()
villes.RowSource = "": villes.Value = ""

End Sub

Private Sub dep_Change()
activites.RowSource = "": villes.RowSource = ""
activites.Value = "": villes.Value = ""

End Sub

Private Sub Form_Load()
dep.RowSource = "": activites.RowSource = "":villes.RowSource = ""

End Sub

Sub charger_liste(controle As ComboBox, champ As String)

End Sub

La procédure villes_Change est vide. Nous l'implémenterons à l'occasion du volet suivant pour charger le sous-formulaire au choix d'une nouvelle ville. La procédure dep_Change intervient au changement de département. Grâce à la propriété RowSource, elle vide les contenus des deux listes dépendantes puis elle efface les potentiels précédents choix. La procédure activites_Change intervient au changement d'activité dans la deuxième liste déroulante. Elle vide le contenu de la liste des villes et efface son précédent potentiel choix. La procédure Form_Load agit au chargement du formulaire. Par mesure de sécurité, elle veille à ce que les contenus des trois listes déroulantes soient vierges.

La dernière procédure se nomme charger_liste. C'est elle que nous devons travailler dans ce volet. Elle doit exécuter des requêtes Sql sur la source de données externe en fonction du contrôle (liste déroulante) qui l'appelle. C'est la raison de la présence d'un objet de type contrôle en attente, en premier argument de cette procédure.

Les objets de base de données
Pour interroger les informations de la base de données externe, nous avons besoin de piloter une instance d'Access. Pour piloter cette instance, nous avons besoin de déclarer des objets de base de données. Et pour pouvoir déclarer ces objets, par anticipation, nous avions ajouté une référence nécessaire au projet. Elle se nomme Microsoft Office 16.0 Access database engine Object Library.

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

...
Dim autreBdd As Access.Application: Dim enr As Recordset
Dim chemin As String: Dim requete As String
...

L'objet autreBdd de type Access.Application doit piloter la nouvelle instance d'Access qu'il nous reste encore à créer. L'objet enr de type Recordset est fait pour manipuler les enregistrements de la source externe dans cette instance d'Access. Enfin, les deux dernières variables sont typées comme des textes (As String). La première doit mémoriser le chemin d'accès à la base externe. La seconde doit mémoriser la syntaxe Sql de la requête sélection à exécuter sur cette dernière.

Créer l'instance d'Access
Nous en avons l'habitude désormais, c'est la fonction VBA CreateObject qui permet d'instancier une classe. Avec la valeur Access.Application en paramètre, nous allons lui demander d'instancier la classe d'Access.

• Après les variables, ajouter les instructions VBA suivantes :

...
chemin = CurrentProject.Path & "\id2sorties.accdb"
Set autreBdd = CreateObject("Access.Application")
autreBdd.OpenCurrentDatabase chemin
autreBdd.Visible = False
...

Tout d'abord, nous reconstruisons le chemin d'accès à la base de données externe. C'est la propriété Path de l'objet CurrentProject qui renseigne sur le chemin de l'application locale. Nous lui associons le nom de la source externe (\id2sorties.accdb) et nous enregistrons le tout dans la variable chemin. Ensuite, nous initialisons (Set) l'objet autreBdd sur une nouvelle instance d'Access. L'objet hérite alors de propriétés et méthodes dédiées. C'est ainsi que nous exploitons sa méthode héritée OpenCurrentDatabase pour pointer sur la source externe (chemin). Comme nous souhaitons que ces manipulations de données se fassent en tâche de fond, nous réglons sa propriété héritée Visible à False. De cette manière, aucune nouvelle fenêtre d'Access n'apparaîtra.

La requête sélection
Maintenant, nous devons construire la requête sélection permettant de remplir la liste déroulante passée en premier paramètre de la procédure. S'il s'agit de la première liste déroulante, une simple requête sur le champ des départements est nécessaire. S'il s'agit de la liste des activités, nous avons besoin d'une requête sélection sur le champ des activités, avec une clause Where sur le département. S'il s'agit de la liste des villes, nous devons construire une requête sélection sur le champ des villes avec une double clause Where, à la fois sur le département et l'activité choisis en amont. C'est donc une instruction conditionnelle à trois branches que nous devons dégainer. Son critère doit porter sur le nom du contrôle passé en paramètre de la procédure.

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

...
If controle.Name = "dep" Then
requete = "SELECT DISTINCT societes_departement FROM societes"
ElseIf controle.Name = "activites" Then
requete = "SELECT DISTINCT societes_activite FROM societes WHERE societes_departement='" & dep.Value & "'"
Else
requete = "SELECT DISTINCT societes_ville FROM societes WHERE societes_departement='" & dep.Value & "' And societes_activite ='" & activites.Value & "'"
End If
...

Ce sont donc de classiques syntaxes SQL que nous exploitons. Vous notez qu'elles s'allongent au fur et à mesure que nous descendons dans les listes déroulantes dépendantes, en raison des clauses Where devant honorer les choix émis en amont, pour restreindre les données en conséquence.

Exécuter la requête
Pour exécuter la requête ainsi construite, nous le savons bien désormais, nous devons exploiter la méthode OpenRecordset mais sur la nouvelle instance d'Access cette fois puisqu'il est question d'interroger les données externes.

• Toujours à la suite du code VBA Access, ajouter l'instruction suivante :

...
Set enr = autreBdd.CurrentDb.OpenRecordset(requete)
...

Nous initialisons (Set) l'objet enr sur les enregistrements restreints (requete) grâce à la méthode héritée OpenRecordset de l'objet CurrentDb. C'est donc cet objet enr qui va maintenant hériter des propriétés et méthodes nécessaires pour manipuler les enregistrements de la base de données externe.

Parcourir les enregistrements
Maintenant que les enregistrements restreints sont stockés dans la mémoire de l'instance, nous devons les parcourir pour ajouter chaque donnée tour à tour, dans la liste déroulante dédiée. Pour cela, nous allons déclencher une boucle Do.

• Toujours à la suite du code VBA Access, ajouter les instructions suivantes :

...
enr.MoveFirst
Do
controle.AddItem enr.Fields(champ).Value
enr.MoveNext
Loop While Not enr.EOF
...

Grâce à la méthode héritée MoveFirst de l'objet enr, nous plaçons tout d'abord le pointeur de lecture sur le premier des enregistrements sélectionnés. Puis, nous engageons la boucle (Do) pour les parcourir tous tant que le dernier n'est pas atteint (While Not enr.EOF). A chaque passage, nous ajoutons (AddItem) l'information du champ passé en second paramètre de la procédure, dans la liste déroulante (controle) passée en premier paramètre de cette même procédure. Bien sûr et ensuite, nous n'oublions pas de déplacer le pointeur sur l'enregistrement suivant (MoveNext) pour poursuivre la récolte.

Fermer les objets de programmation
Pour finir proprement et comme à l'accoutumée, nous devons fermer les objets de base de données, dès lors qu'ils ne sont plus utilisés, soit à la toute fin du code, une fois les traitements achevés.

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

...
enr.Close
autreBdd.Quit
Set autreBdd = Nothing
Set enr = Nothing
...

Nous fermons les objets grâce aux méthodes respectives Close et Quit. Puis, nous les détruisons en les réinitialisant (Set) à Nothing pour les sortir de la mémoire.

Déclencher les requêtes
A ce stade, notre procédure est isolée. En l'absence d'appel extérieur, elle est inopérante. Nous devons la déclencher au chargement du formulaire Access mais aussi au changement de valeur dans les deux premières listes déroulantes.

• Dans la procédure Form_Load, ajouter l'appel suivant :

Private Sub Form_Load()
dep.RowSource = "": activites.RowSource = "":villes.RowSource = ""
charger_liste dep,"societes_departement"
End Sub

Nous déclenchons ainsi la requête sélection pour charger la première liste déroulante des départements sans doublons.

• Dans la procédure dep_change, ajouter l'appel suivant :

Private Sub dep_Change()
activites.RowSource = "": villes.RowSource = ""
activites.Value = "": villes.Value = ""
charger_liste activites,"societes_activite"
End Sub

Nous déclenchons ainsi la requête permettant de charger la deuxième liste déroulante des activités de sorties recensées dans le département choisi avec la première liste déroulante.

• Dans la procédure activites_change, ajouter l'appel suivant :

Private Sub activites_Change()
villes.RowSource = "": villes.Value = ""
charger_liste villes,"societes_ville"
End Sub

De cette manière, nous engageons la requête permettant de remplir la troisième liste déroulante des villes distinctes correspondant à l'activité choisie dans le département sélectionné en premier lieu.

Il est temps de tester ce code VBA.

• Enregistrer les modifications (CTRL + S) et basculer sur le formulaire (ALT + Tab),
• Puis, l'exécuter en enfonçant par exemple la touche F5 du clavier,
• Dès lors, déployer la première liste déroulante,

Comme vous pouvez l'apprécier, elle est effectivement remplie des départements uniques issus de la base de données externe.

• Choisir un département puis, déployer la deuxième liste déroulante,

Cette fois, ce sont uniquement les activités de sorties uniques, a priori pour ce département, qui sont proposées.

• Choisir une activité puis, déployer la troisième et dernière liste déroulante,

Nous obtenons bien la liste des villes uniques proposant l'activité sélectionnée auparavant pour le département choisi en amont.



Nous y verrons encore plus clair à l'issue du volet suivant quand nous serons parvenus à extraire, dans le sous-formulaire, les informations filtrées par ces trois listes déroulantes recoupées.