Voir le flux RSS

Pierre Fauconnier

VBA Excel - Access : Interactions entre Excel et Access avec ADODB : Manipuler vues et procédures stockées (4)

Note : 2 votes pour une moyenne de 4,50.
par , 26/08/2019 à 12h28 (929 Affichages)
Salut.

Dans les trois premiers billets, je vous exposais :
  1. Les fonctions génériques pour l'accès aux données via ADODB ;
  2. Les fonctions spécifiques pour la gestion d'une table via ADODB ;
  3. Les interactions entre un userform VBA et Access.



Dans le deuxième billet, j'écrivais ceci:
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.
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.

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 :

Nom : 2019-08-26_083159.png
Affichages : 494
Taille : 6,0 Ko

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 :
Nom : 2019-08-25_095628.png
Affichages : 491
Taille : 7,2 Ko

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

Nom : 2019-08-26_084527.png
Affichages : 492
Taille : 12,5 Ko

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 !)

Nom : 2019-08-26_093418.png
Affichages : 475
Taille : 32,7 Ko

Nom : 2019-08-26_112407.png
Affichages : 479
Taille : 11,2 Ko

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".

Nom : 2019-08-26_102040.png
Affichages : 486
Taille : 16,9 Ko

Nom : 2019-08-26_102100.png
Affichages : 483
Taille : 23,9 Ko

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

Nom : 2019-08-26_102344.png
Affichages : 475
Taille : 45,2 Ko

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
Nom : 2019-08-26_105510.png
Affichages : 483
Taille : 15,6 Ko

Je peux alors modifier la fonction GetItem de ma DalContact

Nom : 2019-08-26_105808.png
Affichages : 485
Taille : 34,1 Ko



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.

Nom : 2019-08-26_111118.png
Affichages : 484
Taille : 13,5 Ko

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... 

Envoyer le billet « VBA Excel - Access : Interactions entre Excel et Access avec ADODB : Manipuler vues et procédures stockées (4) » dans le blog Viadeo Envoyer le billet « VBA Excel - Access : Interactions entre Excel et Access avec ADODB : Manipuler vues et procédures stockées (4) » dans le blog Twitter Envoyer le billet « VBA Excel - Access : Interactions entre Excel et Access avec ADODB : Manipuler vues et procédures stockées (4) » dans le blog Google Envoyer le billet « VBA Excel - Access : Interactions entre Excel et Access avec ADODB : Manipuler vues et procédures stockées (4) » dans le blog Facebook Envoyer le billet « VBA Excel - Access : Interactions entre Excel et Access avec ADODB : Manipuler vues et procédures stockées (4) » dans le blog Digg Envoyer le billet « VBA Excel - Access : Interactions entre Excel et Access avec ADODB : Manipuler vues et procédures stockées (4) » dans le blog Delicious Envoyer le billet « VBA Excel - Access : Interactions entre Excel et Access avec ADODB : Manipuler vues et procédures stockées (4) » dans le blog MySpace Envoyer le billet « VBA Excel - Access : Interactions entre Excel et Access avec ADODB : Manipuler vues et procédures stockées (4) » dans le blog Yahoo

Commentaires

  1. Avatar de TSAFACK-M
    • |
    • permalink
    Salut.

    Merci pour ce Billet, c'est assez instructif.

    Je pense que même si Excel n'est à la base pas conçu pour faire ce type de travail, cela reste malgré tout une meilleure approche lorsqu'on veut réaliser des Applications CRUD. D'ailleurs, pour ceux qui ont déjà conçu une Application VBA en utilisant Excel comme base de donnée savent combien cela peut être chiant car si l'Application plante, bonjour les dégâts avec la perte des données ou bien si un fichier Excel est utilisé comme Base Dorsale, les actions y sont limités...

    Personnellement, je conçois toutes mes applications CRUD en utilisant Excel comme un pilote pour manipuler la Base de donnée que ce soit Access ou SQLServer; cela est très pratique et plus professionnel. De la sorte, je peux faire des mises à jour du code dans une copie du fichier sans impacter le travail des utilisateurs puisque le fichier contenant le code est bien séparé de la base de donnée. Et pour améliorer les choses, je peux créer un Ruban avec du XML pour customiser complètement l'espace de travail des utilisateurs.

    Les possibilités sont infinies avec cette approche conceptuelle de la programmation interactive en VBA.

    Cordialement
  2. Avatar de WuKoDLaK
    • |
    • permalink
    [supprimé et édité dans le fil du forum par commodité]
    Mis à jour 28/09/2019 à 18h13 par WuKoDLaK
  3. Avatar de Guillaume.Chevallier
    • |
    • permalink
    Bonsoir,

    ces 4 billets sont très intéressants et enrichissants. Je suis justement en train de créer une application du même type. Avec deux buts, séparer les données et l'application et que plusieurs personnes puissent travailler dessus.

    Je vais essayer d'améliorer mon application en m'inspirant de ces billets. Notamment les requêtes paramétrées et les requêtes dans access afin de ne pas écrire des requêtes sql concaténées.

    Je voulais savoir si vous gériez la validation des données provenant des formulaires. Si les données ne sont pas valides, renvoyer le formulaire rempli et indicateurs d'erreurs. (peut-être un prochain billet )

    cordialement,

    Guillaume.
  4. Avatar de Pierre Fauconnier
    • |
    • permalink
    Bonsoir Guillaume,

    Enchanté que ces billets vous plaisent et vous aident à développer votre solution ,)

    Qu'entendez-vous par "validation des formulaires"?

    Pour moi, il y a deux types de validation, la validation technique et la validation métier.

    Par validation technique, j'entends le fait de vérifier que si l'on attend une date comme saisie dans un contrôle, ce soit bien une date qui a été saisie. Par validation métier, j'entends le fait que si l'on attend une date comprise dans une période donnée, ce soit bien une date de cette période qui ait été saisie. Je détaille une façon de réaliser cela dans ce billet.

    Pour Access, le problème réside dans le fait que c'est une application tierce à Excel qui va éventuellement rejeter l'erreur. Comme exemples, je citerai évidemment
    • l'intégrité référentielle qui, en dernier ressort, est vérifiée par Access;
    • les macros de données qui pourraient rejeter telle ou telle valeur;
    • les contraintes de validité qui interdiraient un Null;
    • ...


    J'exclus, temporairement du moins, les erreurs dues à des problèmes de type de données, car le passage par des classes personnalisées comme montré dans les billets réduisent normalement le risque d'erreur. On pourrait toutefois gérer les erreurs à ce niveau-là, en préventif comme en curatif.

    Je pense que l'application Excel qui est développée sur base Access doit prévenir autant que possible les problèmes qui pourraient arriver côté Access. Il me semble raisonnable de penser que le développeur Excel est aussi le concepteur de la base Access, de sorte qu'il peut intégrer, soit dans sa BL soit dans sa DAL les règles qui prévalent côté Access.

    Reste que de toute façon, il faut prévoir le rejet par Access, qui se manifestera par la levée d'une exception qui devra être gérée au niveau de la DAL Excel par une valeur de retour des fonctions de la DAL. A charge pour l'appli Excel de gérer ces retours et au programmeur de prévoir que cela remonte jusqu'au userform (de la DAL vers la PL en passant, en trois tiers pur, par la BL). Normalement, les On Error, soit au niveau de la DAL pour capturer l'exception et l'écrire dans un fichier de log, soit en laissant remonter l'erreur jusqu'au userform, sont les instruments qu'il faudra mettre en place pour gérer les rejets d'Access.

    Je vais suivre votre conseil et écrire un cinquième billet ou je modéliserai les trois cas envisagés ici (rejet technique au niveau du formulaire, rejet métier au niveau Excel et rejet par Access géré par les On Error, ainsi que la gestion en préventif ou en curatif au niveau des objets). Je vais essayer d'écrire ce billet dans le début de la semaine prochaine.
  5. Avatar de Guillaume.Chevallier
    • |
    • permalink
    Bonjour,

    Ce matin en relisant le premier billet de la série, j'ai vu tout en bas de page le lien vers le billet "VBA: De la bonne programmation d'un userform".
    Celui-ci répond effectivement à ma question sur la validation des données. La distinction entre validation technique et validation métier est bien compréhensible.

    Je vais d'abord m'attaquer à la validation technique, même si j'essaie au maximum de guider l'utilisateur avec des listes.

    Vos billets sont clairs et concis, ils sont facilement compréhensible. C'est un plaisir de vous lire.


    Guillaume.