Cours vba Access avec explications et exemples pour réviser ensemble
…
2.3 MS Excel (tableur) VS MS Access (SGBDR)
L'expérience nous montre que plus de 95% des utilisateurs de la suite MS Office utilisent MS Excel à tort pour la gestion de listes et consolidation de données. Ce qui est incroyable c'est que ces utilisateurs se rendent pourtant bien compte que ce logiciel n'est pas fait pour la gestion de données étant donné les difficultés qu'ils rencontrent et la perte de temps que cela engendre!
Le problème vient au fait, d'un manque de recul et de formation de ses utilisateurs car rappelons-le, MS Excel est un TABLEUR (graphiques et calculs) et MS Access un système gestion de BASE DE DONNÉES RELATIONNELLES (cela veut bien dire ce que cela veut dire !!).
Certes, il est possible d'argumenter le fait que MS Access est un logiciel métier pour spécialistes (informaticiens) et que contrairement à MS Excel qui est étudié dès le gymnase (collège) dans les écoles publiques, MS Access n'est lui souvent seulement enseigné qu'en premier ou deuxième cycle universitaire dans les branches d'économie.
Cependant, toute entreprise doit se poser les questions suivantes:
…
15 Optimisation et analyse
Une fois votre base terminée, il existe dans MS Access, une série d'assistants qui vont effectuer une analyse de votre base et qui vont tenter de vous aider à corriger les éventuelles erreurs ou manque de rigueur de votre part.
Nous aurions pu voir ce outil dès le début du cours mais il fallait au préalable certaines connaissances. Par ailleurs, lors de la discussion sur la normalisation des base de données dans le chapitre de modélisation, nous avons fait référence au présent chapitre.
15.1 Table
L'outil Table me dans le menu Outils/Analyse est assez (voir très) médiocre. Il s'agit en fait d'un outil d'aide à la normalisation de deuxième et troisième forme mais qui ne peut deviner ce que vous avez en tête comme stratégie de développement.
Pour exemple, prenons la table tblSorties dans une version complétement erronée (en 1ère forme normal) sans qu'elle soit liée à quoi que ce soit donc:
15.2 Performances
Dans Outils/Analyse lancez l'option Performances:
L'idéal, serait de ne plus avoir aucun message dans les différentes analyses. Ainsi, si vous sélectionnez l'onglet "Tous types d'objets" et cliquez ensuite sur le bouton "Sélectionner tout" et enfin sur le bouton "OK", vous pourriez être amené à avoir un résultat du genre:
Autre point important: Microsoft Access ne recouvre pas automatiquement l'espace libre après avoir effacer des enregistrement ou des objets. Pour recouvrir cet espace, vous devez compacter la base de données. Pour ce faire, allez dans le menu Outils/Utilitaire de base de données et sélectionnez l'options Compacter la base de données. Pour éviter à avoir faire ceci systématique, vous pouvez aller dans Outils/Options:
Vous remarquerez l'option Compacter à la fermeture dans l'onglet General qui vous évitera d'oublier cette procédure.
15.3 Documenter
16 Distribution
Votre application est terminée, et vous souhaitez maintenant la partager avec vos collègues ou autres utilisateurs (si vous êtes développeur). Dans le cas le plus simple une solution consiste à placer la base sur le disque réseau commun de l'entreprise afin que chaque utilisateur puisse l'exécuter sans complications. Cependant, avant de faire ceci, il est bon de penser à la manière dont vous allez la distribuer et faire de la maintenance.
Si votre ordinateur est connecté à un réseau, vous pouvez travailler dans une base de données Microsoft Access en même temps que d'autres utilisateurs. Vous pouvez partager des données dans un environnement multi-utilisateur de plusieurs manières.
Placez la base de données MS Access entière sur un serveur de réseau ou dans un dossier partagé. Cette méthode est la plus facile à mettre en œuvre. Chaque utilisateur partage les données et utilise les mêmes formulaires, états, requêtes, macros et modules. Utilisez cette stratégie si vous souhaitez mettre en place une utilisation uniforme de la base de données ou si vous ne voulez pas que les utilisateurs puissent créer leurs propres objets.
Placez uniquement les tables (la base "Jet") sur le serveur du réseau et conservez les objets de la base de données dans les ordinateurs des utilisateurs. Ainsi, les performances de la base de données sont accrues, car seules les données sont transférées sur le réseau. De plus, les utilisateurs peuvent personnaliser leurs formulaires, états et autres objets en fonction de leurs préférences et besoins individuels, sans affecter les autres utilisateurs.
Vous pouvez séparer les tables des autres objets de base de données à l'aide de l'outil Fractionner la base de données (Outils/Utilitaire de base de données).
Au besoin, on pourra remplacer la base Jet par une base MSDE (Microsoft SQL Server Desktop Edition) ou client/serveur.
Jet et MSE sont limités en taille: 2Go maximum. Au-delà, il faudra utiliser une base client/serveur proprement dite.
Jet, avec une limite théorique de 255 connexions simultanées, marque une baisse de performances à partir de 5 connexions, et n'est pas recommandé au-delà de 10. MSDE est optimisé pour 5 requêtes concomitantes, y compris les procédures stockées. Au-delà, les performances se dégradent rapidement.
Vous pouvez convertir un ou plusieurs objets de base de données au format HTML statique ou HTML généré par serveur, ou créer des pages d'accès aux données, puis les afficher dans un navigateur tel que Internet Explorer, sur le Web
Si vous utilisez deux ordinateurs, un ordinateur de bureau et un portable par exemple, vous pouvez utiliser le Porte-documents de Microsoft Windows pour effectuer des réplicas de votre base de données Access et les maintenir synchronisés. En outre, plusieurs utilisateurs situés à divers endroits peuvent travailler simultanément avec leurs propres copies et les synchroniser à travers le réseau, par l'intermédiaire d'une connexion par numérotation téléphonique ou via Internet.
Si vous travaillez dans un environnement client-serveur, vous pouvez tirer profit de la puissance et de la sécurité supplémentaires qui vous sont offertes en créant une application client-serveur. Pour plus d'informations, il faudrait suivre un cours Visual Basic et MS SQL Server ou ASP/PHP.
...
16.1 Fractionnement d'une base
Objectif: Ne partager que les tables de la base de données MS Access
Allez dans le menu: Outils/Utilitaire de base de données/Fractionner la base de données. Suivez l'assistant et renommez le fichier contenant les tables ainsi: Tables.mdb
Vous verrez dans la fenêtre base de données que chaque table comporte maintenant une flèche ce qui signifie qu'il y a une liaisons externe.
Au cas où la liaison serait perdue ou modifiée, il faut aller dans le menu Outils/Utilitaire de base de données/Gestionnaire de tables liées:
Il suffit alors de cliquer sur Select All et ensuite sur OK et de définir le nouveau chemin du fichier mdb contenant les tables.
Nous allons placer les tables de la base "la plus propre" de la classe sur le serveur (ou de chaque participant respectif)
Distribuons le fichier contenant les formulaires, requêtes et états... sur chaque poste et effectuons-y la liaison.
Vérifiez le fonctionnement.
Remarque: dans une entreprise il est malheureusement fréquent que groupe de travail ait développé sa propre base. Quand vient le temps pour des raisons de cohérence et de productivité de réunir toutes les bases il existe une possibilités qui revient à lier une table d'une base donnée à une autre base. Pour ce faire, la manipulation est trivialement simple, il suffit d'aller dans le menu Fichier/Données externes/Lier une table (nous avons déjà pratiqué cela au début du cours).
Nous verrons dans la partie VBA du cours comment lorsque nous sommes en possession d'un fichier MDE, automatiser la mise-à-jour des liaisons.
17 Securité avancée
Microsoft Office Access offre trois méthodes de protection d'une base de données:
Remarque: Désolé si ce chapitre n'est pas très clair mais à force que Microsoft change les versions à un rythme endiablé il y a un mélange de plus en plus grand dans le présent document.... évidemment tout ce qui est écrit ci-dessous est décanté en formation de manière correcte et propre (du moins j'essaie...).
17.1 Définition d'un mot de passe
La méthode la plus simple pour la (pseudo-)sécurité est de définir un mot de passe pour ouvrir la base de données. Dès qu'un mot de passe est défini, une boîte de dialogue qui exige un mot de passe s'affiche lors de chaque ouverture de la base de données. Seuls les utilisateurs qui tapent le mot de passe correct pourront ouvrir la base de données.
Cette méthode est relativement sûre (Microsoft Access code le mot de passe pour qu'il ne soit pas accessible en lisant directement le fichier de base de données), mais elle ne s'applique qu'à l'ouverture d'une base de données. Dès qu'une base de données est ouverte, tous ses objets sont à la disposition de l'utilisateur. Pour une base de données qui est partagée entre un petit groupe d'utilisateurs, ou sur un ordinateur isolé, définir un mot de passe est souvent suffisant.
Marche à suivre:
Le mot de passe est à présent défini. La prochaine fois que tout utilisateur ouvrira la base de données, une boîte de dialogue demandant le mot de passe s'affichera.
Attention:
n Un mot de passe respecte la casse on doit le taper exactement comme il a été défini (maximum 20 caractères).
n Si on perd ou si on oublie le mot de passe, il ne peut pas être récupéré (sans logiciels spécialisés gratuitement disponibles sur le web) et plus personne ne pourra ouvrir la base de données !
Remarque: le danger avec cette méthode, c'est que chaque utilisateur a quand même accès à toute l'architecture de la base de données s'il connaît comment passer outre l'écran d'accueil (shift...). De plus, n'importe quel utilisateur pourrait l'ouvrir en "Mode exclusif" (Fichier/Ouvrir Bouton Ouvrir Ouvrir en mode exclusif) et dès lors empêcher chacun de ses collègues d'y écrire ou lire la moindre information !
17.2 Sécurité au niveau utilisateur 17.2.1 Protocole
Attention, si vous ne suivez pas le protocole suivant SCRUPULEUSEMENT en ce qui concerne une base sécurisée, vous allez rencontrer des complications!!!
Si vous ne suivez pas ces étapes, n'importe qui pourra ouvrir votre base de données sans aucune sécurité sur un autre ordinateur.
17.2.2 Méthodes
La méthode la plus flexible et la plus étendue pour protéger une base de données s'appelle la "sécurité au niveau utilisateur". Ce type de sécurité est similaire aux méthodes utilisées dans la plupart des systèmes de réseau. Les utilisateurs doivent s'identifier et taper un mot de passe lorsqu'ils démarrent MS Access (et non pas nécessairement une base de donnée spécifique !!!).
Au sein du fichier d'informations de groupe de travail, ils sont identifiés comme étant les membres d'un groupe. MS Access fournit deux groupes par défaut: les administrateurs (appelés le "groupe Administrateurs") et les utilisateurs (appelés le "groupe Utilisateurs"), mais des groupes supplémentaires peuvent être définis (heureusement...).
Les autorisations d'accès sont accordées aux groupes et aux utilisateurs pour déterminer de quelle manière ils sont autorisés à travailler avec chaque objet dans une base de données. Par exemple, les membres du groupe utilisateurs pourraient être autorisés à visualiser, introduire ou modifier des données dans une table, mais ils ne pourraient pas changer la présentation de cette table. Le groupe Utilisateurs pourrait être autorisé à visionner les données de certaines tables et se voir refuser l'accès total à une table contenant des données sensibles. Les membres du groupe Administrateurs ont toutes les autorisations d'accès sur tous les objets d'une base de données.
Les trois raisons principales d'utilisation de la sécurité au niveau utilisateur sont: Protéger la propriété intellectuelle du code.
n Eviter que les utilisateurs ne détériorent par inadvertance une application en changeant le code ou les objets dont l'application dépend.
n Protéger des données essentielles dans la base de données.
Les groupes, les utilisateurs, les bases de données et toutes les autorisations d'accès font partie d'un environnement commun qu'on appelle un espace de travail (Workspace).
La sécurité au niveau utilisateur s'applique donc au niveau du logiciel MS Access et non uniquement sur une unique base prédéfinie !!!
Remarque: Bien qu'un utilisateur appartienne toujours à un groupe, s'il a des droits différents du groupe, ce seront ses droits individuels actifs qui prendront le dessus sur ceux inactifs du groupe (et si le groupe a donc des éléments actifs qui sont inactifs chez l'utilisateur, alors il héritera automatiquement des droits actifs du groupe).
Dans MS Access, le système de sécurité est toujours en activité:
n on appartient toujours à un espace de travail !
n on fait toujours partie d'un groupe !
n on est toujours un utilisateur !
Ces diverses informations sont stockées dans un fichier discret, mais très important, que l'on trouve au premier niveau de l'environnement d'installation de MS Access, soit dans MSOFFICE (ou directement dans le moteur JET de votre base si le protocole précédemment présenté a été suivi) pour les machines en réseau, soit dans:
C:\Documents and Settings\<Utilisateur>\Application Data\Microsoft\Access pour les machines autonomes: le fichier system.mdw.
MDW = Microsoft Data Workspace
C'est le "fichier d'informations du groupe de travail" comme l'appelle Microsoft. Ce fichier est créé au moment de l'installation du logiciel sur la machine. Les paramètres nécessaires à la génération de ce fichier sont pris dans les deux informations que l'on doit fournir lors de l'installation: le nom de l'utilisateur de la machine et le nom de l'organisation dans laquelle il travaille.
Par défaut, toute personne qui lance MS Access va se trouver englobée dans l'espace de travail défini par ce fichier System.mdw:
Remarques:
n Dans la version française, les termes anglais ont été traduits, mais il faut utiliser les termes anglais en Visual Basic Application (V.B.A.).
n Il arrive que l'utilisateur standard (Guest) ne soit pas créé dans l'espace de travail. On n'aura donc que l'Administrateur (Admin) à disposition...
Le(s) utilisateur(s) par défaut susmentionné(s) n'ont pas de mot de passe comme nous l'avons déjà implicitement mentionné dans le protocole de début !
17.2.3 Modélisation des droits
Pour définir les groupes d'utilisateurs il faut d'abord définir une stratégie d'utilisation de l'application. Le mieux est d'organiser tout cela sous forme de tableau.
Voici l'exemple d'un tableau réalisé. Notez que n'importe quel tableur ou logiciel permettant de faire des tableaux fait l'affaire.
17.2.4 Dangers de l'espace de travail "par défaut": Principe: Quand on lance MS Access sans indication particulière:
n on utilise toujours l'espace de travail par défaut décrit pas System.mdw
n on est toujours considéré comme l'Administrateur de cet espace de travail !! Conséquence:
En tant qu'Administrateur, on a tous les pouvoirs sur l'espace de travail en cours, ainsi que sur toutes les bases de données créées à partir de cet espace et, bien entendu, sur tous les objets que peuvent contenir ces bases !
Risque (qui concernant les O.S. avant Windows XP à ma connaissance...):
Le premier utilisateur un peu futé et mal intentionné (ou maladroit...) attribue un mot de passe à l'Administrateur ... il devient le propriétaire de l'espace de travail et, à partir de cet instant, il empêche tous les autres utilisateurs ... d'utiliser toutes les bases de données déjà créées à partir de cet espace de travail (de son ordinateur au fait)!
Changer le mot de passe ne permet de le faire que pour l'utilisateur connecté Précautions à prendre:
Il faut exécuter le programme WRKGADM.EXE (WoRK Group ADMinistrator) qui se trouve, soit dans Windows\System, ou en passant par l'environnement MS Access par Tools/Security/Workgroup Administrator: pour pouvoir créer un nouveau fichier System.mdw.
Comme on le voit, ce fichier n'est pas tiré du néant, mais est "copié" à partir d'un fichier existant: cela peut être l'original ou déjà une version préparée par nos soins conformément au protocole susmentionné.
Remarque: La dénomination "groupe de travail" n'est pas très heureuse: il vaudrait mieux parler d'un "espace de travail", terme utilisé dans Visual Basic. Le mot groupe peut prêter à confusion, car on va définir des groupes d'utilisateurs à l'intérieur du "groupe de travail"...
Trois informations sont nécessaires pour générer le nouveau fichier:
L'Identificateur de groupe de travail doit assurer que l'espace de travail que l'on crée est réellement unique, car la même personne (Nom), dans la même entreprise (Organisation), peut créer plusieurs "groupes de travail". Ce code est donc le seul moyen de les distinguer !
Ensuite, le logiciel va nous demander où le sauvegarder et sous quel nom:
On peut donner un nouveau nom au fichier créé et le stocker dans le même répertoire que le fichier System.mdw d'origine, ou enregistrer le nouveau System.mdw dans un répertoire particulier qui sera partagé par les différents utilisateurs de ce "groupe de travail" (cas le plus communément utilisé).
Il est vital de conserver les paramètres utilisés, par écrit et dans un endroit sûr.
En effet, si le fichier "d'informations du groupe de travail" venait à être détruit ou corrompu, la seule façon de pouvoir accéder aux bases de données partageant cet espace de travail - si la sécurité est correctement mise en place (avec l'assistant de sécurité par exemple) - est de reconstruire un fichier identique !
Attention: Il faut dès lors absolument respecter les majuscules et les minuscules d'origine.
Remarque: Si le fichier *.mdw est créé de manière classique (c'est-à-dire sans l'assistant), une simple suppression de celui-ci permettra à tout le monde d'accéder à la base de données avec tous les droits.
Nous avons maintenant plusieurs espaces (groupes) de travail. Comment faire pour indiquer celui avec lequel on veut travailler ? Dans sa version française, Microsoft parle de "rejoindre un groupe de travail Microsoft Access".
Deux méthodes sont possibles:
Première méthode: Utiliser le programme "Administrateur de groupe de travail"
Ce chemin a été sauvegardé dans la base de registres sous la clé:
Hkey_Local_Machine\Software\Microsoft\Office\<Version actuelle
d'Office>\Access\Jet\<Version actuelle du Jet>\Engines
Au démarrage suivant, Microsoft Access utilisera automatiquement les informations stockées dans le fichier .MDW du groupe de travail que l'on vient de rejoindre.
Deuxième méthode: Démarrer MS Access avec une option sur la ligne de commande (niveau expert en connaissances de MS Windows).
Il suffit d'ajouter l'option /wrkgrp à la ligne de commande.
Exemple: F:\MsOffice\Office\Msaccess.exe /wrkgrp E:\Erguel\Erguel.mdw On peut taper cette ligne dans la fenêtre "CMD".
Attention: Avec des machines en réseau qui sont utilisées par des personnes différentes, la deuxième méthode est nettement préférable. En effet, la base de registres Hkey_Local_Machine n'est pas enregistrée avec les paramètres de l'utilisateur, si bien que l'information va demeurer dans la machine quel que soit l'utilisateur!!! Le prochain utilisateur qui va lancer Access sera d'emblée connecté au dernier groupe de travail utilisé et non pas au groupe de travail par défaut !
Remarque: Dans MS Access 97 et ultérieur, les préférences utilisateur sont stockées dans la base de registres Windows sous la clé:
HkeyÇurrent_User\Software\Microsoft\Office\<Version actuelle d'Office>\Access\Settings. Maintenant que l'espace de travail est créé, il faut s'occuper de l'organiser:
Ces différentes opérations s'effectuent dans MS Access, une fois que l'on est "connecté" au groupe de travail désiré !
C'est le Menu Outils qui donne accès aux options Sécurité.
Rappel: Lorsque l'on crée pour la première fois un fichier d'information de groupe de travail, on est considéré par le système comme étant le fameux "Administrateur" qui dispose de tous les droits. Il faut user de ces droits avec discernement et agir avec méthode pour ne pas se trouver coincé par une mauvaise manipulation.
1ère opération (déjà mentionné au début de ce chapitre):
Créer un nouvel Administrateur, différent de l'administrateur par défaut (piège classique)!
Choisir un nom symbolique du genre: BigBoss, Patron, Chef, etc. ou utiliser le nom du véritable administrateur du groupe de travail
Le N° personnel permet de distinguer des utilisateurs qui auraient le même nom. Comme on l'a déjà vu, Il faut conserver cette information dans un endroit sûr, pour le cas où il faudrait recréer le fichier d'information du groupe de travail ainsi que les utilisateurs qui le composent !
2ème opération:
Inscrire absolument cet utilisateur dans le groupe des Administrateurs !
En prévision de la 3ème opération, il est indispensable bien évidemment qu'une personne au moins soit membre du groupe "Administrateurs" pour disposer des pouvoirs nécessaires à la poursuite des opérations !
3ème opération:
Il faut maintenant retirer l'ancien Administrateur du groupe "Administrateurs" (sinon vous faites un travail pour rien). De cette manière, les éventuels "Administrateurs" d'autres groupes de travail ne pourront plus jamais prendre possession des bases de données qui vont être créées par le nouveau groupe de travail.
Il faut pour cela donner un mot de passe à cet ancien "Administrateur", car il n'est pas possible de détruire ce compte ( ? !) qui va demeurer maintenant en tant qu'utilisateur. Il faut donc empêcher que n'importe qui puisse accéder, sans contrôle, à ce groupe de travail en utilisant le nom "Administrateur".
4ème opération:
Quitter et relancer MS Access. Cette fois, une boîte de dialogue "Connexion" doit apparaître à l'écran... Il faut saisir ou sélectionner le nouvel "Administrateur". Il n'a pas encore de mot de passe ...
5ème opération:
Attribuer un mot de passe à l'Administrateur, puisque l'on est connecté sous son nom.
6ème opération: créer les groupes d'utilisateurs.
Par exemple: Membres, Comité, etc. selon la structure et les besoins de l'entreprise...
7ème opération: créer les comptes d'utilisateurs.
Le terme "compte d'utilisateur" provient du début de l'informatique, lorsque l'on tenait le compte du temps que chaque utilisateur passait sur son terminal connecté sur l'ordinateur central en "time sharing".
8ème opération: attribuer les utilisateurs aux différents groupes.
L'avantage des groupes, c'est qu'en attribuant un utilisateur à l'un d'entre eux, cet utilisateur acquière d'un seul coup tous les droits et toutes les autorisations que l'on a accordés à ce groupe au préalable.
Il n'y a donc pas besoin de définir individuellement le détail des droits de chaque utilisateur: il suffit de le prévoir pour son groupe et de lui faire rejoindre ce groupe !
Remarque: Droits et autorisations seront traitées plus loin.
Maintenant que l'espace de travail est délimité, il faut encore y incorporer les bases de données que les utilisateurs vont utiliser ! Deux cas de figure non exclusifs peuvent se présenter:
n On veut créer de nouvelles bases de données à partir de l'espace de travail choisi.
n On veut utiliser, en les sécurisant dans cet espace de travail, des bases de données existantes.
Dans les deux cas pourtant la procédure est la même: il faut faire une copie de la base de données existante en la passant par le protocole vu plus haut ou par une moulinette particulière: un "Assistant sécurité" qui a été apparu avec Access 2002.
Donc pour sécuriser une base de données existante une autre possibilité plus simple que le protocole consiste à utiliser ce fameux assistant (qui cependant n'efface pas les groupes et utilisateurs dont tout le monde connaît les noms à l'avance... ce qui n'est pas vraiment très très astucieux en termes de sécurité...):
