Guide d'optimisation de vos applications Microsoft ACCESS

Vos postes clients et serveur doivent toujours être à jour. Les Service packs et Service Releases corrigent des problèmes et leur déploiement n'est pas facultatif.

N'hésitez pas à compacter souvent l'application pendant le développement aussi lorsqu'elle est en production.

Lorsque vous créez ou modifiez une application, la taille de l'application a tendance à augmenter de deux voire trois fois la taille normale. Avec les tables, la taille augmente avec l'ajout de données, ce qui est logique, mais également lors de modifications ou de suppressions.
Pour récupérer la place, il est obligatoire de compacter régulièrement vos fichiers. En principe, un fichier applicatif n'a pas besoin d'un compactage aussi fréquent sauf dans le cas où il y des modifications importantes du volume de données.
Hormis le gain de place, il y a celui de la performance. En effet, lors du compactage les requêtes sont recompilées et les statistiques Rushmore sont recalculées. Ce qui impacte directement la rapidité des requêtes.
Les données sont regroupées en fonction des index et classées dans les pages de données contiguës. Ceci dans le but d'avoir toujours le plus petit nombre de pages à consulter.
Le compactage peut être fait à la fermeture de l'application ou à l'aide d'un raccourci lancé dans le Planificateur de tâches.

II-B-1. Compacter avec le Planificateur de tâches▲

Le compactage sur de gros fichiers peut durer plusieurs minutes ; c'est pour cela qu'il vaut mieux lancer les compactages lorsque le poste n'est pas sollicité.
Le Planificateur de tâches présent sur tous les systèmes Windows est idéal pour lancer cette commande.

Commencez par créer un raccourci de compactage. Voir le tutoriel Raccourci de compactage
Le plus dur est fait ! Ouvrez le Planificateur de tâches accessible par le menu Démarrer/Programmes/Accessoires/Outils systèmes Tâches planifiées

Créez une nouvelle tâche en cliquant sur la ligne Création d'une tâche planifiée. Un assistant vous guide tout au long de la création.

Celle-ci se fait en plusieurs phases simples.

• Choix du programme, c'est là que vous choisissez le raccourci.
• Choix de la fréquence, évitez Une seule fois, Au démarrage… et À l'ouverture…
• Suivant la fréquence choisie (mensuel, hebdomadaire ou quotidien) vous devrez sélectionner une heure, un jour, un mois.
  

Si la période est à votre discrétion, l'heure doit être méticuleusement choisie lorsque vous compactez une base située sur un serveur. En effet, les serveurs sont sollicités par les utilisateurs, par la maintenance et surtout par les sauvegardes. Choisissez une heure qui ne gênera pas les tâches existantes.

• Entrez le nom du compte Windows et le mot de passe.

Ce compte devra avoir les autorisations nécessaires pour exécuter cette commande. En principe, si vous arrivez à lancer manuellement le compactage avec le raccourci, le Planificateur de tâches ne rencontrera pas de problème.

Vous pouvez planifier une tâche à partir d'un poste client. Cependant, lancer la commande sur le poste contenant le fichier donne toujours de meilleurs résultats.

Vous avez accès à d'autres paramètres importants en cliquant sur la case à cocher Afficher les paramètres avancés.

Pour choisir la bonne fréquence de compactage, observez l'évolution de la taille de votre base de données sur plusieurs jours.

Veillez à ce que toutes les applications soient bien fermées sinon le compactage échouera. Il existe une méthode à mettre en place sur votre application pour qu'elle se ferme en cas d'inactivité.

Méthode de déconnexion temporiséeFAQ : méthode de déconnexion temporisée.

Avec les nouvelles versions d'ACCESS, il existe un analyseur de performances et un analyseur de tables. N'hésitez pas à les utiliser. Leurs recommandations sont souvent pertinentes.
Suivez toujours les recommandations sur les index, celles-ci sont données en fonction des besoins de Rushmore.

Ce tutoriel n'a pas pour objet d'apprendre la conception des modèles de données. Nous ferons donc l'impasse sur l'analyse des tables.
Dans l'analyseur de performances, cliquez sur l'onglet Tous types d'objets, sur le bouton Sélectionner tout puis OK.

Vous obtenez un rapport clair sur les propositions d'optimisation déclinées en trois groupes. Recommandation, Suggestion, Idée.
Lorsque vous cliquez sur Optimiser, les propositions sont effectuées et mise en statut Fixé.

Windows permet la compression et le cryptage de vos dossiers. Chacune de ces opérations demande du temps à la machine, aussi bien dans un sens que dans l'autre. Il vaut mieux s'abstenir d'une telle pratique.

Notez qu'ACCESS permet également le cryptage des données, ce qui ralentit également l'application. Suivant les versions d'ACCESS cela s'appelle le cryptage ou le codage. Il s'agit de la même chose.

• Mettez les données sur un serveur et l'interface sur chaque poste client lorsque vous êtes dans un environnement multiutilisateur.
• Compilez MDE si vous le pouvez. La compilation MDE accélère l'exécution du code VBA, tout en protégeant les sources de l'application. Elle n'a aucun effet sur les données.
• Évitez de mettre l'application et les données dans des sous-sous-sous-répertoires et évitez l'utilisation des noms longs pour les répertoires et les fichiers.
• Si vous ne partagez pas vos applications, laissez-les en local et ouvrez-les en Mode Exclusif.
• Gardez à l'esprit que lorsque vous utilisez le moteur JET, celui-ci est de type fichier/serveur, c'est donc le poste client qui réalise tous les travaux. Le serveur ne fait que mettre un fichier à disposition. Ces transferts de données peuvent surcharger la bande passante du réseau.

• Développez toujours vos applications en local.
• Faites souvent des sauvegardes.
  Heureusement très rare avec les versions actuelles, des problèmes peuvent survenir pendant le développement d'une application rendant le fichier applicatif instable ou carrément illisible, seule la sauvegarde vous protègera efficacement.
• N'hésitez pas à compacter pendant le développement, de préférence avant la sauvegarde.
• Préférez l'utilisation de VBA aux macros, le gain de rapidité n'est plus à prouver.
• N'utilisez pas la réplication pour mettre à jour une IHM en exploitation ou comme méthode de sauvegarde.

• Évitez les champs OLE si vous pouvez vous en passer.
• Dimensionnez et typez correctement vos champs.
• Pour vos données statiques ou à très faible mise à jour, préférez des tables locales.
• Évitez de stocker des images ou fichiers dans les tables (champs OLE).
• Évitez d'activer les sous-feuilles de données dans les tables.
• Organisez correctement vos tables. Il vaut mieux une multitude de tables de faible volume qu'une seule de gros volume.
• Évitez la redondance inutile d'informations. De rares cas rendent obligatoire cette pratique.
  En général, toutes informations susceptibles de changer dans le temps, mais dont la redondance permet de figer sa valeur à l'instant de son utilisation.
  Les cas classiques sont :
  les adresses clients, les taux de TVA et les prix d'articles utilisés dans les factures et les commandes.

• Pensez à indexer, mais uniquement les champs nécessaires. Trop d'index tue les performances et nuit aux accès concurrents.
• Dans le cas de tables en relation, pensez à indexer les deux côtés de la relation.
• Si les données d'un champ doivent être fréquemment affichées triées, pensez à l'indexer.
• Dans le cas d'une base de données ayant peu ou pas de mise à jour, indexez tous les champs servant dans les relations et les restrictions (utilisés dans des conditions Where des requêtes).
  Ceci améliorera notablement les statistiques Rushmore.
• La permission des valeurs Null dans un champ peut empêcher dans certains cas l'optimisation Rushmore.

• Le générateur de requête (QBE) optimise et corrige les requêtes pour les bases ACCESS, n'hésitez pas à recourir à ses services, même si vous vous sentez un pro de SQL. Chaque langage SQL a ses particularités, celui de Microsoft ACCESS n'échappe pas à la règle.
• Pensez à l'imbrication des SELECT. Une solution tout SQL est souvent préférable à une solution VBA.
• Évitez de sélectionner des champs inutiles, ne remontez que les colonnes et les enregistrements dont vous avez besoin.
• Lors de création de requêtes paramétrées avec jointure, testez si l'exécution est plus rapide lorsque vous placez les critères du côté un ou du côté plusieurs de la relation.
• Évitez les formules dans les sous-requêtes.
• Évitez de faire des recherches sur des champs Mémo.
• Évitez de mettre des liens vers des objets de l'IHM dans vos requêtes paramétrées. Préférez une solution VBA pour intégrer le paramètre.
• Évitez l'imbrication de requête avec NOT IN() ; préférez une requête du type non correspondance.
• Évitez les critères sur des colonnes calculées.
• Pour compter le nombre d'enregistrements, utilisez COUNT(*) au lieu de COUNT([champ]).
• Évitez les fonctions de regroupement de domaine (Dlookup, Dcount, Dsum…) ; utilisez plutôt une sous-requête.
• La fonction Vraifaux() -Iif()- n'est pas optimisable, utilisez si possible cette fonction dans un formulaire ou un état.
• Évitez le Group By sur un trop grand nombre de colonnes.
• Évitez le Group By après une jointure.
• Évitez d'insérer plusieurs fois la même table dans le QBE.

III-E-1. L'optimisation Rushmore en détail▲

Rushmore fait appel aux index et à leur fusion pour optimiser les objets requêtes d'ACCESS. Cette technologie existe depuis la version 2.0 du moteur JET. Elle est issue de Foxpro.
L'optimisation Rushmore est employée sur des requêtes impliquant des restrictions (AND, OR, BETWEEN, =, LIKE, IN) multicolonnes indexées.

LIKE est un opérateur un peu à part puisqu'il bénéficie de l'optimisation uniquement dans les cas suivants :

Optimisé

Sélectionnez

                    LIKE "mavaleur"
                    LIKE "mavaleur*"

Si IN est optimisé, Not IN ne l'est absolument pas !

Rushmore utilise un système de statistique pour déterminer la méthode la plus rapide pour retrouver un volume de données.
Les requêtes utilisant la fonction Count(*) et les index multiples sont les exemples les plus courants des capacités d'optimisation de Rushmore, mais pas seulement.

Rushmore ayant été porté de Microsoft FOXPRO, il fonctionne également avec les tables Dbase et Foxpro indexées liées, mais pas au travers d'ODBC.

Les listes suivantes indiquent les différents paramètres qui sont évalués par Rushmore pour choisir la stratégie la plus performante.

Première analyse

• Le nombre d'enregistrements dans la table.
• Le nombre de pages de données dans la table (plus il y a de pages plus le coût est élevé).
• L'endroit de stockage de la table (locale, liée, Jet, Foxpro, ODBC, ISAM…).
• Le jeu d'enregistrements est modifiable ou non.
• Les index de la table (tableau suivant).

Dans le cas d'un jeu d'enregistrements modifiables (Dynaset), Rushmore choisit la stratégie qui retourne le plus rapidement la 1re page de données. Cela peut se faire au détriment du temps total pour remonter la totalité des enregistrements.

Analyse des index des tables

• Index unique ou non.
• Nombre de pages d'index : plus il y a de pages, plus le coût en temps est élevé.
• La valeur Null est permise : cela peut annuler la fusion d'index.
• Analyse de jointure des tables.

Une fois les paramètres évalués, Rushmore compare plusieurs techniques d'optimisation de recherche. La plus performante en temps est choisie.

Type de stratégie Rushmore

• Itération imbriquée : cette technique est utilisée lorsqu'il y a de gros volumes de données dans les tables.
  
• Indexation : parcourt les lignes de la 1re table et recherche les enregistrements connexes dans la 2e table.
  Utilisée avec peu de données dans la 2e table ou qu'aucune donnée de la 2e table n'est à afficher par la requête.
  
• Recherche : idem que la précédente, sauf que le tri et la restriction se font avant la jointure.
  Utilisée avec un faible volume de données dans la seconde table et jointure non indexée.
  
• Fusion de jointure : trie les deux tables par la jointure et combine les deux tables.
  Utilisée avec un gros volume de données triées sur le champ servant de jointure.
  
• Fusion d'index : idem que la précédente, sauf que les index servent au classement des deux tables.
  Utilisée lorsque les colonnes de la jointure sont indexées, sans valeur Null autorisée dans l'une des colonnes, ou s'il y a plus d'une colonne servant de jointure.

III-E-1-a. Observer l'optimisation Rushmore avec JetShowplan▲

Une option non documentée permet d'observer le travail de l'optimisateur Rushmore. Cette option permet de générer un fichier nommé showplan.out contenant l'ensemble des analyses Rushmore de chaque requête du fichier.
Pour qu'ACCESS génère ce fichier, il faut procéder à une modification de la base de registre.

La modification de la base de registre comporte toujours des risques. Procédez avec précaution !

Pour ACCESS 95 à 2003

1. Ouvrez la base de registre avec Regedit.
2. Allez dans le registre \\HKEY_LOCAL_MACHINE\SOFTWARE\MICROSOFT\Jet\x.x\Engines\Debug ou x.x représente la version du moteur Jet que vous utilisez.
   Actuellement la dernière version de JET est la 4.0.
3. En principe Debug n'est pas créé. Créez cette clef.
4. À l'intérieur, créez la valeur JETSHOWPLAN de type Chaîne.
5. Paramétrez la valeur ON

Pour ACCESS 2007

1. Ouvrez la base de registre avec Regedit.
2. Allez dans le registre HKLM\SOFTWARE\Microsoft\Office\12.0\Access Connectivity Engine\Engines\Debug
3. En principe, Debug n'est pas créé. Créez cette clef.
4. À l'intérieur, créez la clef JETSHOWPLAN de type Chaîne / REG_SZ
5. Paramétrez la valeur ON

Pour annuler la génération, vous devez mettre la valeur JETSHOWPLAN à OFF.

Debug doit obligatoirement respecter la casse. La première lettre est en majuscule ; les suivantes sont en minuscules.

JETSHOWPLAN, ON et OFF doivent obligatoirement être en majuscules.

Notez qu'une fois la clef JETSHOWPLAN est en place, la position ON/OFF est prise en compte sans redémarrage du poste.

Une fois cette modification faite, redémarrez le poste de travail pour prendre en compte la modification de la base de registre.
Le fichier showplan.out se génère sous deux conditions :
l'ouverture, la modification ou la création d'un objet mettant en œuvre un accès à une table ; que ce soit par un accès direct, par une requête ou encore par les fonctions de domaines. Le fichier se génère également lors du compactage de la base.

Pour les autres cas de génération d'un fichier showplan.out, voir le chapitre suivant.

Pour forcer l'analyse d'une requête, il faut la modifier puis la sauvegarder. L'analyse apparaitra dans le fichier showplan.out.

Lors de la génération du fichier, l'analyse des tables système, locales et attachées est systématique. On y trouve donc des données intéressantes comme le chemin d'attache, le nombre d'enregistrements, les nombres de pages de données et tous les renseignements concernant les index.
Les erreurs éventuelles sont inscrites en tête du fichier. Chaque nouvelle action du moteur Jet est écrite à la suite du fichier.

Avec Windows XP, le fichier showplan.out est écrit dans le répertoire MES DOCUMENTS.

Avec les versions antérieures du système, le fichier showplan.out est écrit dans le répertoire courant.

Pour connaitre le répertoire courant :

- démarrez l'invite de commande, le prompt (c:\xxxx\>) indique le répertoire courant ;

- Sous ACCESS, lisez le résultat de la fonction VBA Curdir().

Ouvrez le fichier showplan.out :

Exemple de l'analyse d'une requête

Sélectionnez

--- Requête1 ---

- Inputs to Query -
Table 'Payeur'
    Using index 'PrimaryKey'
    Having index:
    PrimaryKey 122 entries, 1 page, 122 values
      which has 1 column, fixed, unique, primary-key, no-nulls
    NUMERO 122 entries, 1 page, 122 values
      which has 1 column, fixed, unique
    NOM 122 entries, 1 page, 118 values
      which has 1 column, fixed
Table 'CLIENT'
Table 'CONTRAT_APPAREIL'
Table 'CONTRAT'
    Using index 'CLIENTCONTRAT'
    Having index:
    CLIENTCONTRAT 336 entries, 1 page, 281 values
      which has 1 column, fixed
Table 'Appareil'
    Using index 'PrimaryKey'
    Having index:
    PrimaryKey 412 entries, 3 pages, 412 values
      which has 1 column, fixed, unique, primary-key, no-nulls
    N_APPAREIL 412 entries, 3 pages, 412 values
      which has 1 column, fixed
Table 'CONTRAT_APPAREIL'
    Using index 'N_CONTRAT'
    Having index:
    N_CONTRAT 478 entries, 3 pages, 301 values
      which has 1 column, fixed
    N_APPAREIL 478 entries, 3 pages, 404 values
      which has 1 column, fixed
    CONTRATCONTRAT_APPAREIL 478 entries, 3 pages, 301 values
      which has 1 column, fixed
    APPAREILCONTRAT_APPAREIL 478 entries, 3 pages, 404 values
      which has 1 column, fixed
Table 'CONTRAT'
- End inputs to Query -

01) Restrict rows of table CONTRAT
      using rushmore
      for expression "CONTRAT.N_CLIENT Like "411*""
02) Outer Join table 'CLIENT' to table 'CONTRAT'
      using index 'CONTRAT!CLIENTCONTRAT'
      join expression "CLIENT.N_CLIENT=CONTRAT.N_CLIENT"
03) Sort table 'CONTRAT_APPAREIL'
04) Outer Join result of '02)' to result of '03)'
      using temporary index
      join expression "CONTRAT.N_CONTRAT=CONTRAT_APPAREIL.N_CONTRAT"
05) Outer Join result of '04)' to table 'Payeur'
      using index 'Payeur!PrimaryKey'
      join expression "CLIENT.PAYEUR=PAYEUR.N_PAYEUR"
06) Outer Join result of '05)' to table 'Appareil'
      using index 'Appareil!PrimaryKey'
      join expression "CONTRAT_APPAREIL.N_APPAREIL=APPAREIL.N_APPAREIL"
07) Sort result of '06)'
08) Outer Join result of '01)' to result of '07)'
      using temporary index
      join expression "CONTRAT.N_CLIENT=[Vérification Payeur-Client-Contrat-Appareil].Cleint"
09) Inner Join result of '08)' to table 'CONTRAT_APPAREIL'
      using index 'CONTRAT_APPAREIL!N_CONTRAT'
      join expression "CONTRAT.N_CONTRAT=CONTRAT_APPAREIL.N_CONTRAT"

La requête correspondante : Requête1

Sélectionnez

SELECT CONTRAT_APPAREIL.N_CONTRAT, CONTRAT.N_CLIENT, 
       [Vérification Payeur-Client-Contrat-Appareil].T_SERVICE, 
       CONTRAT_APPAREIL.MODEENTR, CONTRAT.D_ECHEANCE
FROM [Vérification Payeur-Client-Contrat-Appareil] RIGHT JOIN (CONTRAT INNER JOIN 
     CONTRAT_APPAREIL ON CONTRAT.N_CONTRAT = CONTRAT_APPAREIL.N_CONTRAT) ON 
     [Vérification Payeur-Client-Contrat-Appareil].Cleint = CONTRAT.N_CLIENT
WHERE (((CONTRAT.N_CLIENT) Like "411*"));

Il s'agit d'une partie du fichier showplan.out comprenant l'analyse de la requête nommée Requête1. Ce résultat d'analyse se découpe en deux segments :
- l'analyse des tables utilisées dans la requête ;
- le détail du processus Rushmore.

On peut remarquer qu'en 01 la restriction est prise en charge par Rushmore. En 04 et 08, Rushmore crée un index temporaire pour les jointures.

L'analyse et la compréhension de ce fichier peuvent apporter beaucoup dans l'optimisation des requêtes. Notez qu'il ne s'agit que d'un résultat et non du processus complet d'analyse.

III-E-1-b. Les inconvénients de JetShowplan▲

Jetshowplan a un gros inconvénient : il ne se déclenche pas uniquement lors de la modification des requêtes, mais lors de toutes manipulations d'objets. De plus, il reçoit des informations en permanence. La solution est de le renommer entre deux écritures ou encore de le détruire sinon il prend de l'embonpoint. Il peut passer de quelques Kilo-octets à plusieurs Mégaoctets, ralentissant d'autant l'exécution de l'application.

Opérations	Taille du fichier showplan.out	Commentaires
Création d'un formulaire	66 Ko	Analyse de la requête source à plusieurs reprises.
Ouverture d'un état en mode consultation	14 277 Mo	Analyse de la requête source et chaque fonction de domaine autant de fois qu'elles sont sollicitées. Dans le cas de cet état élaboré, 10 appels de fonctions de domaines par page, 4040 enregistrements.
Ouverture d'un état en mode création	4 Ko	Analyse de la requête source.
Création d'une table	2 Ko	3 champs 2 index, le nom de la nouvelle table apparait avec l'analyse de chaque index.
Ouverture d'une table	3 Ko	Analyse de la table, nombre d'enregistrements, de pages de données, d'index et d'entrées dans les index.
Exécution d'une requête Select sous VBA	4 Ko	Un mythe tombe ! La requête est bien prise en charge par Rushmore.

Code VBA / Requête

Sélectionnez

Function testjet()
Dim rst As DAo.Recordset
Dim sql As String

    sql = "SELECT FACTURE.N_FACTURE, FACTURE.MONTANTTTC, FACTURE.MTVA55, FACTURE.MTVA196, 
    sql = sql + " FACT_DETAILS.MONTANT, FACT_DETAILS.MONTANTTTC, FACT_DETAILS.TVA"
    sql = sql + " FROM FACTURE LEFT JOIN FACT_DETAILS ON (FACTURE.N_FACTURE = FACT_DETAILS.N_FACTURE)
    sql = sql + " AND (FACTURE.N_FACTURE = FACT_DETAILS.N_FACTURE)"
    sql = sql + " WHERE (((FACTURE.N_FACTURE) > 204050279))"
    sql = sql + " ORDER BY FACTURE.N_FACTURE DESC;"

    Set rst = CurrentDb.OpenRecordset(sql)
    
    Debug.Print rst.Fields(0).Value
    
    rst.Close

End Function

Le fichier résultat ci-dessous et plus particulièrement le processus 01 indique que Rushmore est actif.

 

Sélectionnez

- Inputs to Query -
Table 'FACTURE'
    Database 'D:\ACCESS\wga\WGA_fact.mdb'
Table 'FACT_DETAILS'
    Database 'D:\ACCESS\wga\WGA_fact.mdb'
- End inputs to Query -

01) Restrict rows of table FACTURE
      using rushmore
      for expression "FACTURE.N_FACTURE>204050279"
02) Sort result of '01)'
03) Sort table 'FACT_DETAILS'
04) Outer Join result of '02)' to result of '03)'
      using Merge Join
      join expression "FACTURE.N_FACTURE=FACT_DETAILS.N_FACTURE And FACTURE.N_FACTURE=FACT_DETAILS.N_FACTURE"
05) Sort result of '04)'

Notez que tous les traitements du moteur Jet sont inscrits comme le démontre la présence des requêtes temporaires (~tmp…, ~sql…)


La conclusion sur Jetshowplan est que nous sommes en lien direct avec le moteur Jet. Cette fonction ne doit jamais être mise en place sur une application en exploitation et encore moins en cours de développement, mais uniquement dans le cas d'un besoin ponctuel d'analyse des requêtes.

• Évitez un trop grand nombre d'objets, préférez des solutions dynamiques.
• Restreignez l'utilisation des zones de liste et modifiables dans un environnement réseau. Celles-ci demandent beaucoup de transferts de données.
• Évitez les colonnes inutiles dans les zones de liste.
• Lors de l'utilisation d'onglets contenant beaucoup de données (sous-formulaire, zone de liste…) chargez les données au moment de la consultation.
• Évitez les accès directs aux tables dans la propriété Source ; utilisez plutôt des requêtes pour réduire au maximum le volume de données affichées.
• Prévoyez un bouton de saisie pour l'ouverture de chacun de vos formulaires. Cela évitera de récupérer des enregistrements inutiles à cette action.
• Dans les formulaires, utilisez la lecture seule si vous ne faites que de la consultation.
• Choisissez les méthodes de verrouillage adaptées aussi bien générales qu'au niveau des formulaires.
• Évitez les conflits de verrouillage en réglant les paramètres de temporisation.
• Convertissez vos applications et fichiers de groupe de travail dans la version utilisée.
• Même si la cohabitation de diverses versions d'ACCESS sur une même application est possible, mettez vos versions d'ACCESS à niveau.
• Dans le cas de formules ou de calculs, placez-les dans les formulaires ou états plutôt que dans la requête source.
• Préférez le nom de la requête dans la source des formulaires/états plutôt que la chaine SQL.
• Évitez l'affichage systématique d'un champ Mémo, préférez un bouton pour le charger.
• Limitez le nombre de polices de caractères dans vos applications, préférez toujours les polices standard.
• N'ouvrez jamais directement une table (liée ou non) avec une application en exploitation.
• Pour les liens formulaires/sous-formulaires composés de plusieurs champs, créez l'expression de liaison dans les requêtes sources.
• Si vous utilisez toujours les mêmes images dans un état ou un formulaire, utilisez plutôt un sous-état (ou sous-formulaire) vous n'aurez ainsi qu'un objet image au lieu d'une multitude stockée dans votre application.

• Évitez les noms longs pour vos objets, tables, champs…
• Évitez les espaces et caractères cabalistiques nécessitant l'ajout de crochets [] dans les noms de tables, de champs, de contrôles, de requêtes, d'états… tous les objets que vous devez ou pouvez nommer. L'underscore ou trait de soulignement _ permet de remplacer avantageusement l'espace.

• Si vous utilisez uniquement des bases JET (fichier de données MDB) préférez l'utilisation de DAO nettement plus rapide. Pour d'autres bases de données utilisez ADO.
• Pour des traitements EXCEL / ACCESS utilisez le passage de recordset via DAO au lieu de la modification de cellules (Range, Cell…).
• Lors des accès en automation à une feuille Excel, désactivez le recalcul automatique et l'affichage de la feuille. Les applications ouvertes en automation peuvent être cachées, n'hésitez pas à utiliser cette option.
• Évitez les recordsets modifiables si vous n'en avez pas besoin.
• Préférez toujours du code VBA à des macros.
• Lorsque vous ouvrez des objets recordset, automation et autres, pensez à les vider et les refermer.
• Évitez d'utiliser les anciennes méthodes disponibles pour compatibilité. Convertissez votre code avec les nouveaux objets et propriétés.
• Si vous faites référence plusieurs fois à un même objet dans le code, stockez-le dans une variable objet.
• Les variables Integer sont systématiquement converties en Long. Économisez cette conversion en déclarant vos variables Integer directement en Long.
• Pour savoir si une chaine est vide, utilisez la fonction Len() - Longueur de la chaine -. VBA stocke en mémoire la longueur de chaque chaine de caractères, Len ne fait donc que lire cette valeur. La valeur renvoyée par Len est toujours de type Long.
• Les fonctions renvoyant une chaine de caractères (fonctions précédées du signe dollars $) sont plus rapides que les mêmes sans $ renvoyant une variable Variant.
• Lorsque vous utilisez les dates, vous pouvez utiliser deux séparateurs différents, le # ou le ". Dans le second cas, VBA est obligé de faire une conversion.
• Préférez le type Currency à Single qui sollicite le FPU et donc plus long à traiter.
• Il existe deux opérateurs pour la division. \ (antislash) pour les entiers et / (slash) qui renvoie un Double et donc utilise le FPU.
• Utilisez les constantes mises à votre disposition par VBA pour remplacer certaines fonctions. vbCrLf remplace chr(13)+chr(10).
• Évitez d'utiliser les fonctions de domaines (Dlookup, Dsum…) sur des tables ayant de gros volumes.
• Évitez d'utiliser le Find (ADO) ou FindFirst (DAO) sur des gros volumes de données, créez une requête ou un filtre avant la recherche.
• Si vous faites une recherche sur un champ indexé, utilisez Seek (ADO, DAO) plutôt que Find / FindFirst.
• Utilisez les tableaux de variables au lieu d'une table temporaire.

III-H-1. ISAMstats une autre option non documentée▲

Sub Main() 

Dim dbs As Database, ws As Workspace 
Dim strSQL As String 
Dim lngDiskRead As Long, lngDiskWrite As Long, lngCacheRead As Long, lngLocksReleased As Long
Dim , lngCacheReadAheadCache As Long, lngLocksPlaced As Long
  
' Affecte explicitement 0 aux variables
lngDiskRead = DBEngine.ISAMStats(0, True) 
lngDiskWrite = DBEngine.ISAMStats(1, True) 
lngCacheRead = DBEngine.ISAMStats(2, True) 
lngCacheReadAheadCache = DBEngine.ISAMStats(3, True) 
lngLocksPlaced = DBEngine.ISAMStats(4, True) 
lngLocksReleased = DBEngine.ISAMStats(5, True) 

Set dbs = OpenDatabase("chemin et nom du fichier applicatif", False, False) 
Set ws = Workspaces(0) 
strSQL = "Indiquez ici votre chaîne SQL action"
 
dbs.Execute strSQL, dbFailOnError

' La transaction nulle assure qu'il n'y pas d'activité 
' après l'exécution qui pourrait fausser les statistiques 
ws.BeginTrans 
ws.CommitTrans 

' Les appels ISAMStats retournent les statistiques 
' Les valeurs ne sont pas remises à zéro. 

lngDiskRead = DBEngine.ISAMStats(0) 
lngDiskWrite = DBEngine.ISAMStats(1) 
lngCacheRead = DBEngine.ISAMStats(2) 
lngCacheReadAheadCache = DBEngine.ISAMStats(3) 
lngLocksPlaced = DBEngine.ISAMStats(4) 
lngLocksReleased = DBEngine.ISAMStats(5) 

Debug.Print "0 - Disk reads " & lngDiskRead 
Debug.Print "1 - Disk writes " & lngDiskWrite 
Debug.Print "2 - Cache reads " & lngCacheRead 
Debug.Print "3 - Cache reads from RA cache " & lngCacheReadAheadCache
Debug.Print "4 - Locks placed " & lngLocksPlaced 
Debug.Print "5 - Locks released " & lngLocksReleased   

End Sub

' pour DAO
DBEngine.SetOption DAO.paramètre, MaValeur

' pour ADO : ADOc est un objet Connection ADO
ADOc.Properties("ADO.paramètre") = MaValeur

ODBC est une couche supplémentaire permettant de mettre en relation ACCESS et n'importe quel moteur de base de données. Son utilisation implique une bonne connaissance de la base à laquelle on accède.

• Favorisez toujours un pilote Microsoft (ODBC par exemple).
• Évitez les accès directs aux tables, préférez un attachement et des requêtes.
• Mettez la propriété FailOnError du serveur à Oui.
• Assurez-vous toujours que les requêtes SQL peuvent être exécutées par le serveur.

• Avec les versions ACCESS 2000 et antérieures, désactivez la correction automatique des noms.
• N'hésitez pas à faire des essais de timing et rafraichissement pour optimiser vos temps de réponse. Ces options se paramètrent soit pour l'application, soit pour la globalité d'ACCESS.
• Si vous avez trop d'accès concurrents (utilisateurs au même instant sur la ou les mêmes tables), choisissez un autre moteur de base de données que JET. MSDE, SQL Server par exemple.
• Gardez toujours Windows et MS Office à jour et au même niveau, évitez les bêta versions en production.

Comme nous l'avons vu dans le chapitre concernant les tables, vous devez proscrire l'utilisation des noms longs dans vos répertoires au profit de chemin de type 8.3 (huit caractères « . » trois caractères).

Rassurez-vous ! Vous n'avez pas besoin de tout changer sur votre serveur, vous pouvez vous-même traduire un nom long en nom court à l'aide des tildes ~.

C:\Program files\

C:\progra~1\

Le code VBA suivant permet de transformer automatiquement les noms longs en noms au format 8.3.

Option Compare Database
Option Explicit

Declare Function GetShortPathName Lib "kernel32" _
        Alias "GetShortPathNameA" (ByVal lpszLongPath As String, _
        ByVal lpszShortPath As String, ByVal cchBuffer As Long) As Long

Public Function GetShortName(ByVal sLongFileName As String) As String
    Dim lRetVal As Long, sShortPathName As String, iLen As Integer
    sShortPathName = Space(255)
    iLen = Len(sShortPathName)
    lRetVal = GetShortPathName(sLongFileName, sShortPathName, iLen)
    GetShortName = left(sShortPathName, lRetVal)
End Function

Appel de la fonction :

NomCourt = GetShortName("C:\Program Files\Microsoft Office\")

V-A-1. Transformer les noms longs en noms courts des tables liées▲

Declare Function GetShortPathName Lib "kernel32" _
            Alias "GetShortPathNameA" (ByVal lpszLongPath As String, _
           ByVal lpszShortPath As String, ByVal cchBuffer As Long) As Long

Function RefreshLinks()
    On Error GoTo ErrorHandler
    
    'Définit l'objet catalogue ADOX
    Dim objCat As New ADOX.Catalog 
    'Définit l'objet table ADOX
    Dim objTbl As ADOX.Table 

    'Nom de la base pour la table liée
    Dim strFilename As String 
    'Chemin et nom de la base de la table liée
    Dim strFullName As String 

    Dim blnIsMapi As Boolean
    Dim blnIsImex As Boolean
    Dim blnIsTemp As Boolean
    Dim blnLongFileName As Boolean
    Dim blnFailedLink As Boolean
    Const srtImex = "IMEX"
    Const strMapi = "MAPILEVEL="

    'ouvre le catalogue
    objCat.ActiveConnection = CurrentProject.Connection

    'parcourt la collection de tables et met à jour le lien
    For Each objTbl In objCat.Tables
        'Vérifie que la table est une table liée
        If objTbl.Type = "LINK" = True Then
            blnIsTemp = objTbl.Properties("Temporary Table") Or Left(objTbl.Name, 1) = "~"
            blnIsImex = (InStr(1, objTbl.Properties("Jet OLEDB:Link Provider String"), srtImex, vbTextCompare) > 0)
            blnIsMapi = (InStr(1, objTbl.Properties("Jet OLEDB:Link Provider String"), strMapi, vbTextCompare) > 0)

            If Not blnIsTemp And Not blnIsImex And Not blnIsMapi Then  
                'vérifie que c'est une table JET.
                strFullName = objTbl.Properties("Jet OLEDB:Link Datasource")
                strFilename = Mid(strFullName, InStrRev(strFullName, "\", Len(strFullName)) + 1, Len(strFullName))
                'détermine si la base existe
                If DoesFileExist(strFullName) = True Then
                    objTbl.Properties("Jet OLEDB:Link Datasource") = GetShortName(strFullName)  
                'met à jour le lien de la table en nom court
                Else
                    MsgBox "Mise à jour impossible : '" & objTbl.Name & "'" & 
                        _String(2, vbCrLf) & "Fichier non trouvé: " & vbCrLf & strFullName
                    blnFailedLink = True
                End If
                If InStr(strFilename, ".") > 9 Then blnLongFileName = True
            End If
        End If
    Next
   
    If blnFailedLink = False Then
        If blnLongFileName = True Then
            MsgBox "Le lien de la table a été mis à jour avec succès, 
            cependant le nom de la base serveur n'est pas au format 8.3" & _
            vbCrLf & "Veuillez renommer le fichier, refaire le lien et relancer la procédure.", vbExclamation
        Else
            MsgBox "Le lien a été correctement mis à jour. ", vbInformation
        End If
    Else
        MsgBox "Les liens n'ont pu être mis à jour." & vbCrLf & "Vérifier la cohérence des liens.", vbExclamation
    End If

ExitHandler:
     Exit Function

ErrorHandler:
    MsgBox Err.Description & " " & Err.Number
    Resume ExitHandler
    
End Function

Function GetShortName(ByVal sLongFileName As String) As String
           Dim lRetVal As Long, sShortPathName As String, iLen As Integer
           'paramètre le tampon de retour de l'API.
           sShortPathName = Space(255)
           iLen = Len(sShortPathName)
    
           'appel de la fonction
           lRetVal = GetShortPathName(sLongFileName, sShortPathName, iLen)
           'supprime les caractères inutiles
           GetShortName = Left(sShortPathName, lRetVal)
 End Function

Function DoesFileExist(strFileSpec As String) As Boolean
    'True si le fichier spécifié dans strFilespec existe.
    'False si strFileSpec n'est pas valide
    'Nom du fichier si strFileSpec est un répertoire
    Const INVALID_ARGUMENT As Long = 53
    On Error GoTo DoesfileExist_Err
    If (GetAttr(strFileSpec) And vbDirectory) <> vbDirectory Then
        DoesFileExist = CBool(Len(Dir(strFileSpec)) > 0)
    Else
        DoesFileExist = False
    End If
DoesfileExist_End:
    Exit Function
DoesfileExist_Err:
    DoesFileExist = False
    Resume DoesfileExist_End
End Function

V-A-2. Mettre en cache les noms longs (Postes clients uniquement)▲

    HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MRxSmb\Parameters

    InfoCacheLevel 
    Type = Hexadécimal
    Valeur = 0x10

V-A-3. Désactiver la génération automatique de noms courts (Postes Serveur)▲

    HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\FileSystem
    NtfsDisable8dot3NameCreation 
    Valeur = 1

Cette opération modifie le délai de réaction du serveur lors des notifications d'échecs de partage. Plus le délai est court plus vite la demande est réitérée et donc susceptible d'aboutir.

Il faut agir directement sur la base de registre du serveur. Trouvez la clef suivante :

    HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\LanmanServer\Parameters

Normalement vous devez voir deux valeurs :

    SharingViolationRetries
    SharingViolationDelay

Mettez 0 pour les deux. Si elles n'existent pas, créez-les ! Elles sont du type DWORD.

Nous allons simplifier le mécanisme du fichier ldb pour une meilleure compréhension.
Lors de la connexion du premier utilisateur à un fichier mdb ou mde, un fichier .ldb est créé.
À la fermeture du fichier par le dernier utilisateur, ce fichier est supprimé.
Tous les utilisateurs entre le premier et le dernier se connectent à ce fichier unique.
Chaque fois qu'il accède à des enregistrements, une information est inscrite dans ce fichier indiquant le ou les enregistrements verrouillés. Ce fichier est donc fondamental pour le fonctionnement de JET.
Il arrive que cette connexion échoue ce qui retarde les interrogations aux fichiers de données.
Pour pallier ce problème, vous pouvez créer un accès permanent grâce à une technique simple.

• Dans le fichier dorsal (fichier sur le serveur contenant les données).
• Créez une table que vous nommez tbl_ldb.
• Créez un champ de type booléen.
• Attachez cette nouvelle table dans le fichier frontal (fichier du poste client contenant l'IHM).
• Ouvrez l'éditeur VB. Dans le Menu Outils/Références, vérifiez que vous avez bien activé le Microsoft DAO x.x correspondant à votre version (3.6 en général).
• Dans un module standard, ajoutez le code suivant :

Public rstLienOuvert As DAO.Recordset

• Sauvez le module et fermez l'éditeur VB.
• Créez un formulaire que vous nommerez Frm_ldb.
• Dans les propriétés du formulaire, repérez Sur Ouverture et créez un code VBA.
• Ajoutez ce code :

Set rstLienOuvert = CurrentDb.OpenRecordset("tbl_ldb")

• Toujours dans les propriétés, repérez l'événement Sur fermeture et créez comme précédemment un code VBA.
• Insérez le code suivant :

rstLienOuvert.Close
Set rstLienOuvert = Nothing

• Au lancement, ouvrez ce formulaire avec le statut caché :

DoCmd.OpenForm "Frm_ldb", , , , , acHidden

À la fermeture de l'application, le formulaire caché se fermera, pendant l'intervalle, il n'y aura pas de déconnexions intempestives donc pas d'attente de reconnexion.