VBA Excel - Access : Interactions entre Excel et Access avec ADODB : Manipuler vues et procédures stockées (4)
par
, 26/08/2019 à 11h28 (4595 Affichages)
Salut.
Dans les trois premiers billets, je vous exposais :
- Les fonctions génériques pour l'accès aux données via ADODB ;
- Les fonctions spécifiques pour la gestion d'une table via ADODB ;
- Les interactions entre un userform VBA et Access.
Dans le deuxième billet, j'écrivais ceci:
Jusqu'ici, en effet, les commandes SQL sont créées dans le code VBA, imposant à l'utilisateur d'en connaître la syntaxe et de ne pas commettre d'erreurs de frappe ou autre. Si on peut concevoir qu'il est relativement simple de ne pas se tromper sur select * from contact, le problème réside dans des commandes SQL plus longues comprenant des jointures, des sous-requêtes, des fonctions SQL, etc. Dans ces cas, les procédures stockées pourront nous aider grandement.Avec certains SGBD (système de gestion de base de données), il est possible de faire abstraction de la syntaxe SQL grâce aux procédures stockées, permettant au programmeur "métier" de programmer sans manipuler ADODB et SQL.
Procédures stockées?
Une procédure stockée, ce n'est ni plus ni moins que du code SQL stocké du côté du SGBD, utilisable notamment au travers d'ADODB. Je ne vais pas entrer ici dans le détail de la création d'une procédure stockée (par exemple sur SQL Server), mais proposer une solution EXCEL/ACCESS qui supprime l'obligation de connaître et saisir le sql dans le code VBA, pour le déporter sur la base Access. S'il ne s'agit pas de procédures stockées en tant que telles, les requêtes paramétrées côté Access vont permettre au programmeur métier de se dégager totalement du langage SQL dans son code VBA.
Base de données utilisée
Pour illustrer mon propos, je vais reprendre la base utilisée pour les trois premiers billets et lui ajouter les tables suivantes :
Table des localités :
- CityPK : clé primaire auto-incrémentée=> entier long ;
- CountryFK : clé externe pointant vers les pays => entier long ;
- CityName : nom de la localité => texte court ;
- ZipCode : code postal de la localité => texte court.
Table des pays :
- CountryPK: clé primaire auto-incrémentée => entier long ;
- CountryName : Nom du pays => texte court.
J'ai également modifié la table Contact pour lui ajouter une clé externe vers les localités. Normalement, on placera l'intégrité référentielle sur les tables, mais ces notions sont purement Access et dépassent le cadre de ces billets. Avec l'intégrité référentielle, voici le diagramme des tables :
N. B. Ces fichiers sont disponibles dans cette discussion du forum
Procédure stockée avec fonction
Si l'on reprend l'idée de récupérer les données du contact dans une listbox, on s'aperçoit que les valeurs booléennes sont exprimées par leurs correspondants numériques, ce qui pourrait perturber l'utilisateur. Il serait probablement plus utile d'afficher "Actif" ou "Inactif".
Au départ, nous avions utilisé la commande SQL select contactpk, firstname, lastname, birthdate, amount, active from contact et cela donnait ceci dans le formulaire :
Adaptation de la requête SQL dans le code VBA
Nous allons modifier cette requête pour obtenir les valeurs textuelles plus explicites :
select contactpk, firstname, lastname, birthdate, amount, iif(active,'Actif','Inactif') As Status from contact
Dans la DalContact, nous pouvons créer une fonction qui récupère les données pour la listbox
Code vba : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3 Function getRowsForListBox() getRowsForListBox = Dal.getRows("select contactpk, firstname, lastname, birthdate, amount, iif(active,'Actif','Inactif') As Status from contact") End Function
Bien sûr, la procédure qui appelle, prépare et affiche le userform est adaptée pour appeler cette fonction de la dal, de façon à ce que le résultat dans la listbox affiche bien les valeurs textuelles.
Code vba : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9 Sub ViewConctactUseform() Dim t t = DalContact.getRowsForListBox ' récupération du tableau pour la listbox With usfContacts .lboContacts.List = t .Show End With End Sub
Création de la vue dans Access
À la place de saisir la requête SQL en VBA, il pourrait être intéressant de la fixer une fois pour toutes Dans Access, puis de l'appeler au travers de la couche ADODB.
On peut créer la requête dans Access soit avec le QBE (Query By Example) qui permet de s'abstraire du code SQL, soit directement par SQL, puis l'enregistrer en lui donnant un nom explicite (sans espaces, sans accents svp !)
Utilisation de la requête/vue dans le code VBA
Dans la DalContact, il suffira de remplacer le texte de la requête sql par le nom de la "vue" Access constituée de la requête GetContactsForListBox
Code VBA : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3 Function getRowsForListBox() getRowsForListBox = Dal.getRows("getContactsForListBox") End Function
On constate ainsi que dans notre code VBA, on n'utilise plus la syntaxe SQL. Cela ne dispense bien entendu pas le développeur "métier" d'une maîtrise du langage SQL, mais ces techniques permettent de bien séparer les métiers et les responsabilités. Au développeur côté Access la responsabilité de créer les vues nécessaires à l'application, au développeur "Métier" la responsabilité de savoir utiliser ces vues, éventuellement paramétrées, dans son code VBA. Et entre les deux, un descriptif des vues expliquant l'agencement des colonnes et les paramètres éventuels dont la vue a besoin. En bonus, ce qui est développé en Access peut évidemment être utilisé dans d'autres applis, évitant ainsi la duplication de code.
Vue avec jointure
Cette technique, nous venons de le voir, apporte l'avantage de déporter la "responsabilité" SQL dans le SGBD (ici, Access), mais il apporte également une facilité à la rédaction des requêtes plus complexes, comme celles contenant des jointures.
Si je souhaite récupérer les données d'un contact avec les nom et code postal de sa localité et le pays où il est situé, je vais devoir utiliser les jointures. Ma commande SQL va donc ressembler à ceci :
Code sql : Sélectionner tout - Visualiser dans une fenêtre à part
1
2 SELECT ContactPK, FirstName, LastName, BirthDate, Amount, Active, ZipCode, CityName, CountryName FROM Country RIGHT JOIN (City RIGHT JOIN Contact ON City.CityPk = Contact.CityFK) ON Country.CountryPK = City.CountryFk;
Placer cette commande sql, qui par rapport à certaines que je dois utiliser est encore bien "gentille", dans du code VBA commence déjà à devenir plus complexe. Je vais donc la déporter dans Access et la nommer "GetContactsWithCities".
Dans ma DalContact, je pourrai utiliser cette vue par son nom, ce qui me dispense de la saisie du code SQL au sein de mon développement VBA
Vue paramétrée
Nous utiliserons la vue paramétrée de la même façon qu'une commande sql passée en texte. Il suffira de créer la vue dans Access, puis de l'appeler en VBA en lui passant les paramètres
Création de la vue paramétrée dans Access
Je crée la vue SelectContact dans Access
Je peux alors modifier la fonction GetItem de ma DalContact
Requête action paramétrée
L'utilisation des requêtes Action (Create, Update, Delete) peut aussi être facilitée par la création dans Access de la requête correspondante. Je peux créer la requête de création d'un contact dans Access, et je m'en sers en VBA.
Il faut bien noter ici que, vu qu'il y a plusieurs paramètres, on ne peut pas utiliser les ? dans Access, mais des "noms" différents pour les paramètres :
insert into contact (firstname, lastname, birthdate, amount, active) values(p1, p2, p3, p4, p5). Notez également qu'il est possible, en fonction de la façon dont vous créez votre requête dans Access, que des [] soient ajoutés autour de chaque paramètre.
Pour l'utiliser en VBA, j'adapte le code de CreateItem dans la DalContact
Code vba : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14 Private Function Create(item As Contact) As Long Dim Command As String Dim parameters As New Collection Command = "CreateContact" With item parameters.Add Dal.getParameter("P1", adVarChar, adParamInput, 255, .FirstName) parameters.Add Dal.getParameter("P2", adVarChar, adParamInput, 255, .LastName) parameters.Add Dal.getParameter("P3", adDate, adParamInput, 255, .BirthDate) parameters.Add Dal.getParameter("P4", adDouble, adParamInput, 255, .Amount) parameters.Add Dal.getParameter("P5", adBoolean, adParamInput, 255, .Active) End With Create = Dal.Execute(Command, parameters, True) End Function
Conclusions
Il est intéressant de noter ici que cette modification dans l'approche (vues et requêtes déportées en Access à la place du code sql dans le VBA) n'a imposé aucune modification de la DAL générique. Seules les dal spécifiques sont affectées par cette nouvelle orientation d'architecture et de développement
Ces techniques (requêtes déportées en Access ou sur le serveur de données, utilisation d'une dal générique, utilisation de dal spécifique...) permettent de compartimenter les métiers (DBA, développeur métier...). Bien entendu, un développeur EXCEL-VBA (et éventuellement Access) connaît les techniques de façon isolée et sera à même d'utiliser des syntaxes sql dans son code VBA.
Pourtant, travailler en suivant les bonnes pratiques exposées dans ces billets de blog vous permettra de créer des applications plus solides, plus maintenables, et de les faire vivre et grandir plus facilement, avec, ce qui n'est pas négligeable, une très nette économie de code.
Le découpage des responsabilités (Access, Couche d'accès aux données ; Data Access Layer ou Dal, couche métier ; Business Layer ou BL, couche de présentation ou PL...) permet également une réutilisation du code plus aisée, notamment pour la DAL, amenant à un gain de temps significatif dans le développement d'applications futures, et donc à un gain de rentabilité non négligeable.
N'hésitez pas à commenter sur cette discussion de façon générale, ou sur l'un ou l'autre billet.
Avant de vous souhaiter bon travail, j'enfonce à nouveau le clou en répétant que "c'est la systématisation de l'approche et de la production de code qui est à la base d'un travail professionnel... et rentable !"
Bon travail avec Excel, VBA, Access...