jimbolion

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 :



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 :



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 :



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