Moteur de recherche VBA Access avec ou sans accents

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Moteur de recherche gérant les accents

Les accents et caractères latins d'une manière générale sont problématiques lorsqu'il est question d'entreprendre des comparaisons de textes à la cédille ou à l'accent près. Grâce aux travaux précédents, nous allons ici monter un moteur de recherche VBA Access complètement insensible aux caractères accentués.



Sur l'exemple illustré par la capture, nous évoluons sur un formulaire tabulaire. L'utilisateur tape des termes de recherche dans une zone de texte située dans l'entête. S'il tape ces mots clés sans accents, tous les enregistrements concordants, avec ou sans accents, sont automatiquement extraits à validation. Il en va de même s'il tape les termes avec accents alors que les champs de recherche n'en proposent pas. La comparaison est réalisée sur les mots purgés de ces spécificités latines. De plus, vous pouvez remarquer la présence d'une case à cocher intitulée Stricte. Elle permet d'engager des recherches approximatives, lorsqu'aucun enregistrement ne recoupe tous les termes à la fois et lorsqu'elle est décochée.

Base de données Access à télécharger
Pour la construction de l'application, nous proposons d'appuyer le développement sur une base de données offrant ce formulaire de recherche ainsi que le code des précédents volets pour purger les accents des chaînes dans les comparaisons.

• Télécharger le fichier compressé recherche-accents.rar en cliquant sur ce lien,
• Le décompresser dans le dossier de votre choix,
• Double cliquer sur le fichier résultant pour l'ouvrir dans Access,
• Puis, cliquer sur le bouton Activer le contenu du bandeau de sécurité,

Le volet de navigation sur la gauche de l'écran indique que cette petite base de données est constituée de trois tables et d'un seul formulaire. La table societes2 n'est autre qu'une copie de la table societes.

• Double cliquer sur le formulaire f_recherche pour l'exécuter,

Comme vous pouvez le voir, il s'agit bien d'un formulaire tabulaire livrant tous les enregistrements linéairement, les uns en dessous des autres. Sa source de données est la table nommée recherche. Mais les mots clés tapés dans la zone de saisie en haut à droite, doivent être recherchés dans la table societes. Le code VBA Access doit se charger, à chaque nouvelle demande, de purger la table recherche et de la remplir uniquement des enregistrements concordants avec les termes demandés.

La gestion des accents en VBA
Nous l'avons évoqué, cette base de données héberge le développement VBA des astuces précédentes. Il permet entre autres de purger les champs des enregistrements de leurs accents pour favoriser les correspondances dans les recherches. C'est ce que nous proposons de constater.

• A gauche du ruban Accueil, cliquer sur la flèche du bouton Affichage,
• Dans les propositions, choisir le mode Création,
• Sur le formulaire en conception, cliquer sur le bouton Ok pour le sélectionner,
• Dès lors, activer l'onglet Evénement de sa feuille de propriétés,
• Puis, cliquer sur le petit bouton associé à son événement Au clic,

Nous basculons ainsi dans l'éditeur VBA Access entre les bornes de la procédure événementielle Ok_Click.

Des tableaux sont tout d'abord déclarés pour recenser les lettres accentuées et ces mêmes lettres sans accents :

lesAccents = Split("à, á, â, ã, ä, å, ç, è, é, ê, ë, ì, í, î, ï, ð, ò, ó, ô, õ, ö, ù, ú, û, ü, ý, ÿ", ", ")
lesLettres =Split("a, a, a, a, a, a, c, e, e, e, e, i, i, i, i, o, o, o, o, o, o, u, u, u, u, y, y", ", ")

Les objets de base de données sont initialisés pour atteindre la table societes à scruter :

Set base = CurrentDb()
Set enr = base.OpenRecordset("societes")

Dès lors, une boucle est entreprise pour parcourir chaque enregistrement de la table :

With enr
.MoveFirst

Do
...

Dans cette première boucle, une autre boucle est déclenchée mais cette fois pour purger tous les accents des champs pour l'enregistrement en cours d'analyse :

For i = 0 To UBound(lesAccents)
nom = Replace(nom, lesAccents(i), lesLettres(i))
act = Replace(act, lesAccents(i), lesLettres(i))
dep = Replace(dep, lesAccents(i), lesLettres(i))
ville = Replace(ville, lesAccents(i), lesLettres(i))
cp = Replace(cp, lesAccents(i), lesLettres(i))
Next i

Pour cela, c'est la fonction Replace qui est à l'honneur. Elle remplace chaque lettre avec accent par son homologue sans accents, grâce aux tableaux de variables passés en deuxième et troisième arguments.

C'est ici que nous devons entre autres intervenir, pour rechercher les mots clés tapés, purgés de leurs accents, dans chacun de ces champs eux-mêmes nettoyés de leurs accents.

Les variables des mots clés
Pour débuter l'adaptation du code VBA à la recherche des correspondances, nous devons déclarer deux variables permettant de gérer les mots clés tapés par l'utilisateur. La première doit récupérer l'expression fidèlement saisie. La seconde doit isoler chaque mot clé dans un tableau de variables. C'est ainsi que nous pourrons les passer tous en revue.

• Dans la partie déclarative, ajouter les deux nouvelles déclarations suivantes :

...
Dim base As Database: Dim enr As Recordset
Dim i As Integer: Dim lid As Integer
Dim nom As String: Dim act As String: Dim dep As String
Dim ville As String: Dim cp As String: Dim score As Byte
Dim motsCles As String: Dim lesMots() As String
Dim requete As String: Dim lesAccents() As String: Dim lesLettres() As String
...

La première est une variable classique de type texte (String) pour réceptionner la chaîne des mots clés. La seconde est un tableau de variables pour isoler chacun d'entre eux dans une rangée différente.

Vider la table source
Maintenant et avant de poursuivre, nous devons certes récupérer les mots clés tapés dans la variable motsCles. Mais nous devons aussi et surtout vider la table recherche de ses précédents enregistrements. Nous l'avons dit, c'est elle qui sert de source de données au formulaire tabulaire. Nous devons donc la purger avant d'y insérer les nouveaux enregistrements concordants avec la recherche.

• Après les variables de base et avant le bloc With, ajouter les instructions suivantes :

...
Set base = CurrentDb()
Set enr = base.OpenRecordset("societes")

motsCles = Cherche.Value
requete = "DELETE FROM recherche"
base.Execute requete

With enr
.MoveFirst
...

Grâce à la propriété Value de l'objet TextBox (Cherche), nous stockons les mots clés tapés dans la variable motsCles. Ensuite, nous initions une syntaxe Sql pour supprimer tous les enregistrements de la table recherche. Puis, nous l'exécutons grâce à la méthode Execute de l'objet base.

Purger les accents des mots clés
Désormais, pour une comparaison sans faille, nous devons remplacer les accents dans les mots clés, comme nous l'avons fait pour les contenus des champs de l'enregistrement en cours d'analyse. Pour cela, nous devons exploiter la boucle parcourant toutes les lettres du tableau des lettres accentuées.

• Dans la boucle For à l'intérieur de la boucle Do, ajouter l'instruction VBA suivante :

...
For i = 0 To UBound(lesAccents)
nom = Replace(nom, lesAccents(i), lesLettres(i))
act = Replace(act, lesAccents(i), lesLettres(i))
dep = Replace(dep, lesAccents(i), lesLettres(i))
ville = Replace(ville, lesAccents(i), lesLettres(i))
cp = Replace(cp, lesAccents(i), lesLettres(i))
motsCles = Replace(motsCles, lesAccents(i),lesLettres(i))
Next i
...

C'est une fois encore la fonction Replace, exploitant les deux tableaux de variables, parcourus lettre à lettre, qui permet de nettoyer les mots clés des accents qu'ils contiennent potentiellement.

Chercher les correspondances dans la table
Maintenant, il est question d'engager les recherches. Deux méthodes existent. La première est stricte. L'enregistrement, au travers de tous ses champs, doit recouper tous les mots clés tapés. La seconde est permissive. Elle accorde que seul l'un des mots clés soit trouvé pour que l'enregistrement soit jugé comme concordant. Pour cela, nous devons parcourir chacun des mots clés tapés et chercher leur présence dans chacun des champs de l'enregistrement en cours d'analyse.

• Sous la boucle précédente, ajouter les instructions VBA suivantes :

...
lesMots = Split(motsCles, " ")
score = 0
For i = 0 To UBound(lesMots)
If (InStr(1, nom, lesMots(i)) > 0 Or _
InStr(1, act, lesMots(i)) > 0 Or _
InStr(1, dep, lesMots(i)) > 0 Or _
InStr(1, ville, lesMots(i)) > 0 Or _
InStr(1, cp, lesMots(i)) > 0) Then score = score + 1
Next i
...

Nous stockons chaque mot clé purgé des accents dans une rangée indépendante du tableau de variables, grâce à la fonction Split utilisant l'espace comme délimiteur. Nous initialisons la variable score à zéro. Puis, nous parcourons tous les mots clés (For i = 0 ToUBound(lesMots)). Dès lors, nous engageons une instruction conditionnelle non exclusive (Or). Si le mot clé en cours d'analyse est trouvé dans l'un ou l'autre champ de l'enregistrement étudié, nous incrémentons la variable score d'une unité. Notez que le symbole de l'Under score (_) permet de briser la syntaxe pour la construire sur plusieurs lignes.

Insérer les résultats de recherche
Lorsque des enregistrements sont considérés comme correspondants, nous devons les ajouter tour à tour dans la table recherche. Rappelons-le, c'est elle qui nourrit le formulaire tabulaire. Mais attention, deux cas de figure se suggèrent une fois encore. Soit l'utilisateur a coché la case Stricte, soit il l'a décochée.

• A la suite du code précédent et avant la méthode MoveNext, ajouter l'instruction suivante :

...
If ((stricte.Value = -1 And score = UBound(lesMots) + 1) Or (stricte.Value = 0 And score > 0)) Then

End If

score = 0
...

Soit la méthode stricte est enclenchée (stricte.Value = -1) et nous découvrons que tous les mots clés ont été trouvés (And score = UBound(lesMots) + 1). Soit la méthode permissive est activée (stricte.Value = 0) et nous constatons qu'au moins un mot clé est trouvé (And score > 0).

Remarque : La fonction UBound tient compte du premier élément situé à l'indice 0 d'un tableau. Donc, pour un tableau de 2 mots clés, elle retourne le chiffre 1. C'est la raison pour laquelle nous incrémentons sa valeur afin d'honorer correctement le test. Notez de plus, la réinitialisation de la variable score à zéro avant de déplacer l'étude sur l'enregistrement suivant (MoveNext).

Lorsque l'un de ces critères est vérifié, nous devons engager une requête d'ajout (INSERT INTO). Son rôle est d'insérer chaque champ de l'enregistrement concordant dans chaque champ correspondant de la table recherche. C'est ainsi que les enregistrements vont être ajoutés tour à tour dans la source de données du formulaire.

• Dans les bornes de l'instruction conditionnelle, ajouter les deux lignes VBA suivantes :

...
requete = "INSERT INTO recherche(societes_id, societes_nom, societes_activite, societes_departement, societes_ville, societes_cp) VALUES (" & lid & ", '" & .Fields("societes_nom").Value & "', '" & .Fields("societes_activite").Value & "', '" & .Fields("societes_departement").Value & "', '" & .Fields("societes_ville").Value & "', '" & .Fields("societes_cp").Value & "')"
base.Execute requete
...

Nous construisons la syntaxe de la requête Sql dans la variable requete. Nous insérons (INSERT INTO) dans la table recherche et pour tous ses champs énumérés, toutes les valeurs (VALUES) des champs correspondants pour l'enregistrement concordant. Notez que nous récupérons les informations non purgées de leurs accents (.Fields("societes_nom").Value). De même, notez l'alternance des simples et doubles côtes ("', '") lorsqu'il s'agit d'encadrer les données de type texte.

Actualiser la source de données
Pour finir et à l'issue du traitement de la boucle, nous devons actualiser la source de données pour rafraîchir l'affichage du formulaire tabulaire. C'est ainsi qu'il va livrer automatiquement tous les enregistrements répondant favorablement à la demande des mots clés formulés par l'utilisateur.

• Après la boucle et le bloc With, ajouter l'instruction VBA suivante :

...
Loop While Not .EOF
End With

DoCmd.Requery

enr.Close
base.Close
...

C'est en effet la méthode Requery de l'objet DoCmd (DoCommand) qui permet de rafraîchir les liaisons et sources de données.

Recherche avec ou sans accents
Il est temps de tester le bon fonctionnement du moteur de recherche devant gérer les spécificités des caractères latins.

• Enregistrer les modifications (CTRL + S) et basculer sur le formulaire (ALT + Tab),
• Exécuter le formulaire, par exemple avec la touche F5 du clavier,
• Taper les mots clés de recherche suivants : Hotel restaurant toulon,
• Puis, cliquer sur le bouton Ok,

Comme vous pouvez le voir, seuls trois enregistrements sont parfaitement concordants. En effet, la case Stricte est cochée par défaut. Les mots clés Hotel et Restaurant sont trouvés dans le champ de l'activité. Et bien que nous n'ayons pas mis d'accent dans le mot Hôtel, la correspondance a été trouvée grâce à notre traitement VBA. De plus, le mot clé Toulon est trouvé dans le champ de la ville quant à lui.

Bien sûr, si vous décochez la case Stricte et que vous relancez la recherche, vous obtenez un bien plus grand nombre de résultats mais bien moins pertinents.

En essayant une recherche intégrant les accents comme : restaurant drôme, vous obtenez bien tous les restaurants de la Drôme. Pourtant et inversement au cas précédent, le département de la Drôme est codé étonnamment sans accents dans cette base de données. Nous avons donc réussi à concevoir un moteur de recherche en VBA Access, capable de réaliser les correspondances sur les termes de recherche et ce, quelles que soient les spécificités des caractères latins rencontrés.

Les adaptations que nous avons apportées au code VBA sont inscrites en gras ci-dessous :

Private Sub Ok_Click()
Dim base As Database: Dim enr As Recordset
Dim i As Integer: Dim lid As Integer
Dim nom As String: Dim act As String: Dim dep As String
Dim ville As String: Dim cp As String: Dim score As Byte
Dim motsCles As String: Dim lesMots() As String
Dim requete As String: Dim lesAccents() As String: Dim lesLettres() As String

lesAccents =Split("à, á, â, ã, ä, å, ç, è, é, ê, ë, ì, í, î, ï, ð, ò, ó, ô, õ, ö, ù, ú, û, ü, ý, ÿ", ", ")
lesLettres =Split("a, a, a, a, a, a, c, e, e, e, e, i, i, i, i, o, o, o, o, o, o, u, u, u, u, y, y", ", ")

Set base = CurrentDb()
Set enr = base.OpenRecordset("societes")

motsCles = Cherche.Value
requete = "DELETE FROM recherche"
base.Execute requete

With enr
.MoveFirst

Do
cp = ""
lid = .Fields("societes_id").Value
nom = .Fields("societes_nom").Value
act = .Fields("societes_activite").Value
dep = .Fields("societes_departement").Value
ville = .Fields("societes_ville").Value
cp = .Fields("societes_cp").Value

For i = 0 To UBound(lesAccents)
nom = Replace(nom, lesAccents(i), lesLettres(i))
act = Replace(act, lesAccents(i), lesLettres(i))
dep = Replace(dep, lesAccents(i), lesLettres(i))
ville = Replace(ville, lesAccents(i), lesLettres(i))
cp = Replace(cp, lesAccents(i), lesLettres(i))
motsCles = Replace(motsCles, lesAccents(i), lesLettres(i))
Next i

lesMots = Split(motsCles, " ")
score = 0
For i = 0 To UBound(lesMots)
If (InStr(1, nom, lesMots(i)) > 0 Or _
InStr(1, act, lesMots(i)) > 0 Or _
InStr(1, dep, lesMots(i)) > 0 Or _
InStr(1, ville, lesMots(i)) > 0 Or _
InStr(1, cp, lesMots(i)) > 0) Then score = score + 1
Next i

If ((stricte.Value = -1 And score = UBound(lesMots) + 1) Or (stricte.Value = 0 And score > 0)) Then
requete = "INSERT INTO recherche (societes_id, societes_nom, societes_activite, societes_departement, societes_ville, societes_cp) VALUES (" & lid & ", '" & .Fields("societes_nom").Value & "', '" & .Fields("societes_activite").Value & "', '" & .Fields("societes_departement").Value& "', '" & .Fields("societes_ville").Value & "', '" & .Fields("societes_cp").Value & "')"
base.Execute requete
End If

score = 0

.MoveNext
Loop While Not .EOF
End With

DoCmd.Requery

enr.Close
base.Close

Set enr = Nothing
Set base = Nothing

End Sub