Pierre Fauconnier

VBA Excel - Access : Interactions entre Excel et Access avec ADODB : Manipulation des enregistrements (2)

par

, 25/08/2019 à 09h22 (4020 Affichages)

Dans le premier billet traitant des échanges Excel-Access par ADODB, j'ai développé un framework générique qui permet de réaliser les quatre actions CRUD sur une base de données (Create, Read, Update, Delete).

Après ce billet théorique, je passe à la pratique avec la création d'une DAL spécifique pour les manipulations des données d'une table. Dans une architecture "trois-tiers", ce module standard spécifique fait partie de la couche d'accès aux données. C'est au travers de ce module spécifique que la DAL générique sera utilisée.

Base de données utilisée

Pour l'exemple, j'ai créé une base de données dans laquelle se trouve une seule table. En effet, pour mettre en place les mécanismes d'échange ADODB, nul besoin de requêtes bien complexes, de jointures ou autres. Le code SQL à générer ne viendrait que compliquer la démarche, mais ce qui est illustré ici fonctionne aussi bien avec des requêtes complexes.

Table Contact

Une seule table, donc, la table des contacts:

ContactPK : clé primaire autoincrémentée, donc de type Long ;
FirstName : Prénom du contact, de type "Texte court" en Access ;
LastName : Nom du contact, de type "Texte court" ;
BirthDate : Date de naissance du contact, de type "Date/Heure" ;
Amount : Montant, de type Réel double ;
Active : Contact actif ou pas, de type booléen.

Avec ces données, nous manipulerons les types les plus souvent utilisés.

Dal spécifique pour la gestion des contacts

Tout comme j'ai nommé la dal générique DAL, chaque dal spécifique sera elle aussi nommée. Pour gérer les contacts, je place donc tous les codes qui vont suivre dans un module standard nommé DalContact.

Structure Contact

Pour faciliter la manipulation des données d'un contact, j'ai créé une structure (Type... End Type). On pourrait s'en passer, ou utiliser un tableau (une structure est en fait un tableau), un objet personnalisé. Ce n'est pas important ici, le principal étant de voir la simplicité avec laquelle on manipule les données de la base si l'on prend la peine de systématiser son approche.
Code VBA : Sélectionner tout - Visualiser dans une fenêtre à part
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
Conseil : Peuplez votre table de quelques données avant de continuer la lecture du billet.

Récupération de toutes les données

La magie de l'abstraction va commencer à opérer ici. Bien sûr, vous devez connaître la syntaxe SQL et la structure de la table pour créer votre requête^*.

Si l'on veut récupérer tous les contacts de la table Contact, on doit utiliser une requête Select * from Contact. Personnellement, je préfère nommer tous les champs, ce qui me permet de savoir comment le tableau de retour sera constitué.

Le contrat de la DAL, c'est d'exposer un tableau (Array) constitué en lignes des enregistrements récupérés et en colonne les "champs" de la requête. La DAL générique développée dans le premier billet expose à cette fin la fonction GetRows(sql, [Parameters]. Dès lors, récupérer les données de tous les contacts revient à créer une fonction qui appelle simplement la dal générique en lui passant la requête SQL.
Code VBA : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
Function getRowsForAllItems()
  getRowsForAllItems = Dal.getRows("select contactpk, firstname, lastname, birthdate, amount, active from contact")
End Function
On remarque ici qu'aucune notion ADODB n'a été manipulée. Tout ce qui concerne ADODB est dans la DAL spécifique et uniquement Là. C'est en cela que réside l'abstraction.

En plaçant un point d'arrêt sur End Function, on peut remarquer la variable locale constituée du tableau des enregistrements récupérés.

Conseil : N'hésitez pas à réaliser toute l'opération en pas à pas (F8), de manière à bien voir le déroulement du code.

^* 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. Je parle de cette technique dans le quatrième billet, qui explique comment déporter le sql du côté de la base et s'en passer totalement dans le code VBA...

Récupérer les données d'un contact dans une structure

Pour récupérer les données d'un contact dans la structure vue plus haut, il suffit de connaître la syntaxe sql requise. Ici, nous avons besoin d'un paramètre (la clé primaire), et à nouveau, la DAL générique va nous aider à manipuler cela sans avoir besoin de savoir que nous faisons de l'ADODB. Ce paramètre requis étant la clé primaire, il s'agit donc d'un entier long (4 octets) qu'il faudra renseigner comme paramètre.

La syntaxe SQL pour Access nécessaire pour la récupération du contact est select contactpk, firstname, lastname, birthdate, amount, active from contact where contactpk = ?. Ici, le paramètre est représenté par le signe ?. Il convient donc d'ajouter un paramètre à la commande, ce qui est prévu par la DAL générique qui peut recevoir une collection d'objets ADODB.Parameters.
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
15
16
17
Function getItem(ID As Long) As Contact
  Dim Record
  Dim Command As String
  Dim Parameters As New Collection
 
  Command = "select contactpk, firstname, lastname, birthdate, amount, active from contact where contactpk = ?"
  Parameters.Add Dal.getParameter("P1", adInteger, adParamInput, 4, ID)
  Record = Dal.getRows(Command, Parameters)
  With getItem
    .ID = Record(0, 0)
    .FirstName = Record(0, 1)
    .LastName = Record(0, 2)
    .BirthDate = Record(0, 3)
    .Amount = Record(0, 4)
    .Active = Record(0, 5)
  End With
End Function
Je pense que le code parle de lui-même et se passe de beaucoup d'explications. Ici aussi, on remarque à nouveau qu'aucune notion ADODB n'est pilotée. Les notions liées au paramètre (Type, direction, longueur) ne sont pas à proprement parler des notions ADODB, mais des notions SQL liées à la façon dont les requêtes et procédures stockées paramétrées sont manipulées côté SQL.

L'examen du code en pas à pas permet de voir qu'à la fin de la fonction GetItem, une structure contient bien les infos du contact recherché.

Remarque : il est intéressant de noter ici qu'il y a un contrat avec la DAL. La DAL renvoie un tableau formaté d'une certaine manière. Si l'on change de SGBD, la DAL doit continuer à respecter le contrat d'exposer un tableau formaté comme attendu. Le respect de ces contrats est à la base même de la programmation sous architecture "trois-tiers" où les modifications internes d'une couche ne peuvent pas impacter les autres couches de l'application développée. En bémol à cette volonté irréaliste, on notera que, les SGBD ne respectant pas scrupuleusement les normes SQL en vigueur, il y a fort à parier qu'il faudra adapter le texte des requêtes SQL en fonction du SGDB utilisé.

Suppression d'un contact

Pour supprimer un contact, il suffit d'une requête SQL de suppression delete from Contact where contactpk = ?. Ici, à nouveau, pas de manipulations ADODB.
Code VBA : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
Sub Delete(ID As Long)
  Dim Command As String
  Dim Parameters As New Collection
 
  Command = "delete from Contact where contactpk = ?"
  Parameters.Add Dal.getParameter("P1", adInteger, adParamInput, 4, ID)
  Dal.Execute Command, Parameters
End Sub
Création d'un contact dans la DB

Grâce à la DAL générique, nous pouvons créer un contact sans notions ADODB en utilisant la procédure de la DAL générique que nous avons déjà utilisée pour la suppression.

Ici, nous allons devoir passer 5 paramètres, à savoir les variables de la structure Contact. Comme c'est la DB qui attribue la clé primaire, nous ne pouvons pas la passer à la DAL. Par contre, il sera utile de la récupérer, ce que permet la DAL générique grâce à l'argument optionnel CreateCommand et au fait que Execute renvoie un entier long.
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 = "insert into contact (firstname, lastname, birthdate, amount, active) values(?,?,?,?,?)"
  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
Attention : Access ne gère pas les paramètres nommés, de sorte qu'il est impératif que les paramètres soient créés dans l'ordre des ? mentionnés dans la requête SQL.

Note : Si vous êtes observateur, vous aurez vu que cette fonction est Private. En effet, j'ai choisi de passer par une fonction publique Save qui décidera s'il s'agit de création ou de modification.

Modification des données d'un contact

Pour modifier les données d'un contact, nous avons besoin d'une requête [c/]Update...[/c]. Ici, nous utilisons la clé primaire. Notez que la syntaxe SQL impose qu'elle soit le 6^e argument (6^e signe ?), et donc, dans l'ajout des paramètres à la commande, ce paramètre est ajouté en dernier.
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
15
Private Function Update(item As Contact)
  Dim Command As String
  Dim Parameters As New Collection
 
  Command = "update contact set firstname = ?, lastname = ?, birthdate = ?, amount = ?, active = ? where contactpk = ?"
  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)
    Parameters.Add Dal.getParameter("P6", adInteger, adParamInput, 255, .ID)
  End With
  Dal.Execute Command, Parameters
End Function
Ici aussi, la fonction est Private

Create ou Update?

Afin de permettre d'enregistrer un contact sans se soucier de le créer ou de le modifier, je manipule ces deux fonctions privées au travers d'une fonction publique Save
Code VBA : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
Function Save(item As Contact) As Long
  If item.ID = 0 Then
    Save = Create(item)
  Else
    Update item
  End If
End Function
Ce n'est pas obligatoire, bien sûr, mais je pratique ainsi depuis longtemps... Il est intéressant de noter ici que l'ajout (Create), la modification (Update) et la suppression (Delete) utilisent la même fonction de la DAL générique. On gagne du code par une capitalisation élevée du code existant.

Et si on doit manipuler les données d'une autre table?
Si je dois manipuler par exemple des données liées à une localité, je vais créer une structure ou un objet City et une dal spécifique DalCity. Se créer une dal spécifique modèle peut aider dans ce cas. Et je manipulerai les données de la table City au travers de cette dal spécifique.
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
15
16
17
18
19
20
21
22
23
24
25
26
27
28
Option Explicit
 
Type City
  ID As Long
  CityName As String
  ZipCode As String
  CountryID As Long
End Type
 
Function getRowsForAllItems()
  getRowsForAllItems = Dal.getRows("select citypk, CityName, ZipCode, CountryFK from city")
End Function
 
Function getItem(ID As Long) As City
  Dim Record
  Dim Command As String
  Dim Parameters As New Collection
 
  Command = "select Citypk, CityName, ZipCode, CountryFK from City where CityPk = ?"
  Parameters.Add Dal.getParameter("P1", adInteger, adParamInput, 4, ID)
  Record = Dal.getRows(Command, Parameters)
  With getItem
    .ID = Record(0, 0)
    .CityName = Record(0, 1)
    .ZipCode = Record(0, 2)
    .CountryID = Record(0, 3)
  End With
End Function
On remarque ici que j'utilise les mêmes noms de fonctions que dans DalContact. Là aussi, il s'agit de systématiser son approche. Dès lors, il ne faut pas oublier de préfixer les procédures et fonctions du nom de la dal spécifique que l'on doit manipuler.

En testant le code vu plus haut qui a été créé en quelques minutes, on obtient bien les données des localités.

Conclusions

La DAL spécifique permet d'isoler les procédure et fonctions qui manipulent une table au sein de la base. Ce sont bien entendu les besoins métier qui vous feront développer d'autres fonctions au sein du module pour manipuler les données de la table de façon plus spécifique en fonction de vos besoins.

La DAL générique est le seul module qui contient du code manipulant explicitement des notions ADODB. Au sein de toute votre application, c'est uniquement dans ce module que vous devrez manipuler ADODB. C'est en cela que c'est une "couche d'abstraction", car elle permet au développeur de créer son code en faisant abstraction de ces notions ADODB.

Systématiser cette approche dans vos développements vous fera gagner énormément de temps.

Dans le troisième billet, j'expose la manipulation de ces données au travers d'un userform.