Création et paramétrage dynamique de requêtes

La création d'une requête sous Visual Basic implique la manipulation d'objets de la base de données. Pour cela nous allons utiliser la librairie DAO qui permet de telles acrobaties.

Pour utiliser cette librairie vous devez ajouter la référence Microsoft DAO 3.6 à notre projet. Dans l'éditeur VBA, cliquez sur Outils, puis Références.

Cochez la ligne intitulée Microsoft DAO 3.6 Object Library.

Vous disposez maintenant d'une requête nommée qryClientParPays_modele qui contient le modèle de la requête à créer. Sur le bouton Valider du formulaire, il vous faut récupérer son code SQL, modifier le critère entre crochets, puis sauvegarder le tout dans une nouvelle requête. Enfin, vous devrez exécuter cette requête nouvellement créee afin d'afficher les données voulues.

Un problème majeur se pose alors. A la première exécution, vous n'allez rencontrer aucune difficulté. La requête va bien s'enregistrer sous le nom de qryClientParPays. Mais à la prochaine exécution, lorsque le programme souhaitera enregistrer la requête, une erreur sera levée. En effet, le nom qryClientParPays sera déjà utilisé. Il vous faudra donc procéder à un test d'existence avant d'enregistrer la nouvelle requête sans quoi le programme ne pourra fonctionner. Soit elle n'existe pas, dans ce cas, il faut la créer. Soit elle existe déjà, et dans ce cas, il faut la modifier

Pour vérifier qu'une requête existe déjà, il est nécessaire de rédiger une petite fonction à placer dans un module. Inutile de s'attarder sur le code. L'essentiel est que vous sachiez l'utiliser.

Public Function TesteExistenceRequete(strNomRequete As String) As Boolean
On Error GoTo err
Dim Db As DAO.Database
Dim QryTest As DAO.QueryDef
'initialise un objet représentant la base de données
Set Db = CurrentDb
'tente d'accéder à la requête
Set QryTest = Db.QueryDefs(strNomRequete)
TesteExistenceRequete = True
err:
End Function

En fait, le programme tente d'accéder à la requête dont le nom est passé en paramètre. S'il y parvient, la fonction retourne True (Vrai), sinon la fonction retourne son état d'origine soit False (Faux).

Procédez de la même façon que dans la fonction ci-dessus pour atteindre la requête modèle et en extraire le code SQL à l'aide de la propriété SQL de l'objet QueryDef.

Ainsi, dans la propriété Sur Clic du bouton Valider, créez une procédure événementielle contenant le code suivant :

Dim Db As DAO.Database
Dim QryModele As DAO.QueryDef
Dim strSQLModele As String
Set Db = CurrentDb
Set QryModele = Db.QueryDefs("qryClientParPays_modele")
strSQLModele = QryModele.SQL

A partir de là, il vous devez modifier le mot [criterepays] de la chaîne de caractères par la valeur contenue dans la zone de liste. Suivant le type de champ utilisé par le critère, il faut procéder de différentes manières.

- Le champ est de type texte (Le cas ici) :

Le code SQL devra se présenter de cette manière :

SELECT * FROM Clients
WHERE Pays="FRANCE";

Pour créer une telle chaîne sous VBA, il nous faut donc encadrer la valeur de la zone de liste par des guillemets, ce que fait la fonction Chr(34).

- Le champ est une date :

Access stocke les dates au format américain (mm/dd/yyyy) et les dates sont à encadrer par des #.

Exemple :

SELECT * FROM Clients
WHERE DateNaissance=#10/22/1976#

- Le champ est d'un autre type :

Aucune précaution n'est alors nécessaire et vous pouvez écrire le critère directement.

Complétez le code de la façon suivante :

strSQLModele = Replace(strSQLModele, "[criterepays]", Chr(34) & Nz(cboPays) & Chr(34))

Décortiquons cette ligne en détail :

La fonction Nz permet de traiter le cas où la valeur de la zone de liste serait nulle. Dans ce cas, au lieu de renvoyer NULL, la fonction Nz retourne "" (une chaîne vide). Nous encadrons ensuite la valeur retournée par cette fonction avec les guillemets fournies par la fonction Chr(34).

Enfin, la fonction Replace effectue le remplacement du critère par la nouvelle valeur obtenue

A ce stade le code SQL créé est prêt à être enregistré dans une nouvelle requête nommée qryClientParPays. Je vous rappelle toutefois que nous devons vérifier que la requête n'existe pas avant de l'enregistrer dans un nouvel objet DAO, sans quoi le programme générera un bug.

Le code devient alors :

'Si la requête existe déjà alors
If TesteExistenceRequete("qryClientParPays") Then
  'alors modifier le code de la requête
  Db.QueryDefs("qryClientParPays").SQL = strSQLModele
'Sinon
Else
  'Créer la nouvelle requête
  Db.CreateQueryDef "qryClientParPays", strSQLModele
End If

J'insiste sur le fait que la méthode CreateQueryDef possède comme premier paramètre le nom de la requête à créer et comme second le code représenté par la chaîne SQL correspondante. Cette méthode créée et enregistre une nouvelle requête dans la base de données.

Il ne reste plus qu'à ouvrir la nouvelle requête pour afficher le résultat. Pour cela, utilisez la méthode Docmd.OpenQuery.

DoCmd.OpenQuery "qryClientParPays"

Le code devient donc :

Private Sub btValider_Click()
Dim Db As DAO.Database
Dim QryModele As DAO.QueryDef
Dim strSQLModele As String
Set Db = CurrentDb
Set QryModele = Db.QueryDefs("qryClientParPays_modele")
strSQLModele = QryModele.SQL
'Effectue le remplacement du critere par la valeur
strSQLModele = Replace(strSQLModele, "[criterepays]", Chr(34) & Nz(cboPays) & Chr(34))
'Si la requête existe déjà alors
If TesteExistenceRequete("qryClientParPays") Then
  'alors modifier le code de la requête
  Db.QueryDefs("qryClientParPays").SQL = strSQLModele
'Sinon
Else
  'Créer la nouvelle requête
  Db.CreateQueryDef "qryClientParPays", strSQLModele
End If
'Ouvre la requête
DoCmd.OpenQuery "qryClientParPays"
'Ferme le formulaire
DoCmd.Close acForm, Me.Name
End Sub

Afin de parfaire le fonctionnement de cet exemple, vous pouvez ajouter une routine de gestion d'erreur afin d'alerter l'utilisateur lors d'un dysfonctionnement.

Private Sub btValider_Click()
On Error GoTo err
Dim Db As DAO.Database
Dim QryModele As DAO.QueryDef
Dim strSQLModele As String
Set Db = CurrentDb
Set QryModele = Db.QueryDefs("qryClientParPays_modele")
strSQLModele = QryModele.SQL
'Effectue le remplacement du critere par la valeur
strSQLModele = Replace(strSQLModele, "[criterepays]", Chr(34) & Nz(cboPays) & Chr(34))
'Si la requête existe déjà alors
If TesteExistenceRequete("qryClientParPays") Then
  'alors modifier le code de la requête
  Db.QueryDefs("qryClientParPays").SQL = strSQLModele
'Sinon
Else
  'Créer la nouvelle requête
  Db.CreateQueryDef "qryClientParPays", strSQLModele
End If
DoCmd.OpenQuery "qryClientParPays"
'Ferme le formulaire
DoCmd.Close acForm, Me.Name
Exit Sub
err:
MsgBox "Une erreur est survenue", vbCritical, "Sélection d'un client"
End Sub

De même, les puristes préféreront utiliser des variables pour stocker le nom des requêtes. Ce qui pourrait donner :

Private Sub btValider_Click()
On Error GoTo err
Dim Db As DAO.Database
Dim QryModele As DAO.QueryDef
Dim strSQLModele As String
Dim strNomQueryModele As String
Dim strNomQuery As String
strNomQueryModele = "qryClientParPays_modele"
strNomQuery = "qryClientParPays"
Set Db = CurrentDb
Set QryModele = Db.QueryDefs(strNomQueryModele)
strSQLModele = QryModele.SQL
'Effectue le remplacement du critere par la valeur
strSQLModele = Replace(strSQLModele, "[criterepays]", Chr(34) & Nz(cboPays) & Chr(34))
'Si la requête existe déjà alors
If TesteExistenceRequete(strNomQuery) Then
  'alors modifier le code de la requête
  Db.QueryDefs(strNomQuery).SQL = strSQLModele
'Sinon
Else
  'Créer la nouvelle requête
  Db.CreateQueryDef strNomQuery, strSQLModele
End If
DoCmd.OpenQuery strNomQuery
'Ferme le formulaire
DoCmd.Close acForm, Me.Name
Exit Sub
err:
MsgBox "Une erreur est survenue", vbCritical, "Sélection d'un client"
End Sub

Afin d'utiliser cette table pour lire le SQL des requêtes il est nécessaire de modifier le code du bouton Valider de la sorte :

Private Sub btValider_Click()
On Error GoTo err
Dim Db As DAO.Database
Dim QryModele As DAO.QueryDef
Dim RstTable As DAO.Recordset
Dim strSQLModele As String
Dim strNomRqt As String
'Instancie la base de données
Set Db = CurrentDb
'Ouvre la table des requetes sur l'enregistrement
'dont le nom de la requête est "qryClientParPays"
strNomRqt = "qryClientParPays"
Set RstTable = Db.OpenRecordset("SELECT * FROM tblRequete WHERE NomRqt=" & Chr(34) & strNomRqt & Chr(34))
'Si la requête est trouvée dans la table,
If Not RstTable.EOF Then
  'Récupère le code correspondant
  strSQLModele = RstTable.Fields("CodeRqt")
  'Effectue le remplacement du critere par la valeur
  strSQLModele = Replace(strSQLModele, "[criterepays]", Chr(34) & Nz(cboPays) & Chr(34))
  'Si un objet requête existe déjà alors
  If TesteExistenceRequete(strNomRqt) Then
    'alors modifier le code de la requête
    Db.QueryDefs(strNomRqt).SQL = strSQLModele
    'Sinon
  Else
    'Créer la nouvelle requête
    Db.CreateQueryDef strNomRqt, strSQLModele
  End If
  DoCmd.OpenQuery "qryClientParPays"
  'Ferme le formulaire
  DoCmd.Close acForm, Me.Name
'Sinon
Else
'Avertir que le code SQL n'est pas disponible
MsgBox "Impossible de trouver la requête : " & strNomRqt & " dans la table des requêtes"
End If

Exit Sub
err:
MsgBox "Une erreur est survenue" & vbCrLf & err.Description, vbCritical, "Sélection d'un client"
End Sub

Quelques explications :

Pour lire les données d'une table nous devons utiliser des objets de type Recordset (littéralement : jeu d'enregistrements). Il s'agit ni plus ni moins du résultat d'une requête et chaque enregistrement est stocké en mémoire. Lorsque le Recordset est vide à son ouverture, sa propriété EOF est égale à True. Cette propriété signifie en fait que l'on se trouve après le dernier enregistrement, ce qui est logique si le jeu est vide. Enfin, il est possible d'accéder à un champ du Recordset pour la ligne courante à l'aide de la syntaxe : RstTable.Fields("Nomduchamp")

Une fois le code SQL lu et stocké dans une variable (strSQLModele), le principe de création de la requête reste le même que celui utilisé pour les requêtes modèles tout en gardant à l'esprit que la requête à créer peut déjà exister.

Vous trouverez un cours complet sur l'accès aux données via DAO à l'adresse suivante : https://warin.developpez.com/TutorielDAO

Pour que cela fonctionne il suffit maintenant de stocker notre requête modèle dans la table.

Il ne reste plus qu'à tester le formulaire en saisissant de nouveau un pays...

... et constater que cela fonctionne.