I. Introduction▲
Effectivement, on peut légitimement se poser la question de l’intérêt de programmer des fonctions complexes pour manipuler les données d’une table Access depuis Excel, alors qu’un simple copier/coller ou une requête générée par l’assistant Access, suffisent à ajouter ou supprimer des données ?
Mais contre toute attente, je me suis aperçu qu’il est parfois bien pratique d’utiliser Excel pour interagir avec Access. Par exemple pour mettre à jour les données d’une base d’après un tableau reçu d’Excel, lorsque ce traitement est récurrent.
Cette documentation est consacrée à l’étude de fonctions génériques qui permettent de manipuler les données Access en utilisant la technologie ADODB.
Elle s’adresse aux développeurs Excel qui souhaitent importer, modifier, ajouter, ou supprimer des données d’une base Access, quel que soit leur niveau, mais des connaissances en Access et en SQL faciliteront sa lecture.
L’ensemble du code a été testé avec Excel 2010 et avec Excel 2016 (en 32 bits).
II. Les fonctions pour manipuler les données Access en utilisant ADODB▲
Nous allons étudier les fonctions du module « ADO » que vous trouverez dans le fichier joint.
Ces fonctions génériques ont pour but de simplifier la vie du programmeur qui n’aura pas à se soucier de l’aspect technique parfois complexe de la manipulation des bases avec la technologie ADODB, ce qui lui fera gagner beaucoup de temps.
Nous n’étudierons pas ici cette technologie en détail (technologie plus riche que DAO, car elle permet d’accéder à d’autres formats de bases, par exemple SQL Server), car vous trouverez en annexe 2 quelques liens de documentations sur le sujet, rédigées par des experts.
Pour présenter ces fonctions, voici en résumé les différentes étapes à suivre pour manipuler des données et (en rouge) les fonctions génériques qui permettent de le faire. Gardez bien à l’esprit ce schéma qui sera le fil conducteur dans la suite de cette documentation :
Trois choses à préciser avant d’étudier ces fonctions et de voir des exemples d’utilisation.
- Si la notion de transaction vous est inconnue, quelques explications sont nécessaires.
Par exemple, lors de la réception d’une commande il faut réaliser deux opérations dans une base de données : passer l’état de la commande à « Reçue » dans la table des commandes ; et incrémenter le stock du produit dans la table des stocks.
Imaginez le cas où une anomalie vient à faire planter l’une de ces deux opérations : le stock n’est plus en adéquation avec les commandes. C’est bien fâcheux.
Pour se prémunir de cela, le principe est d’ouvrir une transaction, de réaliser ces deux opérations (qui restent en attente de validation), puis de fermer la transaction. Le fournisseur de données prend alors la main et ne validera la transaction que si les deux mouvements sont correctement réalisés.
Et bien sûr, en cas d’anomalie sur l’une des opérations, les deux sont automatiquement annulées de façon transparente pour le programmeur.
Dit autrement, vous utiliserez une transaction pour garantir que plusieurs enregistrements sont mis à jour simultanément, comme s’il n’y avait qu’une seule opération.
Et si les tables sont dans des bases différentes ? Aucune importance, le fournisseur de données sait gérer les transactions sur plusieurs bases.
- Les messages d’erreurs sont affichés uniquement après avoir refermé le jeu d’enregistrements, les transactions en cours et les connexions : en effet, vous constaterez qu’une connexion à une base Access génère un fichier « .laccdb » (« .ldb » dans l’ancienne version), soit un fichier de verrouillage qui bloque les autres utilisateurs qui doivent intervenir sur la base. Vous imaginez que ça va être gênant, sur une base multi-utilisateur, de laisser un verrou suite à un message d’erreur dans l’attente que l’utilisateur décide de le valider. La fermeture de ces objets permet de supprimer ce verrou (et donc de libérer la base), mais cela nécessite d’avoir une gestion différée des erreurs.
Dit autrement, les erreurs ne seront pas bloquantes, mais mémorisées et reportées à la fin du traitement pour informer le programmeur.
- Un fournisseur de données, « provider » en anglais, est « un programme qui sait manipuler les données d’une source ».
Pour ouvrir les bases Access, nous utiliserons le fournisseur :
« Microsoft.ACE.OLEDB.12.0 » pour les bases « .accdb » ;
« Microsoft.Jet.OLEDB.4.0 » et pour les bases « .mdb ».
Ces informations sont déclarées dans le module « ADO » du fichier joint :
Private Const Provider_ACCDB As String = "Microsoft.ACE.OLEDB.12.0"
Private Const Provider_MDB As String = "Microsoft.Jet.OLEDB.4.0"et vont alimenter l’objet de connexion « Cnn1 » également déclaré dans ce module :
Public Cnn1 As ADODB.ConnectionObjet qui nécessite l’installation de la bibliothèque ADODB. Soit dans l’éditeur VBA, le menu « Outils / Références… » cochez « Microsoft ActiveX Data Objects 6.1 Library » (ou 2.8 si vous n’avez que cette version).
Comme il est possible de travailler sur plusieurs bases en même temps, nous verrons bientôt pourquoi, il est donc déclaré plusieurs objets connexion, « Cnn1 », « Cnn2 » … « Cnn9 ». Soit neuf connexions simultanées possibles à des bases Access différentes.
Sachant qu’il est peu probable d’utiliser autant de connexions pour un traitement, mais j’ai préféré voir large au cas où.
II-A. Cnn_Initialise▲
La fonction « Cnn_Initialise » permet de définir un fournisseur de données pour initialiser une connexion, c’est-à-dire l’étape 1 du schéma :
Function Cnn_Initialise(Cnn As ADODB.Connection, StrBaseSource As String) As BooleanSes arguments sont :
- Cnn : l’objet connexion concerné (entre Cnn1 et Cnn9) ;
- StrBaseSource : base concernée (chemin complet + nom avec l'extension).
La fonction retourne VRAI si la connexion réussit ou FAUX en cas d’erreur.
Exemple pour déclarer en connexion 1 la base « Exemples.accdb » située dans le répertoire « C:\Formation_VBA » :
Cnn_Initialise Cnn1, "C:\_Formation_VBA\Exemples.accdb"Par curiosité, voyons ce qu’il se cache derrière cette fonction (ici une version simplifiée sans gestion des erreurs) :
' Paramètres de connexion:
Set Cnn = New ADODB.Connection
Cnn.CommandTimeout = 30
Cnn.CursorLocation = adUseServer
' Détermine le fournisseur pour les bases .accdb, ou .mdb:
Select Case UCase(Mid(StrBaseSource, InStrRev(StrBaseSource, ".")))
Case Is = ".ACCDB"
Cnn.Open "Provider= " & Provider_ACCDB & ";" & "Data Source=" & StrBaseSource & ";" _
, "Admin", "", adAsyncConnect
Case Is = ".MDB"
Cnn.Open "Provider= " & Provider_MDB & ";" & "Data Source=" & StrBaseSource & ";" _
, "Admin", "", adAsyncConnect
End Select
' Attend la connexion:
While (Cnn.State = adStateConnecting): DoEvents: Wend
' Retourne VRAI si tout va bien:
If Cnn.State = adStateOpen Then Cnn_Initialise = True
' Désactiver la connexion pour supprimer le verrou:
If Cnn Is Nothing = False Then
If Cnn.State = adStateOpen Then Cnn.Close
End IfCela doit vous sembler curieux d’ouvrir une connexion… pour la refermer aussitôt (Cnn.Close) !
Sauf que, si la connexion est refermée, elle n’est pas supprimée : ses paramètres sont conservés et seront utilisés le moment venu.
- La fermeture de la connexion permet simplement de supprimer le verrou sur la base, les fameux fichiers « .laccdb » ou « .ldb » qui bloquent les autres utilisateurs, ce qu’il faut éviter sur une base multi-utilisateur.
- À ne pas confondre avec la suppression d’une connexion qui se fait avec : Set Cnn = Nothing
Une connexion restant mémorisée tant que l’objet n’est pas supprimé, il est possible de n’utiliser la fonction Cnn_Initialise qu’une seule fois dans votre programme.
À condition de ne pas oublier quelle est la base concernée.
Si vous n’avez plus besoin d’une connexion initialisée, vous pouvez réutiliser un objet connexion (entre Cnn1 et Cnn9) pour initialiser une autre base.
La connexion étant désormais initialisée, nous pouvons passer à l’étape suivante.
II-B. Cnn_Debut▲
La fonction « Cnn_Debut » réinitialise le gestionnaire des erreurs différées et, si demandé, ouvre une transaction, c’est-à-dire les étapes 2 et 3 du schéma :
Function Cnn_Debut(ActionTransaction As EnumActionTransaction) As BooleanSon argument est :
- ActionTransaction : l'une des énumérations suivantes :
SansTransaction = pas de transaction générée,
AvecTransaction = ouvre une transaction (sur toutes les connexions existantes).
La fonction retourne VRAI si tout s’est bien passé ou FAUX en cas d’erreur.
La réinitialisation des erreurs différées est simple, elle consiste à mettre la variable « ADO_Err_Number » à zéro. Cette variable contiendra le numéro d’erreur retournée par « Err.Number » si une erreur est déclenchée pendant le traitement.
Si une transaction doit être générée, il convient de boucler sur les connexions Cnn1 à Cnn9, préalablement initialisées, et de les déclarer avec la méthode de transaction « BeginTrans ».
Avec Cnn un objet « ADODB.Connection » :
' Ouvre la connexion et attend qu’elle soit établie:
If Cnn.State = adStateClosed Then Cnn.Open
If Cnn.State >= adStateConnecting Then
While (Cnn.State = adStateConnecting): DoEvents: Wend
End If
' Lance la transaction de Cnn:
Cnn.BeginTransNous verrons plus loin que la transaction sera validée par « CommitTrans » ou annulée par « RollbackTrans » ;
- BeginTrans : commence une nouvelle transaction ;
- CommitTrans : enregistre les modifications et met fin à la transaction en cours ;
- RollbackTrans : annule toutes les modifications apportées pendant la transaction en cours et met fin à la transaction.
Résumons :
Cnn_Initialise Cnn1, "C:\_Formation_VBA\Exemples.accdb"
Cnn_Debut AvecTransactionLa connexion 1 est initialisée sur la base « Exemples.accdb », la réinitialisation du gestionnaire d’erreurs est faite, la transaction est définie (étapes 1, 2, 3 du schéma) nous pouvons maintenant intervenir sur les tables de cette base, soit pour consulter les enregistrements d’une table, soit pour les afficher, soit pour les modifier, les supprimer, les créer, soit pour exécuter une requête SQL.
II-C. Enr_Info▲
La fonction « Enr_Info » retourne des informations sur l’enregistrement désiré. Elle réalise les étapes 4, 5.1, 6 et 8 du schéma :
Function Enr_Info(Cnn As ADODB.Connection, StrTableSource As String, _
ByVal SQLWhere As String, _
TypeInfoEnr As EnumInfoEnr, _
ByVal StrNomChamp As String) As VariantSes arguments sont :
- Cnn : l’objet connexion concerné (entre Cnn1 et Cnn9) ;
- StrTableSource : la table concernée ;
- SQLWhere : requête d'instruction permettant d'identifier l’enregistrement concerné (sans le mot clé WHERE, voir annexe 1 pour plus d’informations sur les requêtes SQL). Si cet argument est vide alors toute la table est sélectionnée ;
- TypeInfoEnr : l'une des énumérations suivantes :
Valeur = retourne la valeur du champ désiré,
Compte = compte le nombre d'enregistrements (COUNT),
Somme = fait la somme des enregistrements (SUM),
Moyenne = calcule la moyenne des enregistrements (AVG),
Mini = retourne le plus petit enregistrement (MIN),
Maxi = retourne le plus grand enregistrement (MAX),
NbChamps = retourne le nombre de champs dans le jeu d'enregistrements ;
- StrNomChamp : le nom du champ concerné.
La fonction retourne le résultat trouvé ou vide si aucun enregistrement n'est sélectionné ou en cas d'erreur.
Soit la table suivante :
Exemple pour connaître la valeur du champ « Libellé » sur la connexion 1 (voir Cnn_Initialise), dans la table « Table_Direction_1 », pour l’enregistrement où « Direction » vaut « H » :
Enr_Info (Cnn:=Cnn1, StrTableSource:="Table_Directions_1", _
SQLWhere:="Direction='H'", TypeInfoEnr:=Valeur, _
StrNomChamp:="Libellé")Retourne : « HAUT »
Pour connaître, dans cette même table, le maximum du champ « ID » :
Enr_Info(Cnn:=Cnn1, StrTableSource:="Table_Directions_1", _
SQLWhere:="", TypeInfoEnr:=Maxi, StrNomChamp:="ID")Retourne : « 5 ».
Et pour connaître le nombre d’enregistrements :
Enr_Info(Cnn:=Cnn1, StrTableSource:="Table_Directions_1", _
SQLWhere:="", TypeInfoEnr:=Compte, StrNomChamp:="")Retourne : « 4 ».
Cette fonction se base sur une requête SQL pour sélectionner le jeu d’enregistrements d’une table, ouverte en lecture seule. Ci-dessous une version simplifiée pour ceux qui souhaitent en comprendre le principe :
TypeOpé = Array("", "COUNT", "SUM", "AVG", "MIN", "MAX", "") ' <- énumération TypeInfoEnr
' Ouvre la connexion et attend qu’elle soit établie:
If Cnn.State = adStateClosed Then Cnn.Open
If Cnn.State >= adStateConnecting Then
While (Cnn.State = adStateConnecting): DoEvents: Wend
End If
' Définition de la requête SQL:
SQLWhere = "SELECT " & TypeOpé(TypeInfoEnr) & " (" & StrNomChamp & ") " _
& "FROM [" & StrTableSource & "] " & IIf(SQLWhere > "", "WHERE " & SQLWhere, "")
' Ouverture de la table en lecture seule:
Set MonRs = New ADODB.Recordset
MonRs.Open SQLWhere, Cnn, adOpenStatic, adLockReadOnly, adCmdText
' Si un résultat existe:
If MonRs.EOF = False Then Enr_Info = MonRs.Fields(0)
' S'il faut retourner le nombre de champs:
If TypeInfoEnr = NbChamps Then Enr_Info = MonRs.Fields.Count
' Ferme l'enregistrement et désactive la connexion:
MonRs.Close
Cnn.Close
II-D. Enr_Affiche▲
Cette fonction permet d’ouvrir un jeu d’enregistrements et de l’afficher dans une feuille de calcul. Elle réalise les étapes 4, 5.2, 6 et 8 du schéma :
Function Enr_Affiche(Cnn As ADODB.Connection, StrTableSource As String, _
ByVal SQLWhere As String, _
RngDest As Range) As BooleanSes arguments sont :
- Cnn : l’objet connexion concerné (entre Cnn1 et Cnn9) ;
- StrTableSource : la table concernée. Si vide alors l'instruction dans SQLWhere sera utilisée comme requête pour ouvrir la source ;
- SQLWhere : requête d'instruction permettant d'identifier le jeu d’enregistrements concerné (sans le mot clé WHERE, voir annexe 1 pour plus d’informations sur les requêtes SQL). Si cet argument est vide alors toute la table est sélectionnée ;
- RngDest : est la cellule où afficher le jeu d’enregistrements.
La fonction retourne VRAI si au moins un enregistrement est trouvé, ou FAUX dans le cas contraire.
Exemple pour afficher en A10 de la feuille active tous les enregistrements de la table « Table_Direction_1 » :
Enr_Affiche Cnn:=Cnn1, StrTableSource:="Table_Directions_1", _
SQLWhere:="", RngDest:=Range("A10")Dans cet autre exemple, une requête SQL permet de réaliser une jointure entre deux tables et d’afficher les champs désirés, triés par « Libellé ». L’argument « StrTableSource » n’est pas alimenté, car l’instruction SQL contient les informations nécessaires :
Enr_Affiche Cnn1, "", "SELECT Table_1.Libellé, Table_2.Durée " _
& "FROM Table_2 INNER JOIN Table_1 ON Table_2.Id = Table_1.Id " _
& "ORDER BY Table_1.Libellé " _
, Range("A10")Pour les requêtes complexes, pensez à utiliser Access pour simuler ce que vous souhaitez faire, puis passez en mode SQL pour récupérer la requête générée.
Un simple copier/coller vous permettra d’alimenter l’argument « SQLWhere » de la fonction « Enr_Affiche ».
Autre astuce : utilisez les requêtes paramétrées.
Une version simplifiée pour en comprendre le principe :
' Ouvre la connexion et attend qu’elle soit établie:
If Cnn.State = adStateClosed Then Cnn.Open
If Cnn.State >= adStateConnecting Then
While (Cnn.State = adStateConnecting): DoEvents: Wend
End If
' Génère la requête SQL complète (si une table est renseignée sinon prend directement SQLWhere):
If StrTableSource <> "" Then SQLWhere = "SELECT * FROM [" & StrTableSource & "] " & IIf(SQLWhere > "", "WHERE " & SQLWhere, "")
' Ouverture de la table en lecture seule:
Set MonRs = New ADODB.Recordset
MonRs.Open SQLWhere, Cnn, adOpenStatic, adLockReadOnly, adCmdText
' Si un enregistrement existe alors indique que la sélection retourne bien un enregistrement:
' Et se place sur le premier enregistrement (pour lever toute ambiguïté):
If MonRs.EOF = False Then
MonRs.MoveFirst
RngDest.CopyFromRecordset MonRs
Enr_Affiche = True
End If
' Ferme l'enregistrement et désactive la connexion:
MonRs.Close
Cnn.Close
II-E. Enr_MAJ▲
La fonction « Enr_MAJ » permet de modifier, créer ou supprimer un enregistrement, soit les étapes 4, 5.3, 6 et 8 du schéma :
Function Enr_MAJ(Cnn As ADODB.Connection, StrTableSource As String, _
ByVal SQLWhere As String, _
ActionMAJ As EnumActionMAJ, _
ParamArray ChampEtValeur()) As BooleanSes arguments sont :
- Cnn : l’objet connexion concerné (entre Cnn1 et Cnn9) ;
- StrTableSource : la table concernée ;
- SQLWhere : requête d'instruction permettant d'identifier l’enregistrement concerné (sans le mot clé WHERE, voir annexe 1 pour plus d’informations sur les requêtes SQL) ;
- ActionMAJ : l'une des énumérations suivantes :
ModificationUniquement= tente une modification, mais si l'enregistrement n'est pas trouvé alors génère une erreur,
ModificationOuCréation = tente une modification, mais si l'enregistrement n'est pas trouvé alors génère une création,
Suppression = fait une suppression de l'enregistrement (ou du jeu d'enregistrements),
CréationUniquement = création d’un nouvel enregistrement ;
- ChampEtValeur : nom du champ à modifier + Valeur du champ. Ce tableau de type Array peut avoir jusqu'à 30 couples : champ + valeur.
Si le champ est suivi des caractères distinctifs suivants (+=), (-=), (*=), (/=), (&=) alors une opération (addition, soustraction, multiplication, division, concaténation) est réalisée sur le champ et non pas une simple mise à jour.
Cet argument n’est pas utile dans le cas d’une suppression.
La fonction retourne VRAI si un enregistrement est bien mis à jour, créé ou supprimé, ou FAUX dans les autres cas.
Exemple pour modifier l'état d’une commande pour le passer à « Reçue » et ajouter au stock la quantité désirée, et indiquer la date de réception, sur la connexion 1 dans la table « T_Commandes » quand le numéro de commande = NumCommande :
Enr_MAJ Cnn1, "T_Commandes", "Id_Commande=" & NumCommande, _
ModificationUniquement, _
"Etat", "Reçue", _
"Stock(+=)", Quantité, _
"Quand_Recue", NowCette fonction se base sur une requête SQL pour sélectionner le jeu d’enregistrements d’une table, ouverte en modification.
Une version très simplifiée (sans la suppression ou les erreurs) pour mieux comprendre :
' Ouvre la connexion et attend qu'elle soit établie:
If Cnn.State = adStateClosed Then Cnn.Open
If Cnn.State >= adStateConnecting Then
While (Cnn.State = adStateConnecting): DoEvents: Wend
End If
' Définition de la requête complète:
SQLWhere = "SELECT * FROM [" & StrTableSource & "] " & IIf(SQLWhere > "", "WHERE " & SQLWhere, "")
' Défilement bidirectionnel + verrou à la modification:
Set MonRs = New ADODB.Recordset
MonRs.Open SQLWhere, Cnn, adOpenKeyset, adLockPessimistic, adCmdText
' Si pas d’enregistrement de trouvé et mode création alors ajoute un enregistrement,
' si un jeu enregistrements est trouvé alors se place sur le premier enregistrement:
If MonRs.EOF = True Then
If ActionMAJ = ModificationOuCréation Or ActionMAJ = CréationUniquement Then MonRs.AddNew
Else
Select Case ActionMAJ
Case CréationUniquement: MonRs.AddNew
Case Else: MonRs.MoveFirst ' Se place sur 1er enregistrement (pour lever toute ambiguïté).
End Select
End If
' Boucle sur les champs qu'il faut mettre à jour:
For i = 0 To UBound(ChampEtValeur) Step 2
' Si le nom du champ est suivi d'un signe particulier, alors faire une opération:
Select Case Right(ChampEtValeur(i), 4)
Case "(+=)" ' Ajouter
ChampEtValeur(i) = Mid(ChampEtValeur(i), 1, Len(ChampEtValeur(i)) - 4)
MonRs.Fields(ChampEtValeur(i)).Value = MonRs.Fields(ChampEtValeur(i)).Value + ChampEtValeur(i + 1)
Case "(-=)" ' Soustraire
ChampEtValeur(i) = Mid(ChampEtValeur(i), 1, Len(ChampEtValeur(i)) - 4)
MonRs.Fields(ChampEtValeur(i)).Value = MonRs.Fields(ChampEtValeur(i)).Value - ChampEtValeur(i + 1)
Case "(*=)" ' Multiplier
ChampEtValeur(i) = Mid(ChampEtValeur(i), 1, Len(ChampEtValeur(i)) - 4)
MonRs.Fields(ChampEtValeur(i)).Value = MonRs.Fields(ChampEtValeur(i)).Value * ChampEtValeur(i + 1)
Case "(/=)" ' Diviser
ChampEtValeur(i) = Mid(ChampEtValeur(i), 1, Len(ChampEtValeur(i)) - 4)
MonRs.Fields(ChampEtValeur(i)).Value = MonRs.Fields(ChampEtValeur(i)).Value / ChampEtValeur(i + 1)
Case "(&=)" ' Concatener
ChampEtValeur(i) = Mid(ChampEtValeur(i), 1, Len(ChampEtValeur(i)) - 4)
MonRs.Fields(ChampEtValeur(i)).Value = MonRs.Fields(ChampEtValeur(i)).Value & ChampEtValeur(i + 1)
Case Else ' Mise à jour simple
MonRs.Fields(ChampEtValeur(i)).Value = ChampEtValeur(i + 1)
End Select
Next i
' Valide la mise à jour:
MonRs.Update
' Ferme l'enregistrement et désactive la connexion:
MonRs.Close
Cnn.CloseLors d’une mise à jour : si plusieurs enregistrements répondent aux critères de sélection, seul le premier est sélectionné, et mis à jour.
Pensez donc à bien identifier l’enregistrement désiré grâce à la requête de l’argument « SQLWhere ».
Pour une mise à jour en masse vous devrez :
- soit faire une boucle sur les enregistrements ;
- soit utiliser une requête SQL comme nous l’étudierons plus loin.
II-F. Enr_Copie▲
La fonction « Enr_Copie » permet de copier un enregistrement d’une table vers une autre, soit les étapes 4, 5.4, 6 et 8 du schéma :
Function Enr_Copie(CnnSource As ADODB.Connection, StrTableSource As String, _
SQLWhere As String, _
CnnDest As ADODB.Connection, StrTableDest As String, _
ActionCopier As EnumActionCopier) As BooleanSes arguments sont :
- CnnSource : l’objet connexion qui contient la source (entre Cnn1 et Cnn9) ;
- StrTableSource : la table source concernée ;
- SQLWhere : requête d'instruction permettant d'identifier l’enregistrement concerné (sans le mot clé WHERE, voir annexe 1 pour plus d’informations sur les requêtes SQL) ;
- CnnDest : l’objet connexion qui contient la destination (entre Cnn1 et Cnn9) ;
- StrTableDest : la table destination concernée ;
- ActionCopier : l'une des énumérations suivantes :
RemplacerUniquement = si l'enregistrement n'est pas trouvé alors génère une erreur,
RemplacerOuCréer = tente une modification, mais si l'enregistrement n'est pas trouvé alors génère une création,
ForcerCréation = force la création dans la destination (même si l'enregistrement existe).
La fonction retourne VRAI si l'action demandée s'est déroulée correctement, ou FAUX dans les autres cas.
Cet exemple archive un enregistrement d’un article de la table « T_Articles » d’une base initialisée en connexion 1 vers une autre table nommée « T_Articles_Historique » dans une autre base initialisée en connexion 2 (nous aurions utilisé la même connexion si les tables étaient dans la même base) pour permettre une traçabilité des mouvements.
Les deux tables ont une structure identique, sauf le champ « Id_Article » de la table « T_Articles_Historique » qui n’est pas une clé primaire afin de ne pas générer de conflit, car un article peut y être enregistré plusieurs fois :
Enr_Copie CnnSource:=Cnn1, StrTableSource:="T_Articles", _
SQLWhere:="Id_Article=" & Article, _
CnnDest:=Cnn2, StrTableDest:="T_Articles_Historique", _
ActionCopier:=ForcerCréationIdéalement, la table source et la table destination ont la même structure.
Faites attention au respect des règles des clés primaires lors de vos copies.
Il est admis que la table destination ait des champs supplémentaires, qui ne seront donc pas alimentés, sauf par leur valeur par défaut.
II-G. Execute_SQL▲
La fonction « Execute_SQL » permet d’exécuter une instruction SQL, pour faire une mise à jour, une création, une suppression, sur un enregistrement ou un jeu d’enregistrements.
Voir annexe 1 pour plus d’informations sur les requêtes SQL.
Ou pour faire toute autre action que le SQL permet de faire (et il y en a beaucoup), d’où l’importance d’avoir cette fonction disponible pour le programmeur.
Elle réalise les étapes 4, 5.3, 6 et 8 du schéma :
Function Execute_SQL(Cnn As ADODB.Connection, StrSQL As String, _
Optional ActionSiErreur As EnumActionSiErreur = MémoriserErreur) As BooleanSes arguments sont :
- Cnn : l’objet connexion concerné (entre Cnn1 et Cnn9) ;
- StrSQL : la requête d’instruction SQL complète (voir annexe 1 pour plus d’informations sur les requêtes SQL) ;
- ActionSiErreur : l'une des énumérations suivantes :
IgnorerErreur = ignore les erreurs,
MémoriserErreur = mémorise l'erreur s'il y en a une, pour un traitement différé.
La fonction retourne VRAI si la requête est exécutée (même si aucun enregistrement n’est sélectionné) ou FAUX en cas d’erreur.
Pensez à mettre les noms des tables et des champs entre crochets « [ » et « ] » s’ils contiennent des espaces, conformément aux règles du SQL.
Reprenons l’exemple vu avec la fonction « Enr_MAJ » pour modifier l'état d’une commande pour le passer à « Reçue » et ajouter au stock la quantité désirée, et indiquer la date de réception, sur la connexion 1 dans la table « T_Commandes » quand le numéro de commande = NumCommande :
Execute_SQL Cnn:=Cnn1, _
StrSQL:="UPDATE T_Commandes SET Etat='Reçue', Stock = Stock + " & Quantité & ", Quand_Recue=#" & Now & "# WHERE Id_Commande=" & NumCommande, _
ActionSiErreur:=IgnorerErreurLe code de cette fonction pourrait se résumer à cela :
' Ouvre la connexion et attend qu'elle soit établie:
If Cnn.State = adStateClosed Then Cnn.Open
If Cnn.State >= adStateConnecting Then
While (Cnn.State = adStateConnecting): DoEvents: Wend
End If
' Exécute la requête:
Cnn.Execute StrSQL
' Ferme la connexion:
Cnn.CloseLes instructions SQL peuvent rendre de grands services pour les traitements en masse dans une table.
Elles permettent aussi de créer ou supprimer des tables, et plein de choses encore.
Par exemple, une requête SQL permet de sélectionner les données d'un champ en supprimant les doublons et en triant le résultat par ordre croissant.
C'est très pratique pour alimenter dans un UserForm une liste déroulante des valeurs disponibles.
Syntaxe : SELECT DISTINCT [NomDuChamp] FROM [NomDeLaTable] ORDER BY [NomDuChamp]
Appliqué à la fonction « Enr_Affiche », pour afficher sur la feuille 1 en « A1 » la liste des valeurs du champ « Jour » sans doublon de la base « Table_Test » cela donne :
Enr_Affiche Cnn:=Cnn1, StrTableSource:="", _
SQLWhere:="SELECT DISTINCT [Jour] FROM [Table_Test] ORDER BY [Jour]", _
RngDest:=Sheets("Feuil1").Range("A1")
N’oubliez pas que les requêtes SQL sont très pratiques dans Excel (comme étudié au tome 5 chapitre VI et suivants) pour optimiser les traitements d’analyse des données.
Un dernier exemple pour vous convaincre qu’une requête SQL peut remplacer des dizaines de lignes de code VBA : à partir de la table des villes de France (d’une base préalablement initialisée en connexion 1) qui contient les deux champs « Departement » et « Ville », afficher en « A2 » de la feuille « Récap » la liste des départements qui possèdent le plus de villes :
Enr_Affiche Cnn:=Cnn1, StrTableSource:="", _
SQLWhere:="SELECT [Departement], COUNT ([Ville]) " _
& "FROM [Table_Villes] " _
& "GROUP BY [Departement] " _
& "ORDER BY COUNT ([Ville]) DESC", _
RngDest:=Sheets("Récap").Range("A2")Résultat :
II-H. Cnn_Fin▲
La fonction « Cnn_Fin » permet de fermer la transaction en cours (en la validant si tout s’est bien passé ou l’annulant en cas d’erreur), de fermer les connexions, et d’afficher les messages d’erreur mis en différé (ou un message de confirmation du traitement), c’est-à-dire les étapes 6, 7, 8 et 9 du schéma :
Function Cnn_Fin(Optional MessageRésultat As EnumMessage = MessageSiErreur) As BooleanSon argument est :
- MessageRésultat : l'une des énumérations suivantes :
SansMessage = pas de message d'affiché même s'il y a une erreur ;
MessageSiErreur = message seulement si erreur ;
AvecMessage = message si erreur ou message de confirmation si pas d’erreur.
La fonction retourne VRAI si tout s’est bien passé ou FAUX s’il y a eu des erreurs.
Voici un exemple qui reprend quelques fonctions déjà étudiées pour les remettre dans un environnement plus réaliste.
Ici le but est de mettre à jour deux tables (dans la même base, mais elles pourraient être dans des bases différentes Cnn1 et Cnn2) suite à la réception d’une commande, en utilisant une transaction évidemment.
Les fonctions sont préfixées ADO afin de bénéficier de « l’IntelliSense » lors de leur saisie, car les fonctions sont dans le module ADO :
'-----------------------------------------------------------------------------------------------
Sub Exemple
'-----------------------------------------------------------------------------------------------
Dim Quantité As Long, Article As Long, NumCommande As Long
NumCommande = 4 ' pour l’exemple nous travaillons sur la commande numéro 4.
' Ouvre une connexion:
If ADO.Cnn_Initialise(Cnn1, "C:\_Formation_VBA\Exemples.accdb") = True Then
' Initialisation du traitement ADO avec transaction:
ADO.Cnn_Debut AvecTransaction
' Mémorise l'article concerné et la quantité commandée:
Article = ADO.Enr_Info(Cnn1, "T_Commandes", "Id_Commande=" & NumCommande, Valeur, "Id_Article")
Quantité = ADO.Enr_Info(Cnn1, "T_Commandes", "Id_Commande=" & NumCommande, Valeur, "Quantité")
' Modifie l'état de la commande pour le passer à "Reçue" et indique la date de réception:
ADO.Enr_MAJ Cnn1, "T_Commandes", "Id_Commande=" & NumCommande, ModificationUniquement, _
"Etat", "Reçue", _
"Quand_Recue", Now
' Modifie le stock par un ajout grâce à (+=) et indique la date de modification:
ADO.Enr_MAJ Cnn1, "T_Articles", "Id_Article=" & Article, ModificationUniquement, _
"Stock(+=)", Quantité, _
"Quand", Now
End If
' Fin du traitement (avec gestion des erreurs et de la transaction):
ADO.Cnn_Fin AvecMessage
End Sub
'-----------------------------------------------------------------------------------------------
II-I. Les requêtes paramétrées▲
Si vous trouvez le SQL trop complexe à manipuler, Access peut venir à votre secours : car les fonctions « Enr_Info » et « Enr_Affiche » ne se limitent pas de lire les enregistrements d’une table, elles peuvent également lire les enregistrements d’une requête…
Vous pouvez donc créer une requête dans Access, éventuellement en utilisant l’assistant pour vous aider, et utiliser ces fonctions en appelant la requête, donc sans avoir besoin de maîtriser le SQL !
Reprenons notre précédent exemple pour illustrer cela.
À partir de la table des villes de France qui contient les deux champs « Departement » et « Ville », afficher en « A2 » la liste des départements qui possèdent le plus de villes.
Une requête est créée, nommée « Requête_CompteVilles » :
La programmation dans Excel se limitera à cet appel :
ADO.Enr_Affiche Cnn:=Cnn1, StrTableSource:="Requête_CompteVilles", _
SQLWhere:="", RngDest:=Range("A2")Résultat :
III. Un exemple de mise à jour de données dans des bases Access▲
Je reçois régulièrement un fichier Excel pour mettre à jour des données dans Access. Le but est de modifier les dates de démarrage de chantiers et d’ouverture à la clientèle des agences. La clé primaire dans Access est le code PPI, le CDR correspond au numéro de l’agence.
Après retraitement j’obtiens ce tableau, qui contient plusieurs dizaines de lignes.
La fonction qui suit va dans un premier temps vérifier que la valeur du CDR enregistré dans la table « PPI_Table_Opérations », dont la clé primaire est le code PPI, correspond au code reporté en colonne B. Et sera affiché en colonne C.
Pour un besoin de traçabilité, la table « PPI_Table_Modif_Jalons » conserve l’historique de champs modifiés : « Date_R02 » représente la date de démarrage du chantier et « Date_R07 » représente la date d’ouverture à la clientèle.
Son champ « Quand_MAJ » est par défaut la date du jour, donc inutile de le renseigner.
'-----------------------------------------------------------------------------------------------
Sub MAJPPIR02R07()
'-----------------------------------------------------------------------------------------------
Dim i As Long, DL As Long, CDR As Long
Dim Date_R02 As Variant, Date_R07 As Variant
' Active la feuille et recherche la dernière ligne renseignée:
ThisWorkbook.Sheets("Feuil1").Activate
DL = Cells(Application.Rows.Count, 1).End(xlUp).Row
' Ouvre la base Access:
If ADO.Cnn_Initialise(Cnn1, "\\Clynas100\IMR$\BASE PILOTAGE\PPI_Base.accdb") = True Then
' Boucle sur les lignes et recherche si l'opération existe dans PPI
' et si c'est le bon CDR du numéro d'opération:
For i = 2 To DL
Debug.Print "Contrôles : " & Format(i / DL, "0%"): DoEvents
' Affiche le CDR si OK ou quitte si erreur:
CDR = ADO.Enr_Info(Cnn1, "PPI_Table_Opérations", "Clé_Opérations= " & Cells(i, "A"), Valeur, "CDR")
If CStr(Cells(i, "B")) = CDR Then
Cells(i, "C") = Cells(i, "B")
Else
MsgBox "Erreur de CDR sur la ligne : " & i
Exit Sub
End If
Next i
' Boucle sur les lignes à traiter:
For i = 2 To DL
ADO.Cnn_Debut AvecTransaction
Debug.Print "Progression : " & Format(i / DL, "0%"): DoEvents
' Recherche les anciennes dates :
Date_R02 = ADO.Enr_Info(Cnn1, "PPI_Table_Opérations", "Clé_Opérations= " & Cells(i, "A"), Valeur, "Date_R02")
Date_R07 = ADO.Enr_Info(Cnn1, "PPI_Table_Opérations", "Clé_Opérations= " & Cells(i, "A"), Valeur, "Date_R07")
' Modifie les dates dans PPI_Table_Opérations:
ADO.Enr_MAJ Cnn1, "PPI_Table_Opérations", "Clé_Opérations=" & Cells(i, "A"), ModificationUniquement, _
"Date_R02", Cells(i, "D"), _
"Date_R07", Cells(i, "E")
' Ajoute l'historique du jalon R02 dans "PPI_Table_Modif_Jalons":
ADO.Enr_MAJ Cnn1, "PPI_Table_Modif_Jalons", "", CréationUniquement, _
"Clé_Opérations", Cells(i, "A"), _
"Jalon", "R02", _
"Ancien_Date", Date_R02, _
"Nouveau_Date", Cells(i, "D")
' Ajoute l'historique du jalon R07 dans "PPI_Table_Modif_Jalons":
ADO.Enr_MAJ Cnn1, "PPI_Table_Modif_Jalons", "", CréationUniquement, _
"Clé_Opérations", Cells(i, "A"), _
"Jalon", "R07", _
"Ancien_Date", Date_R07, _
"Nouveau_Date", Cells(i, "E")
' Validation de la transaction ou si erreur indique la ligne concernée et quitte la boucle:
If ADO.Cnn_Fin(SansMessage) = False Then
MsgBox "Erreur de traitement ligne : " & i
Exit For
End If
Next i
' Si OK alors affiche que tout s'est bien passé, sinon affiche l'erreur.
ADO.Cnn_Fin AvecMessage
End If
End SubRemarque importante sur la gestion des transactions
Comme dit précédemment, « vous utiliserez une transaction pour garantir que plusieurs enregistrements sont mis à jour simultanément, comme s’il n’y avait qu’une seule opération ». C’est pourquoi la transaction est gérée à l’intérieur de la boucle des lignes à traiter.
Et si, inversement, la transaction avait été gérée à l’extérieur de la boucle ?
Dans notre cas, tout se passerait bien, ça serait même plus pratique, car en cas d’erreur toutes les transactions en attente sont annulées, il suffit de corriger le problème puis de relancer le traitement. Mais cela n’est valable que sur le traitement d’une petite quantité de données, car la taille du tampon de transaction, même si elle est assez grande pour mémoriser plusieurs centaines d’enregistrements, n’est pas illimitée. Et vous risquez un dépassement de capacité sur des traitements volumineux, qui fera planter Excel !
IV. Conclusion▲
Les huit fonctions génériques de cette documentation permettent à un développeur de programmer rapidement de petits traitements en VBA pour interagir avec des bases Access depuis Excel.
En utilisant ces fonctions il n’a pas à se soucier de l’aspect relativement technique de l’utilisation des objets ADODB, tout en laissant la base multi-utilisateur disponible pour tous.
Et l’usage des transactions assure une certaine sécurité dans les mises à jour des données.
Je n’ai pas souhaité détailler davantage le code VBA des fonctions du module ADO pour vous épargner la douloureuse lecture des 1 000 lignes de code. Ni aborder la gestion différée des erreurs pour ne pas alourdir les exemples et perdre le lecteur dans des détails hors sujet.
Ceux que cela intéresse trouveront leur bonheur en farfouillant dans le fichier joint.
Si j’ai bien travaillé, vous avez compris le principe de la technologie ADODB et êtes désormais capables, si nécessaire, de programmer d’autres fonctions adaptées à vos besoins.
Laurent OTT. 2020
V. Annexe 1 – Les requêtes SQL▲
Pour rechercher ou bien mettre à jour une donnée dans une table, les fonctions génériques utilisent les requêtes SQL.
Vous allez constater dans les lignes qui suivent que c'est très simple et très pratique…
Si vous programmez sous Access les requêtes SQL vous sont familières, pour ceux qui ne connaissent pas voici un bref rappel des notions de base.
SQL (Structured Query Language) est un langage de programmation qui permet de manipuler une base de données : sélections d'enregistrements, modifications, ajouts, suppressions.
Nous utiliserons le SQL principalement pour faire des sélections, avec « SELECT », soit la syntaxe :
SELECT « nom des champs » FROM « nom de la table » WHERE « conditions » ORDER BY « nom des champs »
- SELECT « nom des champs » : indiquez les champs à sélectionner (entre crochets si leur nom contient un espace), en les séparant par une virgule (l'ordre est libre).
Ou plus simplement, une étoile permet de sélectionner directement tous les champs : SELECT * FROM…
- FROM « nom de la table » : représente le nom de la table ou de la requête à sélectionner (entre crochets si leur nom contient un espace).
- WHERE « conditions » : pour restreindre la sélection, vous pouvez ajouter une condition simple ou combinée avec les opérateurs AND et OR comme en VBA. Les données alphanumériques sont à mettre entre simples quotes. Les dates sont à mettre entre deux croisillons et au format anglo-saxon (année/mois/jour). Par exemple pour sélectionner les personnes nées à Noël 1971 et qui se prénomment « Noël » :
WHERE [Date Naissance] = #1971/12/25# AND [Prenom] = 'Noël'
- ORDER BY « nom des champs » : facultatif, permet de trier le résultat de la requête, par ordre ascendant (par défaut) avec la clause ASC ou descendant avec la clause DESC. Il est possible d'ordonner par plusieurs champs, en respectant la syntaxe :
ORDER BY « nom champ 1 » [ASC, DESC], « nom champ 2 » [ASC, DESC].
DELETE * FROM « nom de la table » WHERE « conditions » permet de supprimer des enregistrements.
UPDATE « nom de la table » SET « nom du champ » = « nouvelle valeur » WHERE « conditions » permet de modifier un champ.
En SQL l’apostrophe et les guillemets sont des symboles réservés, et pour utiliser ces caractères il faut les doubler.
Par exemple, l’instruction suivante trouve le département de la ville « L'Escale » :
SELECT Departement FROM Table_Villes WHERE Ville = 'L''Escale'
Vous trouverez sur Internet d'autres informations sur les requêtes SQL.
J'aime bien cette adresse : http://www.1keydata.com/fr/sql/syntaxe-sql.php.
Attention, toutes les commandes SQL ne sont pas compatibles avec Excel.
VI. Annexe 2 – Des liens utiles▲
Voici quelques liens qui m’ont été bien utiles pour cette documentation et que je vous recommande :
- Comprendre les Recordset ADO (de Jean-Marc Rabilloud) : https://vb.developpez.com/bidou/recordset-ado/
- Utiliser le modèle ADOX avec Visual Basic (de Jean-Marc Rabilloud) : https://vb.developpez.com/bidou/adox/
- ADO et Visual Basic (de Sébastien Curutchet) : https://drq.developpez.com/vb/tutoriels/ADO/
- Les fichiers de verrouillage Access ldb et laccdb (de Thierry Gasperment) : https://arkham46.developpez.com/articles/access/utilisateurs-ldb/
- L’article de Pierre Fauconnier « Dialogues entre Excel et Access grâce à ADODB » où il est abordé la manipulation des données d’une base Access depuis Excel en utilisant le VBA.
VII. Le fichier joint▲
Vous trouverez le fichier joint MAJ_Bases_Access.xlsm qui contient les fonctions étudiées dans cette documentation.
VIII. Remerciements▲
Je remercie Gaby277, Arkham46, et Pierre Fauconnier pour leur relecture technique et leurs très nombreux conseils, et Claude Leloup pour la correction orthographique.
Ainsi que toute l’équipe de Developpez.com qui participe à la maintenance du site.
