Transcript La programmation de bases de données Access en Visual Basic

Chapitre 3
La programmation de bases de
données Access en Visual Basic
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

...
2
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
3
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
4
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
5
Traitements associés à un formulaire ou à un état (suite)
Pour désigner
Utiliser
exemple
un formulaire
Forms![nom formulaire]
Forms![saisie employés]
un contrôle d'un
formulaire
Forms![nom formulaire]![ nom
contrôle]
Forms![saisie
employés]![numemp]
une propriété d’un
contrôle d'un form.
Forms![nom formulaire]![ nom
contrôle].propriété
Forms![saisie
employés]![numemp].visible
un état
Reports![nom état]
Reports![employés]
un contrôle d'un état
Reports![nom état]![nom
contrôle]
Reports![employés]![numemp]
une propriété d’un
contrôle d'un état
Reports![nom état]![nom
contrôle].propriété
Reports![employés]![numemp]
.visible
 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
6
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
7
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 : Set var = objet
 Exemple :
Sub essai()
dim bd as Database
dim t as Table
set bd = currentDB()
set t = bd.OpenTable("employés")
…
End Sub
8
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
9
Manipulation d’une BD en Visual Basic
 Ouverture et fermeture d'une base de données :
Fonction
Signification
OpenDatabase(chemin) ouvre une BD
CurrentDB()
var-bd.Close
Exemple
set bd1 = OpenDatabase
("c:\employe.mdb")
retourne la BD en set bd1 = CurrentDB()
cours d'utilisation
bd1.Close
ferme une BD
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)
10
Manipulation de tables
 Ouverture d’une table en mode visualisation des enreg. :
Fonction
Signification
ouvre une table en mode visualisation
DoCmd.OpenTable "nom table"
 Collections :
Collections
variable-TableDef.Fields
variable-TableDef.Indexes
Signification
liste des champs de la table
liste des index de la table
 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
11
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
12
Manipulation de requêtes
 Ouverture d’une requête en mode visualisation du résultat :
Fonction
DoCmd.OpenQuery "nom requête"
Signification
ouvre une requête en mode visualisation
du résultat
 Champs et collections :
champs et collections
Signification
texte SQL de la requête
variable-queryDef.SQL
variable-queryDef.Fields
liste des champs affichés par la requête
 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"
13
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
14
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
15
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éé, il est positionné sur le 1e enreg. de
l'ensemble
 variable d'enregistrement
 Accès aux champs d'un enregistrement : var-enregistrement![champ]
 Exemple : r![nom]
16
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
17
Déplacement dans un Recordset
 Les Recordset permettent de se déplacer d'un enregistrement à un autre
Fonction
var-enreg.MoveFirst
var-enreg.MoveLast
var-enreg.MoveNext
var-enreg.MovePrevious
var-enreg.EOF
Signification
le premier enreg. devient l'enreg. en cours
le dernier enreg. devient l'enreg. en cours
l'enreg. suivant devient l'enreg. en cours
l'enreg. précédent devient l'enreg. en cours
le dernier enreg. a été franchi
 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
18
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
19
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
20
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
21
End Sub
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 :
Fonction
Signification
variable.FindFirst critère
variable.FindLast critère
variable.FindNext critère
variable.FindPrevious critère
variable.NoMatch
le premier enreg. vérifiant le critère devient l'enreg. en cours
le dernier enreg. vérifiant le critère devient l'enreg. en cours
l'enreg. suivant vérifiant le critère devient l'enreg. en cours
l'enreg. précédent vérifiant le critère devient l'enreg. en cours
le dernier enreg. vérifiant le critère a été franchi
 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
22
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
23
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
24
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
25