IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Voir le flux RSS

jimbolion

Résolution des problèmes fréquemment rencontrés dans l'écriture des requêtes écrites en vba

Noter ce billet
par , 07/02/2016 à 06h55 (1303 Affichages)
J'ai souvent vu passer de nombreux fils de discussion suite aux problèmes rencontrés lors de l’écriture de requêtes SQL en VBA. Je ne vais pas dans ce blog décrire l'exhaustivité de ces cas souvent liés à la conception même de la requête mais repréciser les bonnes pratiques et les erreurs à ne pas commettre.

Avant tout un petit rappel sur la syntaxe du VBA et les bonnes pratiques :

Le langage Visual basic s'écrit sous la forme d'un texte à raison d'une déclaration ou une instruction par ligne. Un renvoi à la ligne est toutefois réalisable à l'aide de la notation _ ; il est possible de placer plusieurs instructions sur une seule ligne à l'aide de la notation :, ainsi que d'intercaler des lignes vides ou de placer un commentaire en fin de ligne ou entre les lignes. Il est sage d'encadrer les symboles (&, :, etc.) par au-moins un espace. Les noms, définis par Visual basic (mot-clefs, constantes, etc.) ou créés par le programmeur, peuvent s'écrire indifféremment en lettres minuscules ou majuscules (pas de différenciation). Toutefois il est recommandé d'utiliser des normes dans l'utilisation des noms ou des champs.

Un type définit la nature d'une variable ou d'un paramètre, qui peut être un type élémentaire : Boolean, Integer, Long, Byte, Single, Double, String, Currency, date, Object, Variant, Enum ou un type composé à l'aide du mot-clef Type, ou une classe.

liste de déclarations : Les types de variables et constantes utilisées doivent être déclarées dans l'en tête de la fonction ou de la procédure grâce aux mots clés Dim, Private, Public et const.

Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
Exemples :
Dim Rsql As string, Madate As Date, manote as Single
Sub maFonction(MonNom As String, Madate As Date)
Me : Objet défini dans une procédure privée, associée généralement à un événement ou un élément d'une boîte de dialogue (contrôle), désignant l'objet à laquelle s'applique la procédure.

Nous allons donc à l'aide d'un exemple précis, soulever quelques difficultés et tenter de les résoudre par des techniques qui jusque là ne m'ont posées aucun souci.

Création de notre entité de base nommée T_Eleves et des champs décrivant son contenu :

id NuméroAuto
nomEleve Texte court
prenom Texte court
dateNais Date/Heure
moyenne Numérique (Réel Simple)
Redoublant Oui/Non (Booleen)

La construction d'une requête repose sur quelques règles de base et principalement pour commencer celles-ci :

  1. Toutes les valeurs de type texte doivent être encadrés par des quotes (simples ou doubles),
  2. Toutes les valeurs de type date doivent être encadrés par des #.


Nous allons donc commencer par créer notre formulaire dont la source est basée sur notre entivé T_Eleves afin de renseigner les différentes valeurs. De nombreux tutoriels existent pour vous aider à réaliser ces formulaires. Le notre plutôt simpliste n'est là que pour parler de notre sujet principal.

Une image de ce formulaire nous donnerait l'effet visuel suivant :

Nom : Formulaire.JPG
Affichages : 367
Taille : 17,9 Ko

Et le code généré sur l’événement sur clic du bouton <Enregistrer> :

Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
Private Sub B_Save_Click()
On Error GoTo Err_Commande
Dim R_Sql As String
      '
      If Not IsDate(Me.T_DateN) Then
        MsgBox "La date de Naissance est obligatoire", vbCritical, "Erreur Saisie"
        Me.T_DateN.SetFocus
        Exit Sub
      End If
      
      '
      R_Sql = "INSERT INTO [T_Eleves] " & _
        "(nomEleve, prenom, dateNais, moyenne, Redoublant) " & _
        " VALUES (" & _
        Nz(Me.T_nom, "") & "', " & _
        Nz(Me.T_prenom, "") & "', " & _
        "#" & Nz(Me.T_DateN, Null) & "#," & _
        Nz(Me.T_Moyenne, 0) & ", " & _
        Nz(Me.T_Redoublant, "")  & _
        ")"
'
CurrentDb.Execute R_Sql, dbFailOnError
'
Exit Sub
Err_Commande:
    MsgBox Err.Description
End Sub
En appliquant les règles citées précédemment, nous devrions donc pouvoir injecter ces données dans notre table T_Eleves. En cliquant sur le bouton <Enregistrer> nous opérons donc sur l’exécution du code. Malheureusement, nous levons une erreur de ce type :

Le nombre de valeurs de la requête doit coïncider avec le nombre de champs destination apparaît à l'écran
La technique de débogage largement expliquée par Caféine dans son fameux tutoriel que voici :
Les techniques de débogage
nous permet donc d'afficher le résultat de notre chaîne dans la fenêtre d'exécution de l'éditeur VBA

Code sql : Sélectionner tout - Visualiser dans une fenêtre à part
INSERT INTO [T_Eleves] (nomEleve, prenom, dateNais, moyenne, Redoublant)  VALUES ('DUPONT', 'Martin',#04/02/1986#,10,5, -1)

En regardant de plus près cette requête, nous constatons que 5 champs sont déclarés mais que 6 valeurs sont identifiées. Notre 10,5 n'a pas été traduit par le code en 10.5 (SQL n'acceptant que des points pour les valeurs numériques). Cette erreur ne sera soulevée que pour les utilisateurs ayant conservés la virgule dans les séparateurs numériques du panneau de configuration. Si votre projet devait être déployé sur d'autres ordinateurs, il vaut mieux intégrer directement ce contrôle dans votre code.

Afin de résoudre ce problème nous allons donc créer une fonction standard dans un module spécifique. Cette fonction de type publique pourra donc être appelée dans toutes les requêtes construites par VBA dans l'ensemble du projet.

Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
Function VirgToPoint(sValeur As String) As String
' Conversion implicite de format Réel en Chaîne
    VirgToPoint = Replace(sValeur, ",", ".")
End Function
et la modification de la ligne dans la procédure de notre formulaire :

Code : Sélectionner tout - Visualiser dans une fenêtre à part
"" & VirgToPoint(Nz(Me.T_Moyenne, 0)) & ", " & _
Après ré exécution du code une fois le correctif apporté notre requête devient :

Code sql : Sélectionner tout - Visualiser dans une fenêtre à part
INSERT INTO [T_Eleves] (nomEleve, prenom, dateNais, moyenne, Redoublant)  VALUES ('DUPONT', 'Martin',#04/02/1986#,10.5, -1)

Aucun message d'erreur soulevé par l'éditeur, je vais de nature méfiante vérifier l'entrée dans la table. A mon grand désarroi, je constate que la date du 4 février 1986 est devenue le 2 Avril 1986. Une autre règle est donc de formater notre date en format américain ainsi cette portion de code à insérer dans le module dédié :

Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
Public Function Convert_DateUS_Short(laDate As Date) As String
  ' Formate la date passée en argument en date US.
    Convert_DateUS_Short = Chr(35) & Format(laDate, "mm-dd-yyyy") & Chr(35)  ' Date au format us
End Function
et la modification de la ligne dans la procédure de notre formulaire :

Code : Sélectionner tout - Visualiser dans une fenêtre à part
& Convert_DateUS_Short(Nz(Me.T_DateN, Date)) & "," & _
Une dernière vérification dans notre table pour vérifier que l'entrée est conforme et nous continuons notre saisie jusqu'à rencontrer un nouveau problème :

Nom : Formulaire2.JPG
Affichages : 358
Taille : 19,4 Ko

Lors de l'enregistrement, un message de ce type apparaît à l'écran :

Erreur de syntaxe (opérateur absent) dans l'expression "'D'ARCHAMBAULT', 'Martin',#09-09-1987#,14, -1)"
la quote étant reconnue par SQL comme délimiteur de chaîne de caractères, le nom d'archambault devra donc être protégé par un double simple quote. Nous écrirons donc une fonction de ce type :

Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
Function Protected_Quote(ChaineProtect As String) As String
  ' Fonction de protection visant à protéger les simple quotes et doublequotes dans les requetes SQL
  Protected_Quote = Replace(ChaineProtect, "'", "''")
  Protected_Quote = Replace(Protected_Quote, Chr(34), Chr(34) & Chr(34))
End Function
et modifierons notre appel dans la construction de notre fameuse requête :

Code : Sélectionner tout - Visualiser dans une fenêtre à part
"'" & Protected_Quote(Nz(Me.T_nom, "")) & "', " & _
En mode débogage voilà ce que nous donnerait la syntaxe de notre requête :

Code sql : Sélectionner tout - Visualiser dans une fenêtre à part
INSERT INTO [T_Eleves] (nomEleve, prenom, dateNais, moyenne, Redoublant)  VALUES ('D''ARCHAMBAULT', 'Martin',#09-09-1987#,14, 0)

Une dernière vérification dans notre table afin de vérifier la saisie de nos deux élèves :

Nom : Formulaire3.JPG
Affichages : 366
Taille : 26,6 Ko

Après lecture de ce billet, vous pourrez sereinement construire vos requêtes en VBA en toute quiétude. Les techniques décrites ci-dessus s'appliquent également aux requêtes de type Update (mise à jour).

le code complet du bouton clic après transformations et appels aux fonctions

Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
Private Sub B_Save_Click()
On Error GoTo Err_Commande
Dim R_Sql As String
      '
      If Not IsDate(Me.T_DateN) Then
        MsgBox "La date de Naissance est obligatoire", vbCritical, "Erreur Saisie"
        Me.T_DateN.SetFocus
        Exit Sub
      End If
      
      '
      R_Sql = "INSERT INTO [T_Eleves] " & _
        "(nomEleve, prenom, dateNais, moyenne, Redoublant) " & _
        " VALUES (" & _
        Protected_Quote(Nz(Me.T_nom, "")) & "', " & _
        Protected_Quote(Nz(Me.T_prenom, "")) & "', " & _
        & Convert_DateUS_Short(Nz(Me.T_DateN, Date)) & ", " & _
        VirgToPoint(Nz(Me.T_Moyenne, 0)) & ", " & _
        Nz(Me.T_Redoublant, "")  & _
        ")"
'
CurrentDb.Execute R_Sql, dbFailOnError
'
Exit Sub
Err_Commande:
    MsgBox Err.Description
End Sub
Bonne lecture à tous

JimboLion

Envoyer le billet « Résolution des problèmes fréquemment rencontrés dans l'écriture des requêtes écrites en vba » dans le blog Viadeo Envoyer le billet « Résolution des problèmes fréquemment rencontrés dans l'écriture des requêtes écrites en vba » dans le blog Twitter Envoyer le billet « Résolution des problèmes fréquemment rencontrés dans l'écriture des requêtes écrites en vba » dans le blog Google Envoyer le billet « Résolution des problèmes fréquemment rencontrés dans l'écriture des requêtes écrites en vba » dans le blog Facebook Envoyer le billet « Résolution des problèmes fréquemment rencontrés dans l'écriture des requêtes écrites en vba » dans le blog Digg Envoyer le billet « Résolution des problèmes fréquemment rencontrés dans l'écriture des requêtes écrites en vba » dans le blog Delicious Envoyer le billet « Résolution des problèmes fréquemment rencontrés dans l'écriture des requêtes écrites en vba » dans le blog MySpace Envoyer le billet « Résolution des problèmes fréquemment rencontrés dans l'écriture des requêtes écrites en vba » dans le blog Yahoo

Mis à jour 21/02/2016 à 11h26 par jimbolion

Catégories
Sans catégorie

Commentaires

  1. Avatar de Malick
    • |
    • permalink
    Salut jimbolion,

    Merci pour cette belle contribution :cool :

    Cordialement,
    Malick
  2. Avatar de jimbolion
    • |
    • permalink
    Citation Envoyé par milkoseck
    Salut jimbolion,

    Merci pour cette belle contribution :cool :

    Cordialement,
    Malick
    Merci Malick
  3. Avatar de Robert Camion
    • |
    • permalink
    JimboLion, Bonjour !

    Je découvre avec plaisir ce petit tutoriel, dont j'ai modestement contribué à te le faire écrire pour m'aider sur le droit chemin du VBA intégrant du SQL !
    Donc Merci !!!

    Je retiens donc :
    Un renvoi à la ligne est toutefois réalisable à l'aide de la notation: _
    Il est sage d'encadrer les symboles (&, :, etc.) par au-moins un espace.
    Toutes les valeurs de type texte doivent être encadrés par des quotes (simples ou doubles)
    Toutes les valeurs de type date doivent être encadrés par des #.

    Du coup, si je reprends le code final d'insertion:
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    Private Sub B_Save_Click()
    On Error GoTo Err_Commande
    Dim R_Sql As String
          '
          If Not IsDate(Me.T_DateN) Then
            MsgBox "La date de Naissance est obligatoire", vbCritical, "Erreur Saisie"
            Me.T_DateN.SetFocus
            Exit Sub
          End If
          
          '
          R_Sql = "INSERT INTO [T_Eleves] " & _
            "(nomEleve, prenom, dateNais, moyenne, Redoublant) " & _
            " VALUES (" & _
            "'" & Protected_Quote(Nz(Me.T_nom, "")) & "', " & _
            "'" & Protected_Quote(Nz(Me.T_prenom, 0)) & "'," & _
            "" & Convert_DateUS_Short(Nz(Me.T_DateN, Date)) & "," & _
            "" & VirgToPoint(Nz(Me.T_Moyenne, 0)) & ", " & _
            "" & Nz(Me.T_Redoublant, "") & "" & _
            ")"
    '
    CurrentDb.Execute R_Sql, dbFailOnError
    '
    Exit Sub
    Err_Commande:
        MsgBox Err.Description
    End Sub
    Si je traduits en français, on veut donc insérer les 5 valeurs des champs dans la table T_Eleves, un contrôle sur la date de naissance.

    Questions "détails":


    Pourquoi pour le nom, tu précises "" et pour le prénom 0 ?

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    Protected_Quote(Nz(Me.T_nom, "")
    Protected_Quote(Nz(Me.T_prenom, 0))
    Est ce que ca veut dire que l'on peut omettre le prénom, mais auquel cas il faudrait mettre un contrôle sur le nom avant l'insertion ? ( je comprends bien que le code n'est pas complet, mais c'est juste pour suivre....)

    Espaces or not espaces:
    C'est encore une remarque à la cxx, mais tant qu'à comprendre, j'en profite:

    Sur l'insertion du nom tu écris:
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
     "'" & Protected_Quote(Nz(Me.T_nom, "")) & "', " & _
    avec et pour le prénom:
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    "'" & Protected_Quote(Nz(Me.T_prenom, 0)) & "'," & _
    avec sans espace après la ,

    je suppose que la norme serait d'écrire à chaque fois ?


    Insertion date, pourquoi mettre double cotes devant alors qu'on est pas sur texte ?
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    "" & Convert_DateUS_Short(Nz(Me.T_DateN, Date)) & "," & _
    A la fin de la ligne précédente, on a bien qui si je suis bien, délimite la notion du prénom dans le code:
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    INSERT INTO [T_Eleves] (nomEleve, prenom, dateNais, moyenne, Redoublant)  VALUES ('D''ARCHAMBAULT', 'Martin',#09-09-1987#,14, 0)
    du coup, pourquoi ne pas écrire:
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    & Convert_DateUS_Short(Nz(Me.T_DateN, Date)) & "," & _
    ?
    Cette remarque vaut pour les 2 lignes suivantes (Moyenne et Redoublant)...

    Enfin, et j'en termine, pourquoi mettre le double cotes à la fin avant la dernière partenthèse ?
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    "" & Nz(Me.T_Redoublant, "") & "" & _
            ")"
    Ce qui me trouble (et je me trouble pour un rien), c'est qu'en VBA il me semble ..., "" = rien/nada, même pas un espace..,
    donc je vois pas l’intérêt, dans ces lignes de codes....


    Voilà voilà, merci pour ton retour éclairant

    Dis toi que si j'arrive à comprendre... beaucoup, si ce n'est tout le monde y arrivera

    RC
  4. Avatar de jimbolion
    • |
    • permalink
    Rc,

    oui quelques remarques judicieuses :

    il faut lire effectivement

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    Protected_Quote(Nz(Me.T_prenom, ""))
    pour cette partie, l'espace ne revêt que peu d'importance puisque le séparateur , est présent (question de visibilité en mode débogage)

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
     "'" & Protected_Quote(Nz(Me.T_nom, "")) & "', " & _
    pour cette partie :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    "" & Convert_DateUS_Short(Nz(Me.T_DateN, Date)) & "," & _
    Le double quotes n'a effectivement aucun intérêt puisque cela signifie chaîne vide, bien vu

    Je vais donc corriger ces quelques anomalies dans le billet initial.

    Amicalement

    JM
  5. Avatar de Robert Camion
    • |
    • permalink
    JimboLion, Bonjour

    merci pour la prise en compte de mes remarques qui m'aident à mieux comprendre la reprise du code SQL en VBA.

    Point clé:
    je note que l'utilisation de l'espace après la virgule est question de mise en forme et facilite le débugage...

    Ensuite, pour l'insertion de la date, tu confirmes qu'on peut mettre directement:
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    & Convert_DateUS_Short(Nz(Me.T_DateN, Date)) & ", " & _
    sans les "" avant ...

    Après relecture, il me semble même que le & est superflu...puisqu'il se trouve sur la ligne précédente...

    ce qui donnerai :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    Convert_DateUS_Short(Nz(Me.T_DateN, Date)) & ", " & _
    Siinon, dernier point, j'avais dans mon post précédent, demandé si cette remarque n'était pas pertinente aussi pour les 2 lignes suivantes:
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    "" & VirgToPoint(Nz(Me.T_Moyenne, 0)) & ", " & _
    "" & Nz(Me.T_Redoublant, "")  & _
    ")"
    du coup, qu'on pourrait écrire comme suit:
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    VirgToPoint(Nz(Me.T_Moyenne, 0)) & ", " & _
    Nz(Me.T_Redoublant, "")  & _
    ")"
    à te lire, et Merci Sensei !

    RC
    Mis à jour 18/02/2016 à 10h24 par Robert Camion
  6. Avatar de jimbolion
    • |
    • permalink
    Rc,

    oui encore bien vu !

    Jimbolion
  7. Avatar de Robert Camion
    • |
    • permalink
    JimboLion,
    merci pour ton suivi, mais je pense que je t'ai perdu en route avec toutes mes remarques...
    si j'ai bien suivi nos différents échanges, il me semble que tu as enlevé les devant le nom et le prénom et que ca ne fonctionnera pas ?
    enfin le devant la date semble superflu comme tu semblait l'approuver dans nos derniers échanges

    le code finale devrait être le suivant:

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    R_Sql = "INSERT INTO [T_Eleves] " & _
            "(nomEleve, prenom, dateNais, moyenne, Redoublant) " & _
            " VALUES (" & _
            "'" & Protected_Quote(Nz(Me.T_nom, "")) & "', " & _
            "'" & Protected_Quote(Nz(Me.T_prenom, "")) & "', " & _
            Convert_DateUS_Short(Nz(Me.T_DateN, Date)) & ", " & _
            VirgToPoint(Nz(Me.T_Moyenne, 0)) & ", " & _
            Nz(Me.T_Redoublant, "")  & _
            ")"

    est ce ton dernier mot JimBo ?


    RC
  8. Avatar de jimbolion
    • |
    • permalink
    Rc,

    oui tu as encore raison
  9. Avatar de Robert Camion
    • |
    • permalink
    Citation Envoyé par jimbolion
    Rc,

    oui tu as encore raison
    enfin, j'arrive à suivre,

    merci pour ce blog très bien fait !!!

    RC