Chapitre 3 La programmation de bases de données Access en Visual Basic

1. Chapitre 3La programmation de bases de données Access en Visual Basic

2. Pourquoi programmer une base de données ? • La programmation a d'innombrables applications dans le domaine des bases de données : • Réalisation de traitements sur des ensembles d'enregistrements provenant de tables ou de requêtes • Réalisation de traitements dans des formulaires ou des états • Import/export de données • Interaction avec le système d'exploitation (création/suppression de répertoires, ...) • Automatisation de sauvegardes • ...

3. Programmer une base de données Access en VB • Deux solutions : 1. Utiliser VB standard : • VB permet de manipuler une BD Access: créer consulter, modifier les tables et leur contenu, lancer des requêtes et récupérer leur résultat, ... • avantage : permet de générer un fichier exécutable (.exe) • inconvénient : nécessite de développer soi-même les formulaires, les états 2. Utiliser VBA (Access Basic) – VB de Access • avantage : développement plus rapide grâce aux formulaires et aux états • inconvénient : ne permet pas de générer un fichier exécutable – chaque utilisateur de la BD doit avoir Access sur son poste ou un runtime d’Access (livré avec Access 97 Office Édition Développeur) • 2e solution étudiée dans le cours

4. Programmer une BD Access : VBA vs macros • En Access, il existe deux moyens de programmer une BD : • les macros • la programmation VB • Les macros : • assez simples à écrire • très difficiles à maintenir • peu réutilisables d’un projet à l’autre • permettent seulement de réaliser des traitements simples • La programmation VB : • nécessite des compétences en programmation • plus puissant : permettent d’effectuer des traitements complexes • plus facile à maintenir que les macros • plus de possibilités de réutiliser du code entre différents projets • nécessaire pour tirer partie des assistants qui génèrent du VB • Sauf cas particulier, ne pas utiliser les macros

5. Traitements associés à un formulaire ou à un état • La programmation en VB pour Access (VBA) s'effectue comme en VB: • dans un formulaire, par le biais des événements (procédures événementielles) • dans un module • … mais il existe des différences importantes entre VBA et VB standard: • les étiquettes fonctionnent en tandem avec les zones de texte • les noms des contrôles peuvent contenir des espaces mais doivent être alors placés entre [ et ] • il ne faut pas utiliser la propriété Text pour les zones de texte • la propriété "Source Contrôle" peut contenir des formules (précédées de =) • lorsqu’un champ n’est pas rempli, il a la valeur Null et non pas "" • l’ouverture de formulaires s’effectue par DoCmd.OpenForm "nom formulaire" • la fermeture des formulaires s’effectue par DoCmd.Close

6. Traitements associés à un formulaire ou à un état (suite) • Pour accéder à un contrôle dans le formulaire ou l’état courant, [nom contrôle] suffit • Lorsqu'un nom de formulaire, d'état ou de contrôle ne comporte pas d'espace, les crochets [] sont facultatifs

7. Exercice 1 • On considère la BD suivante : employés(numemp, numinsee, nom, prénom, date naissance, marié, salaire, salaire conjoint) projets(projet) réalisations(numemp, projet, date, temps) 1. Réaliser cette base de données sous Access 2. Réaliser le formulaire de saisie d’un employé suivant : • Le bouton Valider effectue les contrôles de validité, sauve l’enregistrement saisi, s’il ne contient pas d’erreur, et ferme le formulaire • Le bouton Annuler ferme le formulaire sans sauver • Si la case marié n’est pas cochée, la zone de texte salaire conjoint et l’étiquette correspondant sont grisées • La zone de texte salaire total affiche à tout instant la somme de salaire et salaire conjoint

8. Les types prédéfinis d’objets Access • Principaux types spécifiques pour les BD : • Database (base de données) • Form (formulaire) • Report (état) • Control (contrôle dans un formulaire ou dans un état) • QueryDef (définition de requête) • Table (table d'une base de données) • Recordset (jeu d’enreg. provenant d’une table ou d'une requête) • Affectation pour un objet de BD : Setvar =objet • Exemple : Sub essai() dim bd as Database dim t as Table set bd = currentDB() set t = bd.OpenTable("employés") … End Sub

9. Les collections • Une collection Access est une séquence d’éléments • Pour une base de données : • collection de tables : TableDefs • collections des requêtes : QueryDefs • collection des formulaires : Forms • collection des états : Reports • Pour une table ou une requête : • collection des enregistrements : Recordset • Une collection est accessible par : • son nom: bd.TableDefs("employés") • sa position (à partir de 0) ; ex: bd.TableDefs(0) est la première table de bd • son nombre d’éléments : Count; ex: bd.TableDefs.Count • Exemple : For i = 0 to CurrentDB().TableDefs.Count - 1 Debug.Print CurrentDB().TableDefs(i).name Next i

10. Manipulation d’une BD en Visual Basic • Ouverture et fermeture d'une base de données : C'est par l'intermédiaire d'une variable de type Database que l'on peut effectuer des opérations sur une BD (voir exemple bd1 ci-dessus)

11. Manipulation de tables • Ouverture d’une table en mode visualisation des enreg. : • Collections : • Exemple : dim bd as Database dim t as TableDef Set bd = currentDB() Set t = bd.TableDefs("employés") For i = 0 to t.Fields.count - 1 Debug.Print t.Fields(i).name Next i

12. Exercice 2 1. Ecrire la fonction existe_table(nom_table as String) as boolean qui retourne True si il existe dans la BD courante une table portant le nom nom_table 2. Ecrire la procédure ouvre_table(nom_table as String) qui ouvre la table de nom nom_table en mode visualisation, si elle existe, et affiche un message d’erreur sinon

13. Manipulation de requêtes • Ouverture d’une requête en mode visualisation du résultat : • Champs et collections : • Exemple : dim bd as Database dim q as QueryDef Set bd = CurrentDB() Set q = bd.QueryDefs("req1") ' req1 est une requête de la BD q.SQL = "SELECT * FROM employés" DoCmd.openQuery "req1"

14. Exercice 3 • Construire le formulaire suivant de recherche des employés en fonction de leur salaire: • la boîte combo de gauche permet le choix du champ sur lequel on veut effectuer la recherche : "salaire" ou "salaire conjoint" • la boîte combo du centre permet le choix de l’opérateur voulu : >, = ou < • la zone de texte de droite permet de taper une valeur • le bouton chercher lance la requête correspondant aux éléments choisis et affiche le résultat 3

15. Jeux d'enregistrements : les Recordset • Il est très fréquent, pour réaliser un traitement, de devoir parcourir ou analyser les enregistrements provenant d’une table ou d’une requête : • Recordset • Trois types de Recordset : • Table: jeu d’enreg. provenant d’une table. Permet d’ajouter, modifier ou supprimer des enregistrements • Dynaset: jeu d’enreg. provenant d'une requête. Permet d’ajouter, modifier ou supprimer des enreg. dans une ou plusieurs tables sous-jacentes • Snapshot: jeu d’enreg. provenant d’une d'une requête, mais ne permettant pas de mettre à jour les enregistrements • Le choix du type de Recordset à utiliser dépend de : • l'utilisation qui doit en être faite • l’aspect multi-utilisateurs de la BD

16. Manipulation des Recordset • Les Recordset doivent être déclarés comme des variables : Exemple: Dim r as Recordset • Création d’un Recordset : set var-enregistrement = var-bd.OpenRecordset(table-ou-requete,type): • table-ou-requête : nom d’une table ou d’une requête de la BD ou encore une chaîne de car. représentant une requête SQL • type (facultatif) : une des constantes suivantes : dbOpenTable (table), dbOpenDynaset (dynaset) et dbOpenSnapshot (snapshot) Exemple: set r = currentDb().OpenRecorset("employés") • Lorsqu'un Recordset est créé, ilestpositionnésur le 1eenreg. de l'ensemble  variable d'enregistrement • Accès aux champs d'un enregistrement : var-enregistrement![champ] • Exemple : r![nom]

17. Manipulation des Recordset (suite) • Fermeture d’un Recordset : • une fois la manipulation d’un Recordset terminée, il faut fermer ce dernier par l’instruction : var-enregistrement.Close Exemple: Dim r as Recordset set r = currentDB().openRecordset("select [numinsee], [nom] from employés") Debug.print r![nom] ’affiche le nom du 1e employé sélectionné r.Close

18. Déplacement dans un Recordset • Les Recordset permettent de se déplacer d'un enregistrement à un autre • Exemple : Sub afficher_noms_prenoms () Dim bd as Database Dim r as Recordset Set bd = CurrentDB() Set r = bd.OpenRecordset("employés") do while not r.EOF Debug.Print r![nom], " ", r![prénom] r.MoveNext loop r.Close End Sub

19. Modification des Recordset • Modifier un enregistrement : 1. Positionner la variable d'enregistrement sur l'enregistrement voulu 2. Placer l'enregistrement en mode édition : var-enregistrement.Edit 3. Modifier la valeur des champs : var-enregistrement![champ] =valeur 4. Effectuer la mise à jour de la BD : var-enregistrement.Update • Exemple : Sub augmenter_salaires() Dim bd as Database Dim r as Recordset Set bd = CurrentDB() Set r = bd.OpenRecordset("employés") do while not r.EOF r.Edit r![salaire] = 1.1 * r![salaire] r.Update r.MoveNext loop r.Close End Sub

20. Suppression d’enregistrement dans un Recordset • Supprimer un enregistrement : 1. Positionner la variable d'enregistrement sur l'enregistrement voulu 2. Supprimer l'enregistrement : var-enregistrement.Delete • Exemple: supprimer le 1e enregistrement d’une table Sub supprimer_premier_enregistrement() Dim bd As Database Dim r As Recordset Set bd = CurrentDb() Set r = bd.OpenRecordset("employés") r.Delete r.Close End Sub

21. Ajout d’enregistrement dans un Recordset 1. Créer l'enregistrement: var-enregistrement.AddNew 2. Remplir la valeur des champs: var-enregistrement![champ] = valeur 3. Effectuer la mise à jour de la BD: var-enregistrement.Update • Exemple: Sub ajouter_employé(numinsee As String, nom As String, prénom As String, date_naissance As Date, marié As Boolean, salaire As Single, salaire_conjoint As Single) Dim bd As Database Dim r As Recordset Set bd = CurrentDb() Set r = bd.OpenRecordset("employés") r.AddNew r![numinsee] = numinsee ’ numemp n’est pas saisi car NumAuto r![nom] = nom r![prénom] = prénom r![date naissance] = date_naissance r![marié] = marié r![salaire] = salaire r![salaire conjoint] = salaire_conjoint r.Update r.Close End Sub

22. Recherche dans un Recordset • Un critère est une chaîne de caractères matérialisant une condition de sélection pouvant être utilisée par une des fonctions suivantes : • Exemple: Sub plafonner_salaires() Dim bd as Database Dim r as Recorset Set bd = CurrentDB() r.FindFirst "[salaire] > 20000" do while not r.NoMatch r.Edit r![salaire] = 20000 r.Update r.FindNext "[salaire] > 20000" loop r.Close End Sub

23. Recherche (suite) • La fonction DLookup(champ, table-ou-requete, critère) permet d’effectuer une recherche d’information sans Recordset • table-ou-requete est le nom d'une table, d'une requête, ou une requête exprimée en SQL • Remarque si plusieurs enregistrements vérifient le critère, le premier est retourné • Exemple: Function num_insee(nom As String) As String Dim result ’result n’a pas de type car cela peut être une ’chaîne ou la valeur Null result = (DLookup("[numinsee]", "employés", "[nom] = '" & nom & "'")) If Not IsNull(result) Then num_insee = result Else num_insee = "" End If End Function

24. Exercice 4 1. Ecrire une procédure qui affiche (dans la fenêtre de débogage) le nom, le prénom et l’âge de tous les employés qui sont mariés. (D’abord écrire la fonction qui calcule l’âge) 2. Mettre tous les noms des employés en majuscules dans la table employés 3. Construire le formulaire suivant : • La boîte combo "employé" fait apparaître le n°, le nom et le prénom des employés • Lorsqu'un employé est sélectionné dans la boîte combo, l’âge de cet employé apparaît dans une boîte de message

25. Exercice 5 Ecrire une procédure qui permet de réaliser l’import du fichier de réalisations M:\1-formations\…\msg2si\vbasic\réalisations0101.xls dans la table réalisations