Toute l'actualité de [self-access.com] en direct sur votre ordinateur !  Vous êtes ici : Accueil » Visual Basic » Initiation à VB » L'art de la concaténation » Abolir les concaténationsConnexion
 


Eviter les concaténations

La concaténation est un outil pratique pour générer des chaînes de caractères (SQL ou autres, d'ailleurs) de façon dynamique. Mais le problème - si vous avez lu les pages précédentes - est que la syntaxe devient vite très lourde. Et qui dit lourd dit risque d'erreurs, temps perdu, maintenance compliquée, etc.

L'astuce consiste à éviter la concaténation ! Hum, vous avez lu toutes ces pages sur la concaténation, juste pour arriver au fait... qu'on peut faire sans ? Smiley

Pour éviter certaines concaténations, la première méthode est d'utiliser la fonction BuildCriteria() de Visual Basic. Mais on va s'en passer, parce qu'elle ne traite qu'un seul champ à la fois, et qu'il faut donc toujours finir par une étape de concaténation globale.

On va donc faire autrement : je vous propose une fonction StringFormat(), directement inspirée de la méthode String.Format() de .NET, et qui va simplifier énormément les choses. Voici la mise en place de la fonction...

La fonction StringFormat

Commencez par recopier la fonction suivante dans un module standard de votre base de données (une fois présente dans la base, cette fonction pourra être utilisée partout dans votre projet) :

  1. ' ---
  2. ' INSERTION DE VALEURS DANS UNE CHAINE DE CARACTERES
  3. ' ---
  4. '
  5. Function StringFormat( _
  6.   ByVal strChaine As String, _
  7.   ParamArray varValeurs() As Variant) As String
  8.  
  9.   Dim intI As Integer
  10.   For intI = LBound(varValeurs) To UBound(varValeurs)
  11.     strChaine = Replace(strChaine, "{" & intI & "}", Nz(varValeurs(intI)))
  12.   Next
  13.  
  14.   StringFormat = strChaine
  15. End Function

Fonctionnement de StringFormat

La fonction StringFormat() peut recevoir un nombre variable de paramètres :

  • Le premier paramètre est toujours la chaîne de caractères à formater (dans notre exemple, une instruction SQL). Cette chaîne doit contenir des "marqueurs" pour désigner les valeurs variables (voir plus loin).
  • Le second paramètre et les suivants sont les valeurs variables qui doivent être injectées dans la chaîne.

Dans le premier paramètre, les valeurs variables sont sous la forme {0}, {1}, etc. Chacun de ces marqueurs est ensuite remplacé par sa valeur. Par exemple, si votre instruction SQL est :

Vous écrirez plutôt :

sql = "DELETE * FROM [la table] WHERE [la clef] = {0};"

Vous remplacez simplement la valeur variable par le marqueur {0}. Comme vous avez un seul marqueur, vous devez aussi fournir une seule valeur de remplacement à la fonction StringFormat(). Observez les lignes 7 et 10 notamment :

  1. Sub ExecuterSQL()
  2.   Dim sql As String
  3.   Dim valeur As Integer
  4.  
  5.   ' Initialisation de l'instruction SQL
  6.   valeur = 12
  7.   sql = "DELETE * FROM [la table] WHERE [la clef] = {0};"
  8.  
  9.   ' Injection des valeurs variables
  10.   sql = StringFormat(sql, valeur);
  11.  
  12.   ' Exécution de l'instruction SQL
  13.   CurrentDb.Execute sql
  14. End Sub

Là où la fonction devient efficace, c'est quand le nombre de critères est important. Imaginez une table de 3 champs au moins :

  • champ1 est de type Numérique ;
  • champ2 est de type Texte ;
  • champ3 est de type Date/Heure.

Votre instruction SQL pure pourrait être :

Il suffit de remplacer chaque valeur brute par un marqueur. Dans le cas du champ Texte, les délimiteurs ne font pas partie de la valeur. Par contre, dans le cas du champ Date/Heure, les délimiteurs # feront partie de la valeur (parce que cette valeur est retraitée par la fonction DateUS()). Votre listing devient :

  1. Sub ExecuterSQL()
  2.   Dim sql As String
  3.   Dim valeur1 As Integer
  4.   Dim valeur2 As String
  5.   Dim valeur3 As Date
  6.  
  7.   ' Initialisation de l'instruction SQL
  8.   valeur1 = 12
  9.   valeur2 = "AB99"
  10.   valeur3 = #12/15/2017#
  11.   sql = "DELETE * FROM [la table] WHERE [champ1] = {0} AND [champ2] = '{1}' AND [champ3] = {2};"
  12.  
  13.   ' Injection des valeurs variables
  14.   sql = StringFormat(sql, valeur1, Replace(valeur2, "'", "''"), DateUS(valeur3));
  15.  
  16.   ' Exécution de l'instruction SQL
  17.   CurrentDb.Execute sql
  18. End Sub

Vous notez que l'instruction SQL contient 3 valeurs variables, remplacées donc par les 3 marqueurs {0}, {1} et {2}. Du coup, la fonction StringFormat() reçoit 4 paramètres :

  • l'instruction SQL (avec ses marqueurs)
  • la variable valeur1, de type Numérique, qui remplacera le paramètre {0}
  • la variable valeur2, de type Texte, qui remplacera le paramètre {1}
    Par sécurité, cette valeur est retraitée par la fonction Replace(), pour la gestion des apostrophes SQL.
  • la variable valeur3, de type Date, qui remplacera le paramètre {2}. Comme toute date, cette variable est retraitée par la fonction DateUS() avant d'être transmise à StringFormat().

Important
L'ordre des valeurs doit bien sûr correspondre à l'ordre des marqueurs.

L'avantage de la technique est qu'elle permet de se concentrer sur le code SQL pur, et de conserver l'instruction SQL complète, plutôt que de la découper en segments et d'en perdre la vision globale.

Un peu de formatage

Pour améliorer un chouïa la lecture, il est possible de disposer les instructions VBA sur plusieurs lignes de programme, à l'aide de la combinaison espace+souligné. Vous pouvez vous en servir pour séparer les paramètres de StringFormat() :

  1. Sub ExecuterSQL()
  2.   Dim sql As String
  3.   Dim valeur1 As Integer
  4.   Dim valeur2 As String
  5.   Dim valeur3 As Date
  6.  
  7.   ' Initialisation de l'instruction SQL
  8.   valeur1 = 12
  9.   valeur2 = "AB99"
  10.   valeur3 = #12/15/2017#
  11.   sql = "DELETE * FROM [la table] WHERE [champ1] = {0} AND [champ2] = '{1}' AND [champ3] = {2};"
  12.  
  13.   ' Injection des valeurs variables
  14.   sql = StringFormat(sql, _
  15.     valeur1, _
  16.     Replace(valeur2, "'", "''"), _
  17.     DateUS(valeur3));
  18.  
  19.   ' Exécution de l'instruction SQL
  20.   CurrentDb.Execute sql
  21. End Sub

Version classique

Si vous n'êtes pas convaincu, comparez le listing précédent à celui qui suit, obtenu par concaténation classique. Je vous laisse choisir le plus digeste !

  1. Sub ExecuterSQL()
  2.   Dim sql As String
  3.   Dim valeur1 As Integer
  4.   Dim valeur2 As String
  5.   Dim valeur3 As Date
  6.  
  7.   ' Initialisation de l'instruction SQL
  8.   valeur1 = 12
  9.   valeur2 = "AB99"
  10.   valeur3 = #12/15/2017#
  11.  
  12.   ' Injection des valeurs par concaténation
  13.   sql = "DELETE * FROM [la table] WHERE [champ1] = " & valeur1 & " AND [champ2] = '" & Replace(valeur2, "'", "''") & "' AND [champ3] = " & DateUS(valeur3) & ";"
  14.  
  15.   ' Exécution de l'instruction SQL
  16.   CurrentDb.Execute sql
  17. End Sub

Version finale

Pour l'instant, on n'a travaillé que sur des variables VB. Il est sans doute encore plus intéressant d'alimenter ces variables par des champs de formulaire. Il suffit d'aménager un peu notre listing, et voilà !

  1. Sub ExecuterSQL()
  2.   Dim sql As String
  3.   Dim valeur1 As Integer
  4.   Dim valeur2 As String
  5.   Dim valeur3 As Date
  6.  
  7.   ' Initialisation de l'instruction SQL
  8.   valeur1 = Forms![Mon formulaire]![txtSaisieNombre]
  9.   valeur2 = Forms![Mon formulaire]![txtSaisieTexte]
  10.   valeur3 = Forms![Mon formulaire]![txtSaisieDate]
  11.  
  12.   sql = "DELETE * FROM [la table] WHERE [champ1] = {0} AND [champ2] = '{1}' AND [champ3] = {2};"
  13.  
  14.   ' Injection des valeurs variables
  15.   sql = StringFormat(sql, _
  16.     valeur1, _
  17.     Replace(valeur2, "'", "''"), _
  18.     DateUS(valeur3));
  19.  
  20.   ' Exécution de l'instruction SQL
  21.   CurrentDb.Execute sql
  22. End Sub

Mes livres sur Access...
[cliquez pour plus d'infos]





Hit-Parade 
 
[ Copyright 1997-2018 hervé inisan, self-access.com Reproduction interdite ]