Pierre Fauconnier

VBA Excel - Access : Interactions entre Excel et Access avec ADODB : Fonctions génériques (1)

par

, 25/08/2019 à 08h08 (11725 Affichages)

Salut.

Quelques questions sur le forum m'ont amené à rédiger quatre billets sur la question de l'échange d'informations entre Excel et une base Access. Afin de ne pas réinventer toute la roue à chaque fois, je propose ici un "framework" constitué de quelques fonctions rudimentaires permettant les manipulations CRUD sur une base Access au départ d'Excel, autour duquel nous allons greffer les interactions avec un userform.

Le premier billet détaillera le framework. Ces quelques fonctions constitueront le socle d'une DAL (Data Access Layer) dans une architecture "trois-tiers" et permettront les manipulations CRUD sur la base.

À côté de ces fonctions génériques, nous trouverons une DAL spécifique pour gérer les données d'une table en particulier. Je développerai ces fonctions et procédures dans le 2^e billet, qui exposera un cas pratique de gestion de contacts.

Le troisième billet montrera comment alimenter un userform pour mettre à disposition de l'utilisateur les manipulations CRUD.

Un quatrième billet illustrera comment se passer du SQL dans le code VBA, grâce aux procédures stockées et/ou vues, déportées sur le SGBD (ici, Access).

Très modestement, j'espère aussi illustrer ici quelques bonnes pratiques de programmation, à observer, quel que soit le langage.

Mise en garde

Il convient en préambule 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.

CRUD?

CRUD est l'acronyme de Create, Read, Update, Delete, les quatre actions de base que l'on réalise lors de l'exploitation de données, et qui correspondent aux requêtes SQL de manipulation des données (La requête de sélection et les trois requêtes d'action d'Access):

Create: Insert into... ;
Read: Select ... from... ;
Update: Update ... Set.. .;
Delete: Delete from...

Utilisation de ADODB

Pour faciliter la saisie du code, je travaille ici en early binding, ce qui signifie que je référence la bibliothèque ADODB. Ici, j'utilise la 2.8.

Objets ADODB manipulés

Nous allons travailler avec quatre objets ADODB :

Connection : cet objet nous connecte à la DB et nécessite de connaître la chaine de connexion ;
Command : cet objet agit sur les données (CRUD) par le biais d'une requête SQL, éventuellement paramétrée ;
Recordset : cet objet contient le jeu d'enregistrements retourné par une requête Select. Il ne peut être manipulé qu'avec une connexion ouverte ;
Parameter : cet objet matérialise un paramètre pour une requête paramétrée^*.

^* J'ai choisi de travailler systématiquement avec des requêtes paramétrées. En effet, je n'aime pas recomposer la requête à coup de concaténation, car cela pose des problèmes pour les dates, les valeurs décimales, les textes contenant certains caractères, dont l'apostrophe, etc. Je ne travaille pas non plus sur l'objet Recordset pour les requêtes d'action, préférant les commandes SQL.

Data Access Layer (DAL) - Framework minimaliste, mais parfaitement fonctionnel

Les fonctions et procédures présentées ci-dessous sont regroupées dans un module standard que j'ai nommé DAL...

Chaine de connexion
Pour nous connecter à la DB, nous avons besoin d'une chaine de connexion qui passe toute une série de paramètres lors de l'ouverture de la connexion. Ce billet n'a pas pour but de traiter les connexions de manière exhaustive. Basiquement, une connexion à une base Access est possible avec une chaine de caractères précisant le provider et le chemin d'accès de la base Access : "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=D:\Documents\Database19.accdb".

Pour simplifier le billet, je récupère cette chaine au travers d'une fonction qui la contient en "hard coding". Il conviendra de modifier le corps de cette fonction pour récupérer le chemin de la base non hard-codé (par exemple au travers d'une "plage nommée") pour recomposer la chaine de connexion complète. L'utilisation d'une fonction plutôt que d'une constante String permettra cette évolution sans devoir toucher à autre chose dans le code.
Code vba : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
Private Function getConnectionString() As String
  getConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=D:\Documents\Database19.accdb"
End Function
Fonction permettant de lire des données de la base
Cette fonction renvoie un array contenant les données récupérées et requiert:

un argument obligatoire qui consiste en la commande SQL Select... ;
un argument optionnel constitué de la collection des paramètres nécessaires à l'exécution de la requête. Ces paramètres devront être de type ADODB. Pour faciliter la création de ces paramètres, la DAL générique exposera une fonction GetParameter. Nous la verrons plus loin.

Le recordset récupéré par la commande SQL Select ne pouvant être exploité qu'avec une connexion ouverte, il est intéressant de transférer les données du recordset dans un array, ce qui permettra de fermer la connexion puis d'exploiter les données contenues dans le tableau. Cela peut se réaliser facilement avec la méthode GetRows d'un objet ADODB.Recordset. Le problème est que le tableau récupéré est "mal formé". Pour expliquer cela par un exemple, je vais imaginer une requête Select récupérant 5 enregistrements de 3 champs. Recordset.GetRows va renvoyer un tableau de 3 lignes et 5 colonnes, soit un tableau permuté. Pour une exploitation aisée dans Excel, ce tableau sera permuté grâce à une fonction Transpose.
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
Function getRows(Command As String, Optional Parameters As Collection)
  Dim cn As New ADODB.Connection
  Dim cm As New ADODB.Command
  Dim rs As ADODB.Recordset
  Dim pm As ADODB.Parameter
 
  cn.Open getConnectionString()
  cm.ActiveConnection = cn
  cm.CommandText = Command
  If Not Parameters Is Nothing Then
    For Each pm In Parameters
      cm.Parameters.Append pm
    Next
  End If
  Set rs = cm.Execute()
  If Not rs.EOF Then getRows = Transpose(rs.getRows)
  rs.Close
  cn.Close
End Function
Afin que la transposition du tableau soit générique, j'ai préféré créer une fonction privée de transposition plutôt que d'utiliser les fonctions d'Excel. Cela permet que le framework soit utilisé par Word, PowerPoint...
Code VBA : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
Private Function Transpose(Source)
  Dim i As Long, j As Long
  ReDim target(0 To UBound(Source, 2), 0 To UBound(Source))
 
  For i = 0 To UBound(target)
    For j = 0 To UBound(target, 2)
      target(i, j) = Source(j, i)
    Next j
  Next i
  Transpose = target
End Function
Fonction permettant d'exécuter une requête Action sur la base Access

Les actions CUD (Create, Update, Delete) ne sont pas différenciées au niveau de la base. Ce sont des requêtes "Action". Elles seront exécutées par une même procédure et ne retournent pas de valeur. Elles recevront en arguments :

la commande SQL (obligatoire) ;
la collection des arguments (optionnel) ;
un flag (optionnel) permettant de déterminer si la requête est une requête d'ajout ou de mise à jour. On aurait pu déterminer cela par une lecture du début de la connexion, mais j'ai préféré le préciser de manière explicite.
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 Execute(Command As String, Optional Parameters As Collection, Optional CreateCommand As Boolean) As Long
  Dim cn As New ADODB.Connection
  Dim cm As New ADODB.Command
  Dim pm As ADODB.Parameter
 
  cn.Open getConnectionString()
  cm.ActiveConnection = cn
  If Not Parameters Is Nothing Then
    cm.CommandText = Command
    For Each pm In Parameters
      cm.Parameters.Append pm
    Next
  End If
  cm.Execute
  If CreateCommand Then Execute = cn.Execute("select @@identity").Fields(0)
  cn.Close
End Function
Cette procédure permet les commandes SQL Create, Update et Delete.

Gestion des paramètres de la commande SQL

J'ai dit que je préférais travailler avec des paramètres. Toujours dans le cadre du framework, dont le but est de présenter une couche d'abstraction pour le programmeur qui doit manipuler les interactions avec une DB Access, la dal propose une fonction qui permet de créer un paramètre ADODB. Ainsi, l'utilisateur de la DAL n'a besoin d'aucune notion ADODB pour fonctionner.
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
Function getParameter(Name As String, _
    ParamType As ADODB.DataTypeEnum, _
    Direction As ADODB.ParameterDirectionEnum, _
    size As Long, _
    Value) As ADODB.Parameter
 
  Set getParameter = New ADODB.Parameter
  With getParameter
    .Name = Name
    .Type = ParamType
    .Direction = Direction
    .size = size
    .Value = Value
  End With
End Function
Requête scalaire

En bonus, la DAL expose une fonction qui renvoie une seule valeur, utile par exemple lorsque la requête SQL sert à dénombrer, à récupérer un ID ou autre... SELECT renvoie toujours un recordset, quitte à ce qu'il ne contienne qu'une ligne d'une seule colonne. Cette fonction permet à l'utilisateur de la DAL de ne pas devoir, pour chaque requête scalaire, extraire lui-même la valeur du tableau.
Code VBA : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
Function getValue(Command As String, Optional Parameters As Collection)
  getValue = getRows(Command, Parameters)(0, 0)
End Function
Elle est ici donnée en exemple pour montrer que la DAL peut évoluer pour simplifier la vie de son utilisateur. Je code assez souvent des fonctions de ce type, d'une ligne ou deux, pour simplifier et surtout systématiser mon approche du code.

Conclusions de ce billet

Ce billet, un peu théorique, expose les outils principaux qui permettent le dialogue avec une base Access. Toutefois, il est important de noter que la DAL peut aussi bien fonctionner avec du SQL Server, par exemple, comme avec n'importe quelle autre base de données prise en charge par ADODB, sans qu'il soit besoin de modifier quoi que ce soit à la DAL. C'est la couche ADODB, grâce à la chaine de connexion et au provider renseigné, qui se charge d'adapter son fonctionnement selon la base à laquelle elle se connecte.

Je n'ai pas parlé ici des aspects de la sécurité ni des accès concurrents, etc. L'idée est de montrer qu'il est finalement assez simple de manipuler une base de données au travers d'outils que l'on peut rendre génériques.

Le deuxième billet sur le sujet va vous expliquer comment manipuler des données de la DB grâce à cette DAL générique.