Modifier une instruction SQL (ou requête), en Visual Basic

Vous pouvez commenter et donner une note à cet article : 4 commentaires Donner une note à l'article (5)

Le but de ce tutoriel est de montrer comment insérer des valeurs dans une instruction SQL (requête) ou une expression SQL, en Visual Basic.
La finalité étant de créer dynamiquement, des instructions ou des expressions SQL, qui suivent le cours d'exécution de l'application.

Article lu   fois.

L'auteur

Profil ProSite personnel

Liens sociaux

Viadeo Twitter Facebook Share on Google+   

I. Introduction

La plupart du temps, nous créons nos requêtes à l'aide d'outils visuels, sans se préoccuper du code SQL généré. Pour qu'elles puissent répondre à différentes situations, nous avons la possibilité, dans Access, de les paramétrer. Le plus souvent cela consiste à faire référence à des contrôles de formulaires.
Mais si le paramètre ne se trouve pas sur un formulaire, ou si la requête est la source d'un recordset, ou si le contrôle du formulaire est vide, on est coincé. Même chose, si on développe en dehors d'Access.
L'alternative à cette technique est de modifier dynamiquement, en Visual Basic, une partie du code SQL d'une requête, d'une instruction SQL, ou d'une expression SQL (condition Where de l'ouverture d'un formulaire, d'un filtre de formulaire, d'une fonction de regroupement, …), de manière à la rendre variable.

II. Qu'est-ce qu'une requête ?

D'un point de vue base de données Access, une requête est un objet de la base de données, au même titre qu'une table.
Cet objet sert principalement à stocker une instruction écrite en langage SQL (Structured Query Language) permettant d'interagir avec le moteur de base de données.
On peut donc définir une requête Access, comme étant une instruction SQL sauvegardée (ou stockée) dans une base de données.

Dans le langage courant, nous utilisons le terme « requête », pour désigner indifféremment un objet de base de données Access ou une instruction SQL.

Pour illustrer mon propos, ouvrons une requête Access en mode création :

Image non disponible

Sélectionnons le mode d'affichage SQL (clic-droit sur le fond, derrière les tables) :

Image non disponible

Voila ce qu'est réellement la requête :

Image non disponible

C'est une instruction SQL.
Si on se place du point de vue Visual Basic, c'est simplement du texte (une chaîne de caractères).
La modification d'une instruction SQL (requête) en Visual Basic, se résume à manipuler du texte.
La difficulté réside dans le formatage de ce texte, pour qu'au final, il soit conforme à la syntaxe SQL du moteur de base de données.

III. Ecriture d'une instruction SQL en Visual Basic

En Visual Basic nous écrirons, tout naturellement, une instruction SQL (ou requête) dans une variable de type String.
L'opération consistera à mettre le texte de l'instruction dans cette variable.

III-A. Instruction SQL sans partie variable

Nous allons commencer par écrire une instruction SQL simple, qui ne comportera aucune partie variable.
Prenons par exemple cette requête :

Image non disponible

Affichons son code SQL :

Image non disponible

En Visual Basic, nous allons concaténer les quatre lignes de texte de l'instruction SQL, pour obtenir :

Image non disponible

Le texte de l'instruction SQL est surligné en jaune.
Le pavé bleu clair est un espace, qui a été ajouté pour remplacer le saut de ligne.
L'opérateur de concaténation (&) et le caractère de continuité de ligne (_) sont surlignés en rouge.

On peut conserver les sauts de lignes et ne pas ajouter d'espace.
Il suffit de concaténer la constante vbCrLf

 
Sélectionnez
Dim strSQL As String

strSQL = "SELECT Produits.ID, Produits.[Code du produit], Produits.[Nom du produit]" & vbCrLf & _
         "FROM Produits" & vbCrLf & _
         "WHERE (((Produits.Codefournisseur)='EXOLI'))" & vbCrLf & _
         "ORDER BY Produits.[Nom du produit];"

Le code précédent peut aussi être écrit sans caractère de continuité de ligne.

 
Sélectionnez
Dim strSQL As String

strSQL = "SELECT Produits.ID, Produits.[Code du produit], Produits.[Nom du produit]" & vbCrLf 
strSQL = strSQL & "FROM Produits" & vbCrLf 
strSQL = strSQL & "WHERE (((Produits.Codefournisseur)='EXOLI'))" & vbCrLf
strSQL = strSQL & "ORDER BY Produits.[Nom du produit];"

Au final, la variable strSQL contient le même code SQL que celui de la requête « Requête1 ».
Toutefois, strSQL n'est pas une requête, car ça n'est pas un objet de la base de données.
C'est une variable texte qui contient une instruction SQL.
Je peux utiliser strSQL pour l'affecter à la propriété d'un objet Visual Basic, ou l'utiliser en tant qu'argument d'une méthode ou d'une fonction.
En gros, partout où l'aide indique que l'on peut utiliser une instruction SQL.

III-B. Instruction SQL avec partie variable

Supposons, sur la base de l'exemple précédent, que l'on veuille que le code fournisseur puisse être modifié et qu'il prenne la valeur d'une variable.
Il s'agit de cette ligne :

 
Sélectionnez
         "WHERE (((Produits.Codefournisseur)='EXOLI'))" & vbCrLf & _

On veut que le code fournisseur inscrit « en dur » (EXOLI) puisse être variable.

Pour cela, nous mettrons le code fournisseur (chaîne de caractères) dans la variable déclarée ci-dessous :

 
Sélectionnez
Dim strCodeFournisseur As String

Nous allons maintenant découper la chaîne de caractères en trois, de manière à isoler la valeur que l'on souhaite rendre variable.
Ce que j'appelle valeur, est ici, le texte EXOLI.

La ligne ...

Image non disponible


qui ne comporte qu'une seule chaîne de caractères, peut s'écrire ...

Image non disponible


La valeur (EXOLI) a été isolée dans la deuxième chaîne (surlignée en vert).
Il n'y a plus qu'à remplacer cette chaîne par la variable.

Image non disponible


Le code Visual Basic, utilisé pour mettre l'instruction SQL dans la variable strSQL, est désormais :

 
Sélectionnez
strSQL = "SELECT Produits.ID, Produits.[Code du produit], Produits.[Nom du produit]" & vbCrLf & _
         "FROM Produits" & vbCrLf & _
         "WHERE (((Produits.Codefournisseur)='" & strCodeFournisseur & "'))" & vbCrLf & _
         "ORDER BY Produits.[Nom du produit];"

Nous avons introduit une partie variable, par l'intermédiaire de la variable strCodeFournisseur.
On peut maintenant, facilement modifier l'instruction SQL contenue dans la variable strSQL, en jouant sur le contenu de la variable strCodeFournisseur.

Voyons maintenant comment mettre tout cela en œuvre à l'aide d'un exemple.
Nous avons un formulaire dont le contenu d'une zone de liste changera dynamiquement.
Ce formulaire contient deux zones de liste indépendantes et une étiquette.

Image non disponible

La zone de liste lstFournisseurs contient tous les fournisseurs et la zone de liste lstProduits tous les produits de tous les fournisseurs.

Propriétés des zones de liste :

Propriété lstFournisseurs lstProduits
Nom lstFournisseurs lstProduits
Nbre colonnes 2 3
Largeur colonnes 0cm;4cm 1cm;3cm;8cm
Origine Source Table/Requête Table/Requête
Contenu SELECT Fournisseurs.CodeFournisseur, Fournisseurs.Société
FROM Fournisseurs;
SELECT Produits.ID, Produits.[Code du produit], Produits.[Nom du produit]
FROM Produits
ORDER BY Produits.[Nom du produit];
Colonne liée 1 1

Nous voyons que la propriété « contenu » des deux zones de liste est une instruction SQL.
Celle-ci est obtenue grâce au générateur de requête, invoqué en cliquant sur le bouton Image non disponible à droite de la ligne de la propriété « contenu ».

Nous allons maintenant faire en sorte que, lorsque la sélection de la zone de liste lstFournisseurs change, la zone de liste lstProduits n'affiche que les produits du fournisseur sélectionné.
Pour cela nous allons utiliser la procédure événementielle « Après MAJ » de la zone de liste lstFournisseurs.
Le code de cette procédure devra modifier l'instruction SQL de la propriété « contenu » de la zone de liste lstProduits.
On va réutiliser le code Visual Basic vu un peu plus tôt avec la requête « Requête1 ».

 
Sélectionnez
Private Sub lstFournisseurs_AfterUpdate()
Dim strSQL As String
Dim strCodeFournisseur As String

' Colonne liée = Code Fournisseur (Fournisseurs.CodeFournisseur)
strCodeFournisseur = Me.lstFournisseurs.Value

' Construction instruction SQL de sélection avec concaténation de
' la variable strCodeFournisseur
strSQL = "SELECT Produits.ID, Produits.[Code du produit], Produits.[Nom du produit]" & vbCrLf & _
         "FROM Produits" & vbCrLf & _
         "WHERE (((Produits.CodeFournisseur)=" & strCodeFournisseur & "))" & vbCrLf & _
         "ORDER BY Produits.[Nom du produit];"

' Affectation de la nouvelle instruction SQL à la source
' de la zone de liste lstProduits
Me.lstProduits.RowSource = strSQL

' On copie le contenu de la variable strSQL dans l'étiquette lblSQLlstProduits
Me.lblSQLlstProduits.Caption = strSQL
End Sub

Avec ce simple code, nous avons introduit une partie variable dans l'instruction SQL (requête), source de la zone de liste lstProduits.

Jusqu'ici, nous avons vu comment mettre le code d'une instruction SQL (ou requête) dans une variable Visual Basic de type String, et comment faire qu'une partie de ce code soit variable.
Dans l'exemple utilisé, la partie variable était une valeur de type texte.
Le chapitre suivant va aborder les différents types de valeurs et la façon dont il faut les concaténer.

IV. Ecrire la valeur d'un critère

Le besoin le plus fréquent, dans le paramétrage d'une instruction SQL, concerne la clause WHERE.
Cette clause est constituée de critères, dans lesquels on va mettre des valeurs qui pourront provenir d'un formulaire, d'une variable VB, ou d'une expression VB.
L'instruction SQL étant du texte, il va falloir convertir en texte les valeurs qui ne le sont pas.
De plus, il y a certaines conventions à respecter pour que le moteur de base de données puisse reconnaitre les valeurs écrites de manière littérale.
Il faut, en premier lieu, qu'il reconnaisse que la suite de caractères représente une valeur et non pas un nom de champ ou un mot-clé.
Il lui faut ensuite reconnaitre le type de la valeur (nombre, date, texte).

Qu'est-ce qu'une valeur littérale ?
Ma définition : Une valeur littérale est une valeur inscrite « en dur » dans du code (quel qu'il soit).
Le format d'écriture de la valeur peut signifier explicitement le type de la valeur.

Exemple VB
Sélectionnez
Const Un = 1, lUn = 1&
Const Mois1 = "Janvier"
Dim dt As Date, d As Double
 ...
dt = #1/1/2009#
 ...
If d > 2.71 Then
 ...

1, 1&, "Janvier", #1/12009# , 2.71 sont des valeurs littérales.
#1/1/2009# est explicitement de type date, du fait des dièses qui encadrent la valeur.
Sans cela le compilateur interpréterait 1/1/2009 comme l'expression 1 divisé par 1 divisé par 2009
1& est une valeur littérale, explicitement de type Long à cause du modificateur &

Exemple SQL Access
Sélectionnez

SELECT Commandes.*, Commandes.[Date d'expédition]
FROM Commandes
WHERE ((([Pays/région d'expédition])="France")
  AND (([Frais d'expédition])<10)
 AND (([Date d'expédition]) Between #4/1/2006# And #4/30/2006#));

"France", 10, #4/1/2006#, #4/30/2006# sont des valeurs littérales.

Dans la suite de ce chapitre, les exemples s'appuieront sur une instruction SQL de sélection simple, de ce type :

 
Sélectionnez
SELECT champ(s)
FROM table(s)
WHERE critère

champ(s) : est une liste de champs ou *.
table(s) : une ou plusieurs tables
critère : un critère sur un champ, de la forme Champ = Valeur littérale ou Champ BETWEEN Valeur littérale 1 AND Valeur littérale 2

L'instruction SQL sera écrite en Visual Basic de cette manière :

 
Sélectionnez
Dim strSQL As String
Dim strCrit As String

' Définition du critère
strCrit = <expression chaîne de caractères>

' Création de l'instruction SQL
strSQL = "SELECT champs" & vbCrLf & _
         "FROM table(s)" & vbCrLf & _
         "WHERE " & strCrit & ";"

Nous nous intéresserons uniquement à cette ligne, pour les différents types de valeurs littérales abordés :

 
Sélectionnez
strCrit = <expression chaîne de caractères>

Le type de la valeur sera fonction du type du champ dans le critère.

Voyons maintenant les différents types de valeurs littérales du SQL d'Access, et comment les manipuler en Visual Basic, de manière à ce le moteur de base de données comprenne le code SQL que l'on aura créé.

IV-A. Valeur de type numérique

Les valeurs de type numérique doivent être écrites au format américain.
Le point (.) est le séparateur décimal et il n'y a pas de séparateur pour les milliers.
Deux cent cinquante mille trois cent vingt neuf et trois cent soixante treize millièmes s'écrit : 250329.373

Les nombres entiers ne sont pas concernés puisqu'il n'y a pas de décimales.

En Visual Basic il faut donc convertir des nombres en texte, formatés comme décrit précédemment. Si vous ne le faites pas de manière explicite, Visual Basic le fera pour vous.

Par exemple, ici, je concatène du texte avec un nombre réel double.

 
Sélectionnez
Dim strCrit As String
Dim dblPrix As Double

dblPrix = 99.99
strCrit = "Prix = " & dblPrix

Visual Basic va convertir dblPrix en texte avant de le concaténer.
Mais il y a un problème. Comme je suis en France, la virgule est le séparateur décimal dans mes paramètres régionaux.
strCrit va donc contenir :

 
Sélectionnez
Prix = 99,99

Le moteur de base de données quant à lui attend :

 
Sélectionnez
Prix = 99.99

Solution 1 : Remplacer la virgule par un point, à l'aide de la fonction Visual Basic Replace.

 
Sélectionnez
strCrit = "Prix = " & Replace(CStr(dblPrix), ",", ".")

Solution 2 : Utiliser la fonction Str qui met toujours un point.

 
Sélectionnez
strCrit = "Prix = " & Str(dblPrix)

IV-B. Valeur de type Date

Il y a une double contrainte dans l'écriture de valeurs de type Date.
Elles sont écrites au format date américain et elles sont délimitées (encadrées) par le caractère dièse (#).

Le format date américain est mois/jour/année.
En Visual Basic on pourra utiliser la fonction Format pour convertir une donnée Date/Time en texte.

Tout comme avec un nombre, si on laisse Visual Basic effectuer la conversion Date/Time en texte, on s'expose au problème des paramètres régionaux.
Visual Basic va convertir la date en texte, et la formater selon la configuration des paramètres régionaux.
En France on aura le jour avant le mois, et le moteur de base de données interprétera mal la date puisqu'il va la lire comme si le mois était avant le jour.

Exemple :

 
Sélectionnez
Dim strCrit As String
Dim dtCmde As Date

dtCmde = DateSerial(2009,3,10) ' 10 mars 2009
strCrit = "[Date Commande] = #" & dtCmde & "#"

strCrit contient

 
Sélectionnez
[Date Commande] = #10/03/2009#

Le moteur de base de données va lire 3 octobre 2009, alors qu'on voulait le 10 mars 2009.

Solution : utiliser la fonction Format.

 
Sélectionnez
strCrit = "[Date Commande] = #" & Format(dtCmde, "mm\/dd\/yyyy") & "#"

Les champs de type Date dans Access, contiennent à la fois la date et l'heure.
Si on les utilise sans préciser l'heure, cette dernière est par défaut 00:00:00 .
Dans l'exemple précédent, #10/03/2009# est en fait l'écriture réduite de #10/03/2009 00:00:00# .

Cet exemple crée un critère sur le champ dateHeure, pour extraire les valeurs entre le 11 mars 09 10:00:00 et le 16 mars 09 18:00:00 .

 
Sélectionnez
Dim strCrit As String
Dim date1 As Date, date2 As Date

date1 = DateSerial(2009, 3, 11) + TimeSerial(10, 0, 0) ' 11 mars 09 10:00:00
date2 = DateSerial(2009, 3, 16) + TimeSerial(16, 0, 0) ' 16 mars 09 16:00:00

strCrit = "[dateHeure] BETWEEN " & _
          "#" & Format(date1, "mm\/dd\/yyyy h\:n\:s") & "# AND " & _
          "#" & Format(date2, "mm\/dd\/yyyy h\:n\:s") & "#"

Pour en savoir plus sur les dates, je vous invite à consulter Les Fonctions Date/Heure rédigé par Par Maxence Hubiche.

IV-C. Valeur de type Booléen

Les valeurs de type booléen s'écrivent en langue anglaise.
True/False ou Yes/No pour Vrai/Faux.

Lors de la conversion en texte, nous avons le même souci, lié à la langue, qu'avec les dates : la conversion s'appuie sur les paramètres régionaux.

 
Sélectionnez
Dim strCrit As String
Dim blnRupture As Boolean

blnRupture = False
strCrit = "[Rupture de stock] = " & blnRupture

strCrit contient :

 
Sélectionnez
[Rupture de stock] = Faux

Ne reconnaissant le mot Faux, le moteur de base de données va l'interpréter comme étant un paramètre.

Dans Visual Basic les variables booléennes sont stockées sous forme d'entiers 16-bits : -1/0 pour Vrai/Faux.
On peut utiliser -1/0 en lieu et place de True/False dans une expression SQL Jet.

 
Sélectionnez
strCrit = "[Rupture de stock] = " & CInt(blnRupture)

Il y a un petit désavantage. Le code SQL est moins lisible qu'avec True/False où on reconnait tout de suite qu'il s'agit de valeurs booléennes.

Autre solution avec IIf :

 
Sélectionnez
strCrit = "[Rupture de stock] = " & IIf(blnRupture, "True", "False")

IV-D. Valeur de type Texte

Dans le SQL d'Access, les valeurs de type texte (chaînes de caractères) sont délimitées (encadrées) par des guillemets doubles (") ou simples (').
On peut utiliser indifféremment l'un ou l'autre.
Visual Basic, quant à lui, ne reconnait que le guillemet double en tant que délimiteur de chaînes de caractères.

Rappel : à l'intérieur d'une chaîne de caractères Visual Basic, le caractère guillemet double doit être doublé, pour être interprété comme un caractère et non un délimiteur.
Par exemple, pour affecter à ma variable maVarTexte, le texte « il ne faut pas de "s" à chaque »

 
Sélectionnez
maVarTexte = "il ne faut pas de ""s"" à chaque"

Cette même règle s'applique au SQL d'Access.
Il faut doubler le délimiteur de chaîne de caractères, si celui-ci est un caractère à l'intérieur de la chaîne.
Nous allons voir des exemples SQL, de clause WHERE, où nous reprendrons l'exemple de texte précédent plus celui-ci : « Utilisation non valide de '.', '!', ou '()'. »

Si on utilise " comme délimiteur :

 
Sélectionnez
WHERE MonChampTexte = "pas de ""s"" à chaque"
 
Sélectionnez
WHERE MonChampTexte = "Utilisation non valide de '.', '!', ou '()'."

Si on utilise ' comme délimiteur :

 
Sélectionnez
WHERE MonChampTexte = 'pas de "s" à chaque'
 
Sélectionnez
WHERE MonChampTexte = 'Utilisation non valide de ''. '', ''! '', ou ''()''.'

Voyons maintenant comment mettre tout cela dans une variable Visual Basic de type String.
La difficulté est de "jongler" avec les délimiteurs de texte Visual Basic et SQL.
Dans les exemples de code, nous n'écrirons que le critère portant sur le champ MonChampTexte.

Si on utilise " comme délimiteur :

 
Sélectionnez
strCrit = "MonChampTexte = ""pas de """"s"""" à chaque"""
 
Sélectionnez
strCrit = "MonChampTexte = ""Utilisation non valide de '.', '!', ou '()'.""

Si on utilise ' comme délimiteur :

 
Sélectionnez
strCrit = "MonChampTexte = 'pas de ""s"" à chaque'"
 
Sélectionnez
strCrit = "MonChampTexte = 'Utilisation non valide de ''.'', ''!'', ou ''()''.'"

Nous allons encore corser les choses, en mettant le texte à comparer au champ MonChampTexte, dans deux variables.

 
Sélectionnez
Dim strCrit As String
Dim strTexte1 As String, strTexte2 As String

' Texte : pas de "s" à chaque
strTexte1 = "pas de ""s"" à chaque"

' Texte : Utilisation non valide de '.', '!', ou '()'. 
strTexte2 = "Utilisation non valide de '.', '!', ou '()'."

Comme nous sommes en Visual Basic, j'ai doublé les guillemets doubles autour du s car ils sont à l'intérieur d'une chaîne de caractères VB.

Si on utilise " comme délimiteur :

 
Sélectionnez
strCrit = "MonChampTexte = """ & strTexte1 & """"
 
Sélectionnez
strCrit = "MonChampTexte = """ & strTexte2 & """"

Cela ne fonctionne que pour le deuxième exemple. Pourquoi ? Parce que la variable strTexte1, dans le premier exemple, contient des caractères guillemets doubles, et qu'ils ne sont pas doublés. On va obtenir ceci dans la variable strCrit :

Image non disponible

Ce n'est pas du SQL correct.
J'ai surligné en bleu le délimiteur de début de chaîne, et en gris le délimiteur de fin de chaîne.
Le moteur de base de données voit deux chaînes de caractères. Je les ai surlignées en jaune.

La solution la plus rapide consiste à doubler les éventuels guillemets doubles, avec la fonction Visual Basic Replace.

 
Sélectionnez
strCrit = "MonChampTexte = """ & Replace(strTexte1, """", """""") & """"
 
Sélectionnez
strCrit = "MonChampTexte = """ & Replace(strTexte2, """", """""") & """"

Si on utilise ' comme délimiteur :

 
Sélectionnez
strCrit = "MonChampTexte = '" & strTexte1 & "'"
 
Sélectionnez
strCrit = "MonChampTexte = '" & strTexte2 & "'"

Cette fois-ci, c'est la deuxième ligne qui ne fonctionne pas, parce que les guillemets simples n'ont pas été doublés.
La variable strCrit va contenir :

Image non disponible

Nous pouvons, encore une fois, utiliser la fonction Replace pour doubler les guillemets simples.

 
Sélectionnez
strCrit = "MonChampTexte = '" & Replace(strTexte1, "'", "''") & "'"
 
Sélectionnez
strCrit = "MonChampTexte = '" & Replace(strTexte2, "'", "''") & "'"

De tout cela, il se dégage une chose : utiliser des guillemets simples, comme délimiteurs de chaîne de caractères SQL, est un peu plus simple et plus lisible.

IV-E. Résumé

Nous avons vu que l'écriture de valeurs littérales en SQL Access est soumise à certaines règles.
En voici un résumé :

Type valeur Délimiteur Règle de conversion en Texte
Numérique   Utiliser le point (.) comme séparateur décimal
Date/Heure # Mettre la date au format américain mois/jour/année
Booléenne   Ecrire True/False ou Yes/No. Eventuellement -1/0
Texte ' ou " Doubler le délimiteur s'il est à l'intérieur du texte

V. Mise en application à l'aide d'un exemple

J'ai créé une base à partir de la base exemple Northwind.mdb de Microsoft, dont je n'ai conservé que quelques tables.

Image non disponible

J'ai également créé un formulaire fmCommandes qui regroupe tous les exemples que je vais commenter par la suite.
La source de données de ce formulaire est une requête basée sur les tables Commandes et Clients.

 
Sélectionnez
SELECT Commandes.[Réf commande], Commandes.[Réf employé], 
  Commandes.[Réf client], Clients.Société, Commandes.[Date de commande],
  Commandes.[Réf statut], Commandes.[Date d'expédition], 
  Commandes.[Pays/région d'expédition] 
FROM Clients INNER JOIN Commandes ON Clients.ID=Commandes.[Réf client];

fmCommandes est composé de trois parties

Image non disponible

1 : données principales relatives à la commande
2 : sous-formulaire contenant les lignes de la commande
3 : contrôle à onglets

V-A. Onglet « Commandes »

L'onglet « Commandes » comporte une zone de liste lstCmdesClient dont la fonction est de lister un résumé des commandes du client.
Le bouton « Créer liste » (cmdCreerLstCmdesClient) réalise cette tâche, en créant dynamiquement une instruction SQL, et en l'affectant à la source de la zone de liste.

Voici le code de l'événement « sur clic » du bouton :

 
Sélectionnez
Private Sub cmdCreerLstCmdesClient_Click()
Dim strSociete As String

strSociete = Nz(Me.Société, "")
If Len(strSociete) > 0 Then
   Me.lstCmdesClient.RowSource = Me.CreerInstSqlCmdesClient(strSociete)
   Me.lblSQLlstCmdesClient.Caption = Me.lstCmdesClient.RowSource
End If
End Sub

Pour ceux qui ne connaissent pas la fonction Access Nzfonction Nz, son rôle est de remplacer la valeur Null par une expression de son choix.
On peut ainsi, éviter l'erreur utilisation  incorrecte  de  NullErreur d'exécution 94, et la propagation de la valeur Null dans une expression.
La conséquence d'une propagation de Null dans une expression, est que le résultat de cette dernière est lui-même Null.

L'instruction SQL, utilisée comme source de la zone de liste, a été créée en faisant un copier/coller du code SQL de cette requête :

Image non disponible
 
Sélectionnez
SELECT Commandes.[Réf commande], Commandes.[Réf client], Clients.Société, 
   Count([Détails commande].[Réf produit]) AS Articles, 
   Sum([Prix unitaire]*[Quantité]*(1-[Remise])) AS [Montant Commande]
FROM (Clients INNER JOIN Commandes ON Clients.ID = Commandes.[Réf client])
   INNER JOIN [Détails commande] ON Commandes.[Réf commande] = [Détails commande].[Réf commande]
WHERE (((Clients.Société)='Nom Société'))
GROUP BY Commandes.[Réf commande], Commandes.[Réf client], Clients.Société;

La partie variable de l'instruction SQL portera sur le critère de la clause WHERE.
Le champ Société étant de type texte il faudra concaténer une valeur littérale texte.
On fait le choix d'utiliser le guillemet simple comme délimiteur de texte SQL.

La fonction CreerInstSqlCmdesClient crée le texte de l'instruction SQL, en y insérant la partie variable, fournie en argument.

 
Sélectionnez
Function CreerInstSqlCmdesClient(strSociete As String) As String
Dim strSQL As String
Dim strSQLSELECT As String
Dim strSQLWHERE As String
Dim strSQLGROUPBY As String

On met tout le texte de "SELECT" jusqu'à "WHERE" dans la variable strSQLSELECT.

 
Sélectionnez
strSQLSELECT = _
"SELECT Commandes.[Réf commande], Commandes.[Réf client], " & _
"Clients.Société, Count([Détails commande].[Réf produit]) AS Articles, " & _
"Sum([Prix unitaire]*[Quantité]*(1-[Remise])) AS [Montant Commande]" & vbCrLf & _
"FROM (Clients INNER JOIN Commandes ON Clients.ID = Commandes.[Réf client]) " & _
"INNER JOIN [Détails commande] " & _
"ON Commandes.[Réf commande] = [Détails commande].[Réf commande]"

On met tout le texte de la clause GROUP BY dans la variable strSQLGROUPBY

 
Sélectionnez
strSQLGROUPBY = _
"GROUP BY Commandes.[Réf commande], Commandes.[Réf client], Clients.Société;"

On crée le texte de la clause WHERE en concaténant les parties fixes et la variable strSociete.
Comme strSociete peut contenir des guillemets simples ('), et que ce caractère est utilisé ici comme délimiteur de texte dans l'instruction SQL, on ne concatène pas la variable directement.
On utilise la fonction Replace pour doubler les guillemets simples.

 
Sélectionnez
strSQLWHERE = "WHERE (((Clients.Société)='" & _
              Replace(strSociete, "'", "''") & "'))"

Enfin, on assemble toutes les parties de l'instruction SQL dans la variable strSQL.

 
Sélectionnez
strSQL = strSQLSELECT & vbCrLf & _
         strSQLWHERE & vbCrLf & _
         strSQLGROUPBY
         
CreerInstSqlCmdesClient = strSQL
End Function

Bouton « Créer requête »
Le code de l'événement « sur clic » du bouton « Créer requête » fait presque la même chose que celui du bouton « Créer liste ».
Mais au lieu d'affecter l'instruction SQL générée, à la source d'une zone de liste, il va la sauver dans une requête de la base de données.
Le code utilise la bibliothèque DAO, sur laquelle Christophe Warin a écrit un article de référence : Définition et manipulation de données avec DAODAO.
Voir la partie 4.7. L'objet QueryDefL'objet QueryDef pour ce qui concerne la manipulation de requêtes en tant qu'objets de base de données Access.

 
Sélectionnez
Private Sub cmdCreerRequeteCmdesClient_Click()
Dim db As DAO.Database, qd As DAO.QueryDef
Dim strRequete As String, strSQL As String
Dim strSociete As String
' Nom requête à modifier ou créer
strRequete = "reqCmdesUnClient"
' Nom société
strSociete = Nz(Me.Société, "")
If Len(strSociete) > 0 Then
   Set db = CurrentDb
   ' Crée le code SQL et le met dans la variable strSQL
   strSQL = CreerInstSqlCmdesClient(strSociete)
   ' On s'assure que la requête n'est pas ouverte.
   DoCmd.Close acQuery, strRequete
   ' Tente d'obtenir l'objet QueryDef correspondant à
   ' la requête. Si elle n'existe pas une erreur se produit
   On Error Resume Next
   Set qd = db.QueryDefs(strRequete)
   On Error GoTo 0
   If qd Is Nothing Then
      ' Si la requête n'existe pas, on la crée avec
      ' son code SQL
      Set qd = db.CreateQueryDef(strRequete, strSQL)
   Else
      ' Affecte le nouveau code SQL de la requête
      qd.SQL = strSQL
   End If
   qd.Close
   Set db = Nothing
   ' Ouvre la requête en mode feuille de données
   DoCmd.OpenQuery strRequete
   Me.SetFocus
End If
End Sub

V-B. Onglet « Autres infos »

Dans l'onglet « Autres infos » il y a deux parties que je nommerai zone 1 et zone 2.

Image non disponible

V-B-1. Zone 1

Le propos de cette zone est d'ajouter des données dans la table tblCommandes à l'aide d'une instruction SQL INSERT.

 
Sélectionnez
INSERT INTO TableDestination
(ChampDest1, ChampDest2, ...)
VALUES(Valeur1, Valeur2, ...)

Cette instruction permet d'ajouter un enregistrement à TableDestination.
(ChampDest1, ChampDest2, ...) est la liste des champs à remplir.
(Valeur1, Valeur2, ...) est la liste des valeurs.
Valeur1 va dans ChampDest1, Valeur2 dans ChampDest2, etc.

Nous ne sommes plus dans le contexte de l'écriture d'un critère SQL, mais il s'agit toujours d'écrire des valeurs littérales.
Ces valeurs devront être correctement formatées, en fonction des types des champs qui vont les recevoir.

Les boutons dont la légende finit par "v1" et "v2" créent l'instruction SQL, et la copient dans l'étiquette juste en dessous.
Le bouton +! exécute l'instruction SQL.

Voyons en détail le code appelé par le bouton « Créer instruction SQL, "Ajout dans tblCommandes" v1 » (cmdCreeInstrSqlInsert1).

Début de la procédure et déclaration des variables.

 
Sélectionnez
Sub CreerInstrSql1()
Dim strRefCmde As String
Dim strTbl As String, strSQL As String, strVal As String

Le nom de la table dans laquelle nous ajoutons des enregistrements est mis dans la variable strTbl.
Le numéro de la commande de l'enregistrement en cours est mis dans la variable strRefCdme.

 
Sélectionnez
strTbl = "tblCommandes"
strRefCmde = CStr(Nz(Me.Réf_commande, 0))

Si le numéro de commande est nul, on quitte la procédure.

 
Sélectionnez
If strRefCmde = "0" Then Exit Sub

On met le début de l'instruction SQL dans la variable strSQL.

 
Sélectionnez
strSQL = "INSERT INTO [" & strTbl & "] " & vbCrLf & _
         "([Réf commande], " & _
         "[Réf employé], [Société], [Date de commande], " & _
         "[Date d'expédition], MontantTotal, " & _
         "NbreArticles, Remise)" & vbCrLf & _
         "VALUES ("

Nous allons maintenant ajouter, en les concaténant, les valeurs.

Réf commande (Entier Long). La valeur a déjà été extraite dans la variable strRefCmde.

 
Sélectionnez
strSQL = strSQL & strRefCmde & ", "

Réf employé (Entier Long).

 
Sélectionnez
strSQL = strSQL & Nz(Me.Réf_employé, "Null") & ", "

Visual Basic ne sait pas convertir implicitement la valeur Null en texte.
On utilise Nz pour substituer à la valeur Null, la chaîne de caractères "Null".

Société (Texte).

 
Sélectionnez
strVal = Nz(Me.Société, "Null")
strSQL = strSQL & "'" & Replace(strVal, "'", "''") & "', "

Le délimiteur de texte (SQL) est le guillemet simple. Par conséquent on utilise la fonction Replace pour doubler les guillemets simples qui pourraient se trouver dans le champ Société.

Date de commande (Date).

 
Sélectionnez
strSQL = strSQL & "#" & _
         Format(Me.Date_de_commande, "m\/d\/yyyy") & "#, "

Conversion de la date en texte, au format mois/jours/année.

Date d'expédition (Date).

 
Sélectionnez
If IsNull(Me.Date_d_expédition) Then
   strSQL = strSQL & "Null, "
Else
   strVal = Format(Me.Date_d_expédition, "m\/d\/yyyy")
   strSQL = strSQL & "#" & strVal & "#, "
End If

Ce champ peut être vide. On effectue donc un test pour savoir si le champ contient Null.
Si c'est le cas, on concatène la chaîne de caractères "Null", sinon on convertit la date en texte mois/jour/année.

MontantTotal (Monétaire).

 
Sélectionnez
strVal = Str(Nz(DSum("MontantLigne", "reqDetCmde", "[Réf commande]=" & strRefCmde), 0))
strSQL = strSQL & strVal & ", "

Il s'agit d'un nombre avec des décimales. On emploie donc la fonction Str pour effectuer la conversion en texte.

NbreArticles (Entier Long).

 
Sélectionnez
strVal = DCount("*", "reqDetCmde", "[Réf commande]=" & strRefCmde)
strSQL = strSQL & strVal & ", "

Remise (Booléen).

 
Sélectionnez
If Nz(DSum("Remise", "reqDetCmde", "[Réf commande]=" & strRefCmde), 0) > 0 Then
   strVal = "True"
Else
   strVal = "False"
End If

Finalisation de l'instruction SQL.

 
Sélectionnez
strSQL = strSQL & strVal & ")"

Me.lblSQLinsert.Caption = strSQL
End Sub

Je ne détaillerai pas le code du bouton "Créer instruction SQL, "Ajout dans tblCommandes" v2".
C'est le même code que celui du "v1", mais en faisant appel à une fonction de formatage personnelle.
Cette fonction est utilisée un peu plus loin.

V-B-2. Zone 2

Dans cette zone nous allons compter le nombre d'enregistrements de la table Commandes, répondant à certains critères, à l'aide de la fonction de regroupement Domaine DCount.

 
Sélectionnez
DCount(Expr As String, Domain As String, [Criteria])

Le troisième argument de cette fonction est une chaîne de caractères (String), contenant une expression SQL servant de critère à une clause WHERE, mais sans le mot-clé WHERE. Il s'agit encore une fois, d'écrire du code SQL dans une expression Visual Basic de type String. Il faudra donc respecter les règles de formatage vues précédemment.

Pour simplifier le formatage des valeurs littérales SQL, j'ai écrit une fonction de conversion en texte, qui sera utilisée dans les exemples à venir.

 
Sélectionnez
Public Function CLittSql(expr As Variant, TypeSql As TypeSqlLitteral)
Dim strRet As String

If IsNull(expr) Then
   strRet = "Null"
ElseIf IsEmpty(expr) Then
   strRet = "Null"
Else
   Select Case TypeSql
       Case TypeSqlLitteral.sqlNombre
            strRet = Trim(Str(expr))
       Case TypeSqlLitteral.sqlDate
            strRet = "#" & Format(expr, "m\/d\/yyyy") & "#"
       Case TypeSqlLitteral.sqlDateheure
            strRet = "#" & Format(expr, "m\/d\/yyyy hh\:nn\:ss") & "#"
       Case TypeSqlLitteral.sqlBooleen
            If (expr = True) Then strRet = "True" Else strRet = "False"
       Case TypeSqlLitteral.sqlTexte
            strRet = "'" & Replace(expr, "'", "''") & "'"
   End Select
End If
CLittSql = strRet
End Function

Le premier argument est la valeur à convertir.
Le deuxième est le type depuis lequel il faut convertir en texte.
Ce type est de type énuméré, déclaré somme suit :

 
Sélectionnez
Public Enum TypeSqlLitteral
    sqlNombre
    sqlDate
    sqlDateheure
    sqlBooleen
    sqlTexte
End Enum

Tous les exemples sont basés sur le même principe.
Le critère est écrit dans une variable de type String (strCrit), puis cette variable est fournie à la fonction DCount, en troisième argument.

V-B-2-a. Nombre de commandes à cette date

Il s'agit de compter le nombre de commandes dont la date est la même que celle de la commande en cours.

 
Sélectionnez
Private Sub cmdNbCmdesMemeDate_Click()
Dim strCrit As String

strCrit = "[Date de commande]=" & CLittSql(Me.Date_de_commande, sqlDate)
Me.txtNbCmdesMemeDate = DCount("*", "Commandes", strCrit)

End Sub

V-B-2-b. Nombre de commandes pour le pays sélectionné

Ici, nous comptons le nombre de commandes pour le pays sélectionné dans la zone de liste modifiable cboPays.

 
Sélectionnez
Private Sub cmdNbCmdesPays_Click()
Dim strCrit As String

If Not IsNull(Me.cboPays) Then
   strCrit = "[Pays/région d'expédition]=" & CLittSql(Me.cboPays, sqlTexte)
   Me.txtNbCmdesPays = DCount("*", "Commandes", strCrit)
Else
   Me.txtNbCmdesPays = DCount("*", "Commandes")
End If

End Sub

Je distingue deux cas, selon qu'une sélection a été faite, ou pas.
Si aucune sélection n'a été faite, je considère que le pays est indifférent et je ne fournis pas le troisième argument de DCount.
J'obtiens donc le nombre d'enregistrements de la table Commandes.

V-B-2-c. Nombre de commandes pour le pays sélectionné et la date de commande

Compte le nombre de commandes pour le pays sélectionné dans la zone de liste modifiable cboPays2, et dont la date de commande est la même que celle de l'enregistrement en cours.

 
Sélectionnez
Private Sub cmdNbCmdesPaysMemeDate_Click()
Dim strCrit As String

strCrit = "[Date de commande]=" & CLittSql(Me.Date_de_commande, sqlDate)
If Not IsNull(Me.cboPays2) Then
   strCrit = strCrit & " AND " & _
          "[Pays/région d'expédition]=" & CLittSql(Me.cboPays2, sqlTexte)
End If
Me.txtNbCmdesPaysMemeDate = DCount("*", "Commandes", strCrit)

End Sub

Si un pays est sélectionné, le critère pour DCount est composé de deux critères. Un sur la date et un sur le pays.
Si aucun pays n'est sélectionné, le critère ne porte que sur la date.

V-B-2-d. Nombre de commandes à cette date d'expédition

Compte les commandes dont la date de livraison est identique à celle de l'enregistrement courant.

 
Sélectionnez
Private Sub cmdNbCmdesMemeDateExp_Click()
Dim strCrit As String

strCrit = "[Date d'expédition]"
' Choix opérateur de comparaison
If IsNull(Me.Date_d_expédition) Then
   strCrit = strCrit & " Is "
Else
   strCrit = strCrit & "="
End If
' Concatène valeur de comparaison
strCrit = strCrit & CLittSql(Me.Date_d_expédition, sqlDate)

Me.txtNbCmdesMemeDateExp = DCount("*", "Commandes", strCrit)

End Sub

La date d'expédition peut contenir Null. L'opérateur de comparaison pour Null étant Is, il faut traiter séparément ce cas.

VI. Conclusion

Les compétences requises pour manipuler, par programmation, du code SQL ne sont pas trop exigeantes.
Il faut avoir des notions de SQL et savoir concaténer du texte dans un langage de programmation, comme Visual Basic par exemple.
On n'a même pas besoin d'écrire soi-même le code SQL. Il peut être copié/collé depuis un outil visuel qui génère ce code.

Si l'on sait correctement écrire la valeur d'un critère SQL en Visual Basic, on est capable de modifier/créer dynamiquement :
- le ou les critères de la clause WHERE ou de la clause HAVING d'une instruction SQL.
  Cette instruction SQL pourra servir de source à un Formulaire, une zone de liste, un recordset …
- l'argument Where des fonctions de regroupement DCount, DSum, DLookup …
- l'argument Where de la méthode DoCmd.OpenForm
- la propriété Filter d'un formulaire.
- les valeurs d'une instruction (SQL) INSERT INTO table (champ1[, Champ2[, …]]) VALUES(Valeur1[, Valeur2[, …]])
- les valeurs d'une instruction (SQL) UPDATE table Set champ1=Valeur1 [,champ2=Valeur21[, …]]
La liste est loin d'être exhaustive.

Il ne faut pas, cependant, délaisser les requêtes paramétrées lorsque leur utilisation est possible.
Elles offrent des avantages par rapport à du code SQL écrit en Visual Basic.
- Conception et maintenance plus aisées.
- Moindre sensibilité au type des paramètres.
- Possibilité d'afficher les dépendances à d'autres objets.

Ce tutoriel n'a exploré que quelques applications possibles de la manipulation de code SQL.
Voici d'autres exemples d'applications :
Les Fonctions de Domaine dans Access
Concevoir une liste qui en alimente une autre
Les listes multifonctions
Formulaires de recherche

D'autres liens utiles :
Débogage des requêtes écrites en VBA
LE SQL de A à Z : 2eme partie - le simple (?) SELECT et les fonctions SQL
Guide de référence Microsoft Jet SQL

VII. Remerciements

L'équipe MS-Office de developpez.com.
Nono40 pour son outil de création d'article et developpez.com pour leur hébergement.
RougeCitron pour la relecture orthographique.

Base de données exemple (2000): Image non disponible

Vous avez aimé ce tutoriel ? Alors partagez-le en cliquant sur les boutons suivants : Viadeo Twitter Facebook Share on Google+   

  

Copyright © 2009 Christophe Le Fustec. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents, images, etc. sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts.