Cours Access 2007 Simplifié

Notions sur les bases de données

Base de données

Une base de données est un ensemble organisé d’informations avec un objectif commun.

Système de gestion de bases de données

Un SGBD (Système de Gestion de Base de Données) est un ensemble de programmes informatiques (logiciel) qui permettent l'accès à une base de données et sa manipulation.

Exemples de SGBD :

 Access (payant)

 Oracle (payant)

 MySQL (gratuit)

 SQL server (payant)

 PostgreSQL (gratuit)

Notions sur les bases de données  relationnelles (modèle EntitéAssociations)

Avant de réfléchir au schéma relationnel d’une application, il est bon de modéliser la problématique à traiter d’un point de vue conceptuel et indépendamment du logiciel utilisé. Il existe plusieurs outils de modélisation :

 MERISE pour le modèle relationnel 

 UML pour le modèle objet

Quelques éléments du modèle relationnel

Une Entité : représentation d'un objet du monde réel (concret ou abstrait), perçu par le concepteur comme ayant une existence propre, et à propos duquel on veut enregistrer des informations.(ex : un agent)

Un Attribut : Un attribut est un identificateur (un nom) décrivant une information stockée dans une base. (ex : le nom de l’agent)

Une occurrence, ou n-uplets, ou tuples, est un élément de l’ensemble figuré par une relation. Autrement dit, une occurrence est une ligne du tableau qui représente la relation.

Une clé candidate d’une relation est un ensemble minimal des attributs de la relation dont les valeurs identifient à coup sûr une occurrence (ex : le matricule de l’agent).

-Une Clé primaire : La clé primaire d’une relation est une de ses clés candidates.

Pour signaler la clé primaire, ses attributs sont généralement soulignés.

Une Clé étrangère : Une clé étrangère dans une relation est formée d’un ou plusieurs attributs qui constituent une clé primaire dans une autre relation.

Une association ou relation : Elle établit un lien sémantique entre deux ou plusieurs entités

     Bases de données appliquées avec Microsoft ACCESS 2007

Microsoft Access est un programme de gestion de base de données relationnelle (SGBD). Il offre un ensemble d’outils permettant de saisir, de mettre à jour, de manipuler, d’interroger et d’imprimer des données. Il est intégré dans la suite Microsoft office. Nous avons les versions 97,  2000, 2003, 2007, 2010.

Pratiques essentiels pour créer une base de données ACCESS

Analyse du domaine d’étude 

Cette étape consiste en :

 La définition des objectifs, consistant à définir la finalité du projet.

 L’analyse des besoins et faisabilité, c'est-à-dire l'expression, le recueil et la formalisation des besoins du demandeur (le client) et de l'ensemble des contraintes. 

 Faire un inventaire des données 

Il s’agit de lister toutes les informations (données) ayants un intérêt pour l’utilisateur. Ces informations se trouvent généralement sur les documents de gestion (factures, bon de commande, fiche d’enquête, reçu de paiement, fiche d’entretien etc.).

Il est conseillé de préciser le type de données pour faciliter la création dans Access.

On parle généralement de dictionnaires de données

Exemple : Matricule (texte), Date de naissance (Date), Nombre d’enfants (entier). Très souvent il est fait une épuration des données (élimination des champs calculés, détection des polysémies, synonymies).

Exemple : TVA = Montant HT * 0,18. Si nous avons la donnée Montant HT, on déduit de là la TVA.

Identifier les entités 

En rappel, l’entité représente un objet (matériel ou immatériel) du monde réel.

Exemple : Un agent, un véhicule

On regroupe les informations d’un même sujet ensemble pour former une entité. Les professionnels utiliseront un graphe de dépendance fonctionnelle. Mais avec la pratique et la connaissance du métier il est possible de détecter les entités de façon, intuitive.

Exemple : je regroupe (matricule agent, nom agent, prénom agent, date de naissance) pour former l’entité Agent.

Trouver le schéma relationnel de la base de données

A cette étape, on identifie les clés primaires, les clés étrangères (à créer très souvent), les relations entre entités en tenant compte des règles de gestion.

.

Par soucis de compréhension nous allons adopter les termes suivants :

Entité mères, entités filles.

Comment détecter une clé primaire ?

Une donnée est considéré comme une clé primaire si et seulement si la connaissance de cette donnée permet d’identifier de façon unique une occurrence de l’entité.

Exemples : Si je connais le numéro matricule de l’agent, je peux identifier de façon unique l’agent concerné.

Si je connais le code de la facture, je peux ressortir une et une seule facture.

Comment détecter une entité mère ?

Si dans nos règles de gestion, une occurrence d’une entité A implique un ou plusieurs occurrence d’une entité B, alors A est une entité mères et B une entité

Filles.

Les enregistrements ou occurrences de A sont appelés enregistrement pères et ceux de B des enregistrements fils.

Exemple : Un agent peut prendre un ou plusieurs prêts.

Agent est une entité père et prêts une entité fille.

Comment détecter une relation entre entités ?

Il y’a une relation entre une entité A et une entité B s’il existe un lien sémantique entre A (entité pères) et B (entité filles).  La connaissance d’une occurrence de B implique la connaissance sans équivoque de l’occurrence correspondante de A.

Exemple : Si j’ai le numéro de la facture, je sais à quel client cette facture appartient.

Après l’identification des clés primaires éventuelles (obligatoires pour les entités pères), on migre cette clé au niveau de l’entité fille ; il s’agit de la clé étrangère.

Dans Access la relation est créée entre la clé primaire de A et la clé étrangère de B

Les objets fondamentaux de  ACCESS 

Les tables

C’est la structure fondamentale d’une base de données Access. Les tables stockent les données de la base. Ce sont les premiers objets à créer. Dans une table, les données sont organisées sous forme de champs (les colonnes) et d’enregistrements (les lignes).

Les requêtes

Une requête sert à poser des questions sur les données des tables et à réaliser des actions sur ces données. Par exemple, une requête répondra à la question « Combien de nos clients vivent en province et quels sont leurs noms et numéros de téléphone ?

 Les requêtes servent aussi à combiner (ou joindre) des données issues de tables séparées. Elles sont également utiles pour modifier, supprimer ou ajouter rapidement de gros volume de données. Elles servent également à la construction des formulaires et des états.

Les formulaires

Le formulaire est en quelque sorte la représentation à l’écran du formulaire imprimé classique. Il permet l’ajout de données dans la base de données tout en les présentant sous une forme utile et agréable : il peut être de facture simple ou très élaborée, avec des graphiques et des filets. Il existe des possibilités de remplissage automatique de zones en fonction des données entrées dans d’autres zones. Par ailleurs, un formulaire peut en contenir un autre, ce qui autorise l’entrée de données dans plusieurs tables.

Les états

L’état sert à afficher et imprimer les données sous une forme adéquate. A titre d’exemple, on peut citer les étiquettes de publipostage, les listes, les enveloppes, les factures. Un état sert aussi à présenter les résultats d’une requête. 

Les macros 

Une macro-commande est un ensemble d’instruction dont le rôle est d’automatiser une tâche que vous réaliser souvent. Lorsque vous exécutez une macro-commande, Access reproduit fidèlement les actions décrites, en respectant l’ordre dans lequel elles apparaissent. Sans avoir à écrire une seule ligne de code de programme, vous pouvez construire une macro-commande pour ouvrir automatiquement des formulaires, imprimer des étiquettes de publipostage, traiter des commandes … Les macros commandes permettent également de construire des applications exploitables par des utilisateurs ne connaissant rien ou peu d’Access : ces macro-commandes servent alors à la navigation entre les diverses tables, les formulaires …

Création des objets pas à pas avec ACCESS

Comment lancer ACCES ?

Cliquez sur le menu Démarrer,  Office et cliquez sur Microsoft Access 2007

Présentation succincte de l’interface de ACESS

Création de tables

Suivre les étapes suivantes pour créer une table dans Access 2007

1- Ouvrir la base de données concernée

3-   Saisir les informations (nom des champs, types de données)

4-   Cliquer sur création pour définir la clé primaire, puis enregistrer. Access vous demandera un nom pour la table

Le tableau suivant décrit les types de données disponibles pour des champs dans Office Access 2007.

Type   de données	Stockage	Taille
Texte	Caractères alphanumériques  Utilisés pour le texte ou le texte et les nombres qui ne sont pas utilisés dans des calculs (par exemple, l'ID d'un produit).	Jusqu'à 255 caractères.
Mémo	Caractères alphanumériques (dépassant 255 caractères) ou texte avec une mise en forme de texte enrichi. Utilisé pour le texte de plus de 255 caractères ou pour le texte qui utilise la mise en forme de texte enrichi. Dans le cas des notes, des descriptions longues et des paragraphes avec une mise en forme gras ou italique, il convient d'utiliser un champ Mémo.	1 giga-octet de caractères ou 2 giga-octets au maximum de stockage (2 octets par caractère) pour lesquels il est possible d'afficher 65 535 caractères dans un contrôle.
Numérique	Valeurs numériques (entiers ou valeurs fractionnaires). Permet de stocker des nombres à utiliser dans des calculs, à l'exception des valeurs monétaires (utilisez Devise comme type de données de valeurs monétaires).	1, 2, 4 ou 8 octets, ou 16 octets pour les numéros de réplication.
Date/Heure	Dates et heures. Permet de stocker des valeurs de type date et heure. Notez que chaque valeur stockée inclut un composant date et un composant heure.	8 octets.
Monnaie	Valeurs monétaires. Permet           de        stocker           des             valeurs monétaires (devises).	8 octets.
NuméroAuto	Valeur numérique unique qu'Office Access 2007 insère automatiquement lors de l'ajout d'un enregistrement. Permet de générer des valeurs uniques qui peuvent être utilisées comme clé principale. Notez que les champs NuméroAuto peuvent être incrémentés séquentiellement, par incréments que	4 octets ou 16 octets en cas d'utilisation pour un numéro de réplication.
	vous définissez ou par incréments choisis de façon aléatoire.
Oui/Non	Valeurs booléennes. Utilisées pour les champs Vrai/Faux qui peuvent contenir Oui/Non ou Vrai/Faux par exemple.	1 bit (8 bits = 1 octet).
Objet OLE	Objets OLE ou autres données binaires. Permet de stocker des objets OLE d'autres applications Microsoft Windows.	Jusqu'à 1 giga-octet.
Pièce jointe	Images, fichiers binaires, fichiers Office. Ce type de données est celui qui convient pour stocker des images numériques et tout type de fichier binaire.	Pour les pièces jointes compressées, 2 gigaoctets. Pour les pièces jointes non compressées, environ 700 ko selon le degré de compression de la pièce jointe.
Lien hypertexte	Liens hypertexte. Permet de stocker des liens hypertexte pour fournir un accès par un clic simple à des pages Web via une URL (Uniform Resource Locator) ou des fichiers via un nom au format UNC (Universal Naming Convention). Vous pouvez également établir un lien aux objets Access stockés dans une base de données.	1 giga-octet de caractères ou 2 giga-octets au maximum de stockage (2 octets par caractère) pour lesquels il est possible d'afficher 65 535 caractères dans un contrôle.
Assistant Liste            de choix	Il ne s'agit en fait pas d'un type de données. Cela permet d'appeler l'Assistant Liste de choix. Permet de démarrer l'Assistant Liste de choix afin que vous puissiez créer un champ qui utilise une zone de liste déroulante pour rechercher une valeur dans une autre table, une requête ou une liste de valeurs.	Basé sur une table ou une requête : taille de la colonne liée. Basée sur une valeur : taille du champ Texte utilisé pour stocker la valeur.

Le tableau suivant présente les propriétés de champ disponibles.

Utilisez propriété  de champ	cette	Pour
TailleChamp		Définir la taille maximale des données stockées sous le type Texte, Nombre ou NuméroAuto.
Format		Personnaliser l'apparence d'un champ imprimé ou affiché.
Décimales		Spécifier le nombre de décimales à utiliser pour afficher des
		nombres.
NouvellesValeurs		Définir si un champ NuméroAuto est incrémenté ou s'il reçoit une valeur aléatoire.
MasqueSaisie		Guider la saisie des données.
Légende		Afficher le texte par défaut dans des étiquettes pour les formulaires, les états et les requêtes.
ValeurParDéfaut		Attribuer automatiquement une valeur par défaut à un champ lorsque des nouveaux enregistrements sont ajoutés.
ValideSi		Fournir une expression qui doit être vraie à chaque fois que vous ajoutez ou modifiez la valeur de ce champ.
MessageSiErreur		Taper le texte qui apparaît lorsqu'une valeur viole l'expression ValideSi.
Requise		Demander que des données soient saisies pour un champ.
ChaîneVideAutorisée		Autoriser une chaîne vide ("") dans un champ Texte ou Mémo (en attribuant la valeur Oui.
Indexé		Accélérer l'accès aux données du champ par la création et l'utilisation d'un index.
UnicodeCompression		Compresser le texte stocké dans ce champ lorsqu' une grande quantité de texte est stocké (plus de 4 096 caractères).
IMEMode		Contrôler la conversion des caractères dans une version asiatique de Windows.
ModeFormulationIME		Contrôler la conversion des caractères dans une version asiatique de Windows.
SmartTags		Associer des balises actives à ce champ.
AjouterUniquement		Autoriser le suivi de version (en affectant la valeur Oui) d'un champ Mémo.
FormatTexte		Choisir le format Texte enrichi pour stocker du texte au format HTML et autoriser la mise en forme de texte enrichi. Choisir Texte brut pour stocker du texte uniquement.
AlignerTexte		Spécifier l'alignement par défaut du texte dans un contrôle.
Précision		Spécifier le nombre total de chiffres autorisés, y compris à droite et à gauche de la virgule.
Échelle		Spécifier le nombre maximal de chiffres qui peuvent être stockés à droite de la virgule.

Le tableau suivant fournit des informations supplémentaires sur les propriétés de champ TailleChamp, Format et NouvellesValeurs .

Propriété TailleChamp	Description
Texte	Taper une valeur comprise entre 1 et 255. Les champs de texte peuvent contenir entre 1 et 255 caractères. Pour les champs devant contenir un texte plus long, utiliser le type de données Mémo.
Numérique	Sélectionnez l'une des options suivantes :         •      Octet — Pour les valeurs numériques comprises entre 0 et 255

	caractères. Stockage sur un octet. •    Entier — Pour les valeurs numériques comprises entre -32 768 et +32 768 caractères. Stockage sur deux octets. •    Entier long — Pour les valeurs numériques comprises entre 2 147 483,648 et +2 147 483,647 caractères. Stockage sur quatre octets.  Conseil   Utilisez la propriété Entier long pour stocker sous forme de clé étrangère la valeur qui apparaît dans le champ de clé primaire NuméroAuto d'une autre table. •    Réel simple — Pour les valeurs numériques à virgule flottante comprises entre -3,4 x 1038 et +3,4 x 1038 avec jusqu'à sept chiffres après la virgule. Stockage sur quatre octets. •    Réel double — Pour les valeurs numériques à virgule flottante comprises entre -1,797 x 10308 et +1,797 x 10308 avec jusqu'à quinze chiffres après la virgule. Stockage sur huit octets. •    N° de réplication — Pour le stockage d'un identific ateur unique global requis pour la réplication. Stockage sur seize octets. Notez que la réplication n'est pas prise en charge pour le format de fichier .accdb.  •    Décimal — Pour les valeurs numériques comprises entre 9,999 x 1027 et +9,999 x 1027. Stockage sur douze octets.
NuméroAuto	Sélectionnez l'une des options suivantes : •    Entier long  — Pour les valeurs numériques uniques comprises entre 1 et +2 147 483 648 lorsque la propriété de champ NouvellesValeurs est définie sur Incrément et pour les valeurs comprises entre -2 147 483 648 et +2 147 483 647 lorsque la propriété de champ NouvellesValeurs est définie sur Aléatoire. Stockage sur 4 octets. •    N° de réplication — Pour le stockage d'un identific ateur unique global requis pour la réplication. Stockage sur seize octets. Notez que la réplication n'est pas prise en charge pour le format de fichier .accdb.
Propriété de mise en forme
Texte	Vous pouvez définir un format personnalisé.
Numérique	Sélectionnez l'une des options suivantes : •    Nombre général  — Affiche le nombre tel qu'il a été tapé. Par exemple, 3 456,789 est affiché 3 456,789 •    Devise  — Affiche le nombre utilisant le séparateur de milliers et applique les paramètres dans les Options régionales et linguistiques du Panneau de configuration pour les montants négatifs, les symboles décimaux et monétaires et les décimales.

	Par exemple, 3 456,789 est affiché $3,456.789 •    Euro  — Affiche le nombre avec le symbole monétaire Euro, quel que soit le symbole spécifié dans les Options régionales et linguistiques. •    Fixe  — Affiche au moins un chiffre et applique les paramètres des Options régionales et linguistiques du Panneau de configuration pour les montants négatifs, les symboles décimaux et monétaires, et les décimales. Par exemple 3 456,789 est affiché 3 456,79 •    Standard — Affiche le nombre utilisant le séparateur de milliers et applique les paramètres définis dans les Options régionales et linguistiques du Panneau de configuration pour les montants négatifs, les symboles décimaux et les décimales. Ce format n'affiche pas de symbole monétaire. Par exemple, 3 456,789 est affiché 3 456,79 •    Pourcentage — Multiplie la valeur par 100 et affiche le nombre avec un signe de pourcentage à la fin. Applique les paramètres définis dans les Options régionales et linguistiques du Panneau de configuration pour les montants négatifs, les symboles décimaux et les décimales. Par exemple, 0,3456 est affiché 35 % •    Scientifique — Affiche la valeur en notation scientifique standard. Par exemple, 3 456,789 est affiché 3 46E + 03
Date/Heure	Sélectionnez l'un des formats d'affichage prédéfinis suivants : •    Date, général  — Affiche la valeur utilisant une combinaison des paramètres Date, abrégé et Heure, complet.  •    Date, complet — Affiche la valeur utilisant le paramètre Date, complet défini dans les Options régionales et linguistiques du Panneau de configuration. •    Date, réduit — Affiche la valeur utilisant le format jj-mmm-aa (14-Jul-06, par exemple). •    Date, abrégé — Affiche la valeur utilisant le paramètre Date, abrégé défini dans les Options régionales et linguistiques du Panneau de configuration. •    Heure, complet — Affiche la valeur utilisant le paramètre Heure, complet défini dans les Options régionales et linguistiques du Panneau de configuration. •    Heure, réduit — Affiche la valeur utilisant le format HH:MM PM où HH correspond à l'heure, MM aux minutes et où PM peut être remplacé par AM. L'heure peut être comprise entre 1 et 12 et les minutes entre 0 et 59.  •    Heure, abrégé — Affiche la valeur utilisant le format HH:MM où HH correspond à l'heure et MM aux minutes. L'heure peut
	être comprise entre 0 et 23 et les minutes entre 0 et 59.
Oui/Non	Sélectionnez l'une des options suivantes : •    Vrai/Faux — Affiche la valeur et indique Vrai ou Faux.  •    Oui/Non — Affiche la valeur et indique Oui ou Non. •    Actif/Inactif — Affiche la valeur et indique Actif ou Inactif.  Remarque   Les mentions Actif, Vrai et Oui sont équivalentes ainsi que Faux, Non et Inactifs.
Propriété NouvellesValeurs
NuméroAuto	Sélectionnez l'une des options suivantes (uniquement pour les types de données Numéro Auto) : •    Incrément — La valeur de départ est 1 et est incrémentée de 1 pour chaque nouvel enregistrement. •    Aléatoire — La valeur de départ est aléatoire et chaque nouvel enregistrement reçoit une valeur aléatoire.

Création de relation entre tables

1-   Cliquez sur outils de bases de données 

2-   Cliquez sur relation

3-   Ajouter les tables concernées par la relation

4-   Glisser-déposer la clé primaire de la table mère vers la clé étrangère de la table fille

5-   Cliquer sur créer

Nous avons le résultat suivant : relation un à plusieurs entre la table agents et la table prêts : Un agent peut contracter plusieurs prêts.

Création de requêtes

Il existe plusieurs types de requêtes :

 Les requêtes sélection qui permettent d’interroger les tables pour afficher des résultats (ex : liste des agents). On peut faire des tris, des regroupements, des calculs statistiques et numériques,  des analyses croisées etc. sur les requêtes (voir TP).

 Les requêtes « action » permettent d’agir sur les données des tables Ex : requête ajout, suppression, mises à jour .

Une réflexion préalable est nécessaire pour la création d’une requête :

•   Lister les tables en présence

•   Inventorier les champs nécessaires (tenir compte des calculs)

•   Ressortir les tris, les critères de sélection, les regroupements etc. éventuels • Ensuite procéder comme ci-dessous

Cliquez sur assistant requête simple et suivre l’assistant pas à pas

Création de formulaire 

Dans ce document, nous nous intéressons à la création des formulaires par l’assistant (génération automatique). Toutefois la création manuelle est indispensable à un certain niveau.

Comment générer un formulaire (à partir de la table)

1-    Ouvrir la table ou la requête concernée

2-    Cliquer sur créer

3-    Puis sur formulaire

Ouverture de la table (clic droit sur la table puis ouvrir)

Formulaire généré par Access

Création d’états

La création d’un état est similaire à celle d’un formulaire Il suffit de :

1-    Ouvrir la table ou la requête concernée

2-    Cliquer sur créer

Ouverture de la table (clic droit sur la table puis ouvrir)

    Exercices

Exercice 1

Vous êtes chargés en tant que gestionnaire d’assister le service informatique dans la mise en œuvre d’une base de données pour la gestion des prêts au sein de votre entreprise.

Cette gestion concerne plusieurs agents. Un agent est identifié par son matricule, son nom et prénom, sa date de naissance, sa date d’embauche.

Un prêt est caractérisé par son montant, sa durée en mois, la date de début de paiement. 

Il est à noter qu’un agent peut bénéficier de plusieurs prêts remboursables en plusieurs échéances.

A chaque paiement le caissier enregistre le montant du paiement, la date de paiement.

Travail à faire :

1) Identifier toutes les données (propriétés) du domaine d’étude avec leur type

2°) A partir de ces données,  identifier les entité s (tables) du modèle ainsi que les relations existantes entre elles.

3     °) Pour quelle entité la clé primaire est-elle ob ligatoire ?

4     °) Faire un modèle pour ce cas en représentant pa rticulièrement les clés primaires, les clés secondaires, les tables.

5     °) Créer les tables sous ACCESS 

Exercice 2

L’entreprise Flora Tech souhaite moderniser la gestion de sa clientèle d’affaires. Elle décide donc de mettre en place un système informatique de gestion de la relation client.

Vous êtes chargés d’élaborer le cahier des charges utilisateurs à partir des informations suivantes :

Flora Tech dispose de clients identifiés par un code, une désignation, un numéro de téléphone, une adresse mail, un nombre d’employés, une localisation.

Dans le cadre de la gestion de proximité des visites sont faites aux clients. Flora Tech souhaite enregistrer donc la date de la visite, le motif de la visite, le résumé de la visite pour chaque client qui peut être visité plusieurs fois.

Aussi afin de maitriser les insatisfactions il sera important d’enregistrer les réclamations clients caractérisés par le code de la réclamation, le résumé de la réclamation, la date de la réclamation. Le client à la possibilité de réclamer plusieurs fois.

Travail à faire

1  °) Etablir la liste des propriétés (données) avec leur type

2  °) Identifier les entités pères et filles à parti r des règles de gestion

3  °) Faire ressortir les clés primaires et les clés étrangères

4  °) Elaborer un modèle en faisant ressortir les ta bles et les relations existantes entre elles.

5  °) Créer la base de données sous Access

Exercices sur les requêtes

A partir de la base de données gestion des prêts, Créer les requêtes suivantes :

1  °) Liste des agents par ordre de numéro matricule croissant comportant le matricule, le nom, le prénom et la date de naissance.

2  °) Liste des agents avec les années de naissance (ex 2000.1977 etc.)

3  °) La liste des agents dont le nom comme par O et se terminant par O

4°) La liste des agents qui sont mariés ou qui sont nés entre le 01/01/1997 et le

31/12/2000

5  °) La liste des agents avec une colonne concaténé du nom et prénom nommée Agent

6  °) Le nombre d’agents (a chercher sur le net, voir les groupes)

NB : Liste des agents contient au moins le matricule, le nom, le prénom

Ressources pédagogiques