par , 25/08/2019 à 12h25 (3316 Affichages)
Salut.
Un premier billet exposait la création d'une DAL générique pour faciliter l'accès ADODB à des données stockées dans une DB pilotable par ADODB.
Un second billet proposait d'utiliser cette dal générique comme une "couche d'abstraction" permettant au développeur "métier" de coder son application sans se soucier de la couche et des techniques ADODB, encapsulées dans la dal générique.
Dans ce troisième billet, je vais exposer les manipulations de ces données au travers d'un userform dans une approche tendant à systématiser nos développements informatiques. On ne peut pas parler de "trois-tiers", dans la mesure où le userform (couche de présentation, ou Presentation Layer ou PL) ne peut, dans un "trois-tiers" pur, dialoguer directement avec la DAL. Ce raccourci qui pourrait heurter les puristes est emprunté pour permettre de modéliser rapidement les échanges entre un useform VBA et une base de données Access.
Les manipulations des données au travers d'un useform sont expliquées dans ce billet, et je m'appuie bien entendu sur cette systématisation de la manipulation d'un useform dans la suite de ce tuto.
Notez que, vu les DAL spécifiques développées dans le deuxième billet, il n'y aura bien entendu AUCUNE notion liée à la base de données au sein du userform, celui-ci se contentant de manipuler les dal spécifiques.
Côté Presentation Layer (PL), les fonctions de dialogue entre la dal et le formulaire manipuleront les données d'un contact au travers de la structure vue dans le deuxième billet et que je rappelle ici :
1
2
3
4
5
6
7
8
| Type Contact
ID As Long
FirstName As String
LastName As String
BirthDate As Date
Amount As Double
Active As Boolean
End Type |
Rappel:
Le lecteur voudra bien se souvenir que je ne parle ici :
- ni de la gestion des accès concurrents sur une DB ;
- ni de la gestion des erreurs en DB ;
- ni de la validation des données saisies (dates et valeurs numériques notamment) ;
- ni de la gestion des erreurs de manipulation du formulaire ;
- ni de création d'une base Access ou de tables Access par VBA.
C'est volontaire, car cette suite de billets a pour unique but de modéliser les échanges avec Access et n'a pas la prétention d'être un cours exhaustif de programmation d'une "application de gestion des données".
Il convient également de bien comprendre que, normalement, Excel n'est pas prévu pour réaliser ces travaux, qui devraient normalement être dévolus à Access par exemple, ou à un développement en c#, vb.net ou autre. Access permet par exemple de réaliser ce que je développe ici sans aucune ligne de code et en prenant nativement en charge les accès concurrents, la validation de la saisie, etc. Il conviendra donc, avant de s'attaquer à "réinventer la roue" en VBA/Excel, d'examiner la faisabilité de la mise en place d'une solution naturellement orientée vers la gestion des données.
Constitution du formulaire
Le useform est constitué d'une listbox, de quelques textbox, d'une checkbox et de boutons de commande. Il permettra d'afficher la liste de tous les contacts, d'en choisir un par double-clic pour permettre d'en modifier les données, d'en supprimer un ou de créer un nouveau contact. Bref, d'effectuer les actions CRUD vues dans le premier billet (Create, Read, Update, Delete).
Le formulaire expose les contrôles suivants :
- lboContacts : listbox récupérant le tableau des contacts. Elle expose la première colonne (ID) ce qui n'est pas nécessaire, mais laissé ici à des fins didactiques. En exploitation, cette première colonne sera masquée, mais constituera la BoundColumn, c'est-à-dire celle qui contient la valeur renvoyée par la propriété Value de la listbox ;
- tboID : textbox contenant l'ID du contact (a priori inutile en exploitation, laissé ici pour des raisons didactiques, mais rendu inaccessible par Enabled = False) ;
- tboFirstName, tboLastName, tboBirthDate, tboAmount : textbox qui contiendront les données correspondantes du contact ;
- chkActive : checkbox qui contiendra la valeur correspondante du contact ;
- btnCreate, btnSave, btnDelete, btnClose : boutons dont les noms sont suffisamment explicites.
Manipulations du formulaire
Ouverture du formulaire et affichage de la liste des contacts.
Le code est en lui-même très simple. Il est placé dans un module standard, idéalement nommé PL (Presentation Layer) ou PLContact dans le cadre d'une application plus costaude. Il consiste en une interrogation de la DalContact pour récupérer l'array des contacts, et cet array alimente directement la listbox. Après quoi, le formulaire est affiché.
1
2
3
4
5
6
7
8
9
| Sub ViewConctactUseform()
Dim t
t = DalContact.getRowsForAllItems
With usfContacts
.lboContacts.List = t
.Show
End With
End Sub |
En exploitation, il n'est pas forcément nécessaire que la listbox reprenne toutes les données des contacts. L'ID ainsi que les noms et prénoms pourraient suffire. Dans ce cas, on créera dans la DalContact une fonction qui reprend uniquement ces champs.
On remarque la simplicité du code, qui s'appuie sur la couche d'abstraction constituée par les modules de la DAL. Normalement, on testera que l'on a bien récupéré des données avant d'afficher le formulaire, et si aucune donnée n'est retournée, il conviendra d'afficher un msgbox d'information spécifiant qu'il n'y a pas de données. Ce sont des considérations ergonomiques que je ne détaillerai pas ici, souhaitant rester sur la problématique précise de l'échange des données Excel-Access au travers d'une couche ADODB.
Sélection d'un contact à des fins de modification
Par double-clic sur une ligne de la liste, l'utilisateur récupérera les données du contact sélectionné dans les contrôles prévus à cet effet. Dans le cas où la listbox embarque toutes les colonnes (champs) dont on a besoin, on pourrait récupérer les données par l'appel d'une fonction privée du userform qui lit les données de la ligne active de la listbox:
1
2
3
4
5
6
7
8
9
10
11
12
| Private Sub lboContacts_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
GetContact
End Sub
Private Sub GetContact()
tboID.Value = lboContacts.Column(0, lboContacts.ListIndex)
tboFirstName.Value = lboContacts.Column(1, lboContacts.ListIndex)
tboLastName.Value = lboContacts.Column(2, lboContacts.ListIndex)
tboBirthDate.Value = lboContacts.Column(3, lboContacts.ListIndex)
tboAmount.Value = lboContacts.Column(4, lboContacts.ListIndex)
chkActive.Value = lboContacts.Column(5, lboContacts.ListIndex)
End Sub |
Toutefois, dans le cas où la listbox n'exposera pas toutes les données, il conviendra de charger les contrôles avec les données récupérées de la DalContact qui expose, je le rappelle, la fonction GetItem(ID As Long) As Contact. Voici dès lors comment procéder.
Il faut noter que l'utilisation d'une structure (Type...End Type) ne permet pas de déclarer une variable de type Contact au sein du userform. Il faudrait alors se tourner vers une classe personnalisée Contact, dans le cadre d'un développement plus orienté POO. Ici, je vais rester dans du "procédural" en utilisant une PL spécifique PlContact qui dialoguera avec le userform. C'est donc l'illustration d'une technique possible parmi d'autres, mais qui permet de bien séparer les différentes responsabilités (le useform dialogue avec l'utilisateur d'un côté et la PL spécifique de l'autre, la PL spécifique dialogue avec la Dal spécifique, etc.).
Voici le code de la procédure dans PlContact qui reçoit un ID et remplit les contrôles du userform avec les données récupérées de la dal spécifique.
1
2
3
4
5
6
7
8
9
10
11
12
13
| Sub Load()
Dim Item As Contact
Item = DalContact.getItem(usfContacts.lboContacts.Value)
With Item
usfContacts.tboID.Value = .ID
usfContacts.tboFirstName.Value = .FirstName
usfContacts.tboLastName.Value = .LastName
usfContacts.tboBirthDate.Value = .BirthDate
usfContacts.tboAmount.Value = .Amount
usfContacts.chkActive.Value = .Active
End With
End Sub |
Dès lors, le code dans le userform est réduit à l'appel de cette procédure par le double-clic, et la fonction GetContact du userform n'est plus utile.
1
2
3
| Private Sub lboContacts_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
PlContact.Load
End Sub |
Après le double-clic sur une ligne de la listbox, le formulaire est rempli avec les données du contact sélectionné :
Saisie d'un nouveau contact, modification d'un contact existant
Comme pour la récupération d'un contact, nous travaillerons avec la PlContact pour transférer nos données du formulaire vers la base de données. Mais avant, il faut nettoyer le formulaire pour le préparer à la saisie des nouvelles données, ce que fera la procédure ClearControls du userform, exposé ci-dessous avec les codes des boutons de création et d'enregistrement.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
| Private Sub btnCreate_Click()
ClearControls
End Sub
Private Sub btnSave_Click()
PlContact.Save
End Sub
Private Sub ClearControls()
tboID.Value = ""
tboFirstName.Value = ""
tboLastName.Value = ""
tboBirthDate.Value = ""
tboAmount.Value = ""
chkActive.Value = False
tboFirstName.SetFocus
End Sub |
N. B. L'important ici est de bien comprendre l'architecture mise en place dans le cadre du dialogue Excel-Access. On peut bien entendu améliorer le code fourni avec des boucles et autres, mais ce n'est pas l'objet du présent billet et je me contenterai donc de préparer les contrôles un par un.
Dans le cas d'un nouveau contact, il suffira de saisir les données puis de cliquer sur le bouton Enregistrer. Fidèle à notre architecture, celui-ci appellera une procédure de la PlContact pour le transfert en DB. Le même bouton sera utilisé pour transférer les mises à jour d'un contact existant.
La procédure de la PlContact s'occupant du transfert sera la suivante :
1
2
3
4
5
6
7
8
9
10
11
12
13
14
| Sub Save()
Dim Item As Contact
Dim ID As Long
With Item
If usfContacts.tboID.Value <> "" Then .ID = usfContacts.tboID.Value
.FirstName = usfContacts.tboFirstName.Value
.LastName = usfContacts.tboLastName.Value
.BirthDate = CDate(usfContacts.tboBirthDate.Value)
.Amount = CDbl(usfContacts.tboAmount.Value)
.Active = usfContacts.chkActive.Value
End With
ID = DalContact.Save(Item)
If usfContacts.tboID.Value = "" Then usfContacts.tboID.Value = ID
End Sub |
On remarque deux choses :
- le bouton Enregistrer enregistre la donnée sans que l'utilisateur ait à se préoccuper de savoir s'il crée un contact ou s'il modifie les données d'un contact existant ;
- la procédure Save de la PLContact se charge de créer ou de mettre à jour en DB grâce à en testant la valeur du contrôle tboId. Elle affiche dans le même contrôle la valeur du nouvel ID en cas de création d'un nouvel ID (tboID.Value = "").
On capitalise donc le code en utilisant les mêmes procédures pour la création ou la mise à jour du contact.
Après, il convient probablement d'actualiser la listbox avec les données ajoutées et/ou modifiées, ce que réalise la dernière ligne de la procédure SaveContactFromUserform. Cela peut se faire par une requête en DB qui actualise la propriété LIST du listbox ou par des manipulations de la listbox. Si la récupération des données via la DB peut s'avérer +/- coûteuse, la manipulation du listbox pourra être privilégiée. À chacun ici de choisir ce qui lui convient le mieux. Je ne détaillerai pas ces pratiques ici, car elles sortent un peu du cadre de l'objet du billet.
Ici, j'illustre le userform après ajout de Julie Hénas, et l'on remarque le nouvel ID récupéré dans tboID.
Suppression d'un contact
Pour supprimer un contact, il suffit d'appeler une procédure de la PlContact qui récupérera la valeur de Listbox avant d'appeler la procédure de suppression présente dans la DalContact. Même si cette procédure ne fait qu'une ligne, il convient de la placer dans le module PlContact plutôt que directement sur le bouton du userform. Cela permet de respecter notre architecture et de scinder les responsabilités*.
Voici la procédure de la PlContact qui permet la suppression:
1
2
3
4
| Sub Delete()
DalContact.Delete usfContacts.lboContacts.Value
usfContacts.lboContacts.List = DalContact.getRowsForAllItems
End Sub |
Ce code est appelé par le clic sur le bouton btnDelete
1
2
3
| Private Sub btnDelete_Click()
PlContact.Delete
End Sub |
* Respectez TOUJOURS votre architecture sans aucune exception. L'observation de cette règle est primordiale pour produire du code propre, maintenable et évolutif. La systématisation de votre approche vous permettra en plus de gagner du temps dans vos développements futurs par la réutilisation de code générique ou rapidement adaptable. Au passage, remarquez que les procédures s'appellent Laod, Save et Delete et pas LoadContact, SaveContact et DeleteContact. Cela permet la préparation d'une PL "modèle" qui sera adaptée plus facilement pour la gestion de données en provenance d'autres tables.
Conclusions
Avec ce troisième billet, nous avons approché les interactions entre un userform Excel (ou Word, PowerPoint...) et une base de données (ici, Access). Au travers d'une architecture tendant vers le Trois-Tiers, nous avons vu qu'il était possible de créer du code générique, facilement réutilisable d'une part, respectant une approche professionnelle du code, et qui permet aussi, lorsque la DAL est bien créée, de faire abstraction des techniques ADODB dans tout le reste de l'application. Seule la DAL générique manipule les notions ADODB, permettant au développeur "Métier" de se concentrer sur son boulot.
Un quatrième billet illustre comment se passer de code SQL dans les procédures et fonctions VBA... 
J'espère que ces billets vous aideront dans la suite de vos développements. N'hésitez pas à me faire part de vos commentaires et questions sur les techniques développées ici.
Bons développements
Consulter le profil
Marquer ce blog comme lu
