Les spécificités de DAO sous Microsoft Access 2007 et ultérieurs

Christophe WARIN

Ce support est la suite de Définition et manipulation de données avec DAO consacrée aux nouveautés de Microsoft Access 2007 et 2010.

Commentez

- Titre : Les spécificités de DAO sous Microsoft Access 2007 et ultérieurs

- Auteur : Christophe WARIN

- Parution : 26 janvier 2014

- Mise à jour : 26 janvier 2014

- Licence : Copyright ® 2014 Christophe WARIN. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents, images, etc. sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts.

Sommaire

- I. Introduction

- II. L'objet Database

- II-A. Ouverture d'une base de données

- II-B. Création d'une base de données

- II-C. Fermeture et libération d'une base de données

- II-D. Maintenance d'une base de données

- II-D-1. Compactage de la base de données

- II-D-2. Changement du mot de passe

- III. Structure des données

- III-A. Rappels

- III-B. Le champ mémo

- III-C. Les champs à valeurs multiples

- III-D. Les champs pièces jointes

- III-E. Les champs calculés

- III-F. L'objet DAO.Field2

- IV. Accès aux données

- IV-A. Rappels

- IV-B. L'objet QueryDef

- IV-C. Les champs mémo

- IV-D. Les champs multivalués

- IV-E. Les champs pièce jointe

- IV-E-1. Lecture des données (enregistrement sur le disque)

- IV-E-2. Écriture des données (envoi du fichier dans la table)

- IV-F. Les champs calculés

- V. Conclusion

- VI. Lire aussi

I - Introduction

Avec les versions 2000 et 2002 d'Office, Microsoft avait mis en avant le modèle d'objet ADO apparu avec VB 6.0 relayant en second plan DAO issu de VB 4.0. Bien que le modèle DAO soit d'avantage tourné vers le moteur JET (moteur de base de données d'Access) que son successeur ADO, il n'était pas rare de voir certains développeurs recommander systématiquement l'utilisation d'ADO, gage selon eux de stabilité et de performances. Certains allaient même plus loin en prédisant une mort à court terme de DAO. Microsoft Access 2007 aura su donner tort à ces gens-là puisque d'une part toutes les nouveautés ont été intégralement reprises dans le modèle DAO et d'autre part, la référence Microsoft ActiveX Data Object Library a vu son activation par défaut retirée des nouvelles applications au profit de celle de DAO. La version 2010 du célèbre SGBDR continuant dans ce sens, la rédaction de ce document devenait une évidence. Vous y trouverez l'ensemble des spécificités de ces deux versions appliquées au Data Access Object Model.

Si vous travaillez sur une application Microsoft Access, aucune référence supplémentaire n'est à ajouter à votre projet. En revanche si vous développez depuis une autre application Office, n'oubliez pas d'activer la bibliothèque Microsoft Office 12(14).0 Access Database Engine Object via le menu Outils->Références de l'éditeur Visual Basic.

Ajout d'une référence

II - L'objet Database

Un objet Database représente une base de données ouverte dans une session utilisateur. Cette notion de session utilisateur aussi nommée espace de travail (Workspace) a perdu un peu de son sens du fait de l'abandon de la sécurité de niveau utilisateur depuis Access 2007, si bien que les objets de type Workspace sont complètement oubliés du développeur VBA. Les rares cas où ils sont encore explicitement définis et ouverts concernent des traitements spécifiques nécessitant des transactions isolées, dont vous pourrez trouver des exemples dans les liens figurant à la fin du document.

II-A - Ouverture d'une base de données

L'ouverture d'une base de données est réalisée depuis l'objet racine de DAO : DBEngine. Celui-ci représente le moteur de base de données. Deux cas de figure peuvent être présentés :

Le code est exécuté dans Microsoft Access :

Dans ce cas, un objet DBEngine est déjà actif et peut être atteint via l'instruction suivante :

Application.DBEngine

Ou, plus rapidement par :

DBEngine

Le code est exécuté dans une autre application Office :

Dans ce cas, l'objet DBEngine doit être déclaré et instancié. Exemple sous Excel :

Sub Ouverture()
Dim oDb As DAO.DBEngine
Set oDb = New DAO.DBEngine
End Sub

Quel que soit le mode d'accès à un objet DBEngine, sa méthode OpenDatabase permet d'ouvrir la base de données passée en paramètre.

Prototype :

.OpenDatabase(Name, Options, ReadOnly, Connect)

- Name : chemin de la base de données accdb ou accde (mdb pour les anciennes versions non concernées par ce tutoriel) ;

- Options : True pour un accès en exclusif, False pour un accès partagé ;

- ReadOnly : True pour un accès en lecture seulement, False dans le cas contraire ;

- Connect : informations de connexion.

Dans la mesure où le présent document traite uniquement de l'accès aux données d'une base Access, nous réduirons le paramètre Connect au passage du mot de passe de la base de données.

L'objet résultant de la méthode OpenDatabase est de type DAO.Database. Comme tout objet en VBA, son affectation doit être réalisée à l'aide du mot-clé Set.

Exemple :

Sub Ouverture()

Dim oDb As DAO.Database
Dim strChemin As String

strChemin = "c:\dbclient.accdb"
Set oDb = Application.DBEngine.OpenDatabase(strChemin, False, False)
End Sub

Si la base de données est introuvable, une erreur d'exécution 3024 est levée.

L'ouverture d'une base de données sécurisée par un mot de passe système est obtenue de manière analogue, à la différence près que le paramètre Connect doit être spécifié. Celui-ci est de la forme : ;PWD=motdepasse;

Exemple depuis Excel :

Sub Ouverture()

Dim oDBE As DAO.DBEngine
Dim oDb As DAO.Database
Dim strChemin As String

strChemin = "c:\dbclient.accdb"

Set oDBE = New DBEngine
Set oDb = oDBE.OpenDatabase(strChemin, True, False, ";PWD=test;")
End Sub

Dans le cas où le mot de passe est invalide, l'erreur d'exécution 3031 est levée. Si la chaîne Connect est mal orthographiée, notamment au niveau des points-virgules, l'erreur 3151 vous sera retournée.

Rappelons que sous Access, il est inutile d'utiliser la méthode OpenDatabase afin d'accéder à la base de données courante. Utilisez simplement la méthode CurrentDb de l'objet Application.

Sub Ouverture()

Dim oDb As DAO.Database

Set oDb = Application.CurrentDb
End Sub

Ou :

Sub Ouverture()

Dim oDb As DAO.Database

Set oDb = CurrentDb
End Sub

II-B - Création d'une base de données

À défaut de pouvoir ouvrir une base existante, il peut être nécessaire d'en créer une. La méthode CreateDatabase de l'objet DBEngine retourne la base de données nouvellement créée dont le nom et le chemin sont passés en paramètres.

Prototype :

.CreateDatabase(Name, Locale, Option)

- Name : chemin du fichier à créer ;

- Locale : constante permettant de spécifier l'ordre de tri des données (collation). Valeur fréquemment utilisée en France : dbLangGeneral ;

- Option : constante permettant de définir la version du fichier de données. dbVersion140 pour Access 2010 et dbVersion120 pour Access 2007.

Exemple :

Sub Creation()

Dim oDb As DAO.Database
Dim strChemin As String

strChemin = "c:\dbTest.accdb"
Set oDb = DBEngine.CreateDatabase(strChemin, dbLangGeneral, dbVersion140)
End Sub

Si la base de données existe déjà, l'erreur 3204 est levée.

Le paramètre Locale permet aussi de spécifier un mot de passe afin de sécuriser la base dès sa création.

Sub Creation()

Dim oDb As DAO.Database
Dim strChemin As String

strChemin = "c:\dbTestS.accdb"
Set oDb = DBEngine.CreateDatabase(strChemin, dbLangGeneral &amp; ";PWD=test;", dbVersion140)
End Sub

II-C - Fermeture et libération d'une base de données

La fermeture d'une base de données se fait simplement en appelant la méthode Close de l'objet Database correspondant. À ce stade, seule la connexion physique du moteur de base de données est fermée, cependant l'espace mémoire alloué à l'objet Database n'est pas libéré. Celui-ci ne sera effectivement détruit qu'en fin de procédure si l'objet avait une portée locale ou bien lors de l'affectation explicite à Nothing. Si cette affectation n'intervient pas, l'objet à portée globale ou publique sera fermé en fin d'exécution (fermeture volontaire de l'application ou crash).

Beaucoup d'encre a coulé au sujet de la fermeture des objets DAO et la réponse officielle de Microsoft est la suivante :

Il est nécessaire de libérer les objets avec Set Objet=Nothing avant la fin d'une méthode, faute de quoi des problèmes d'accroissement anormal de la base de données ont été constatés nécessitant des compactages plus fréquents. À noter que cela vaut pour n'importe quel objet DAO (Recordset, Querydef) et pas exclusivement aux objets Database.

Si vous programmez sous Access, prenez garde à ne pas confondre la méthode Close de l'objet retourné par CurrentDb et la méthode CloseCurrentDatabase de l'objet Application ayant pour effet de fermer le fichier de base de données au sein de l'environnement Access.

Sub Ouverture()

Dim oDBE As DAO.DBEngine
Dim oDb As DAO.Database
Dim strChemin As String

strChemin = "c:\dbclient.accdb"

Set oDBE = New DBEngine
Set oDb = oDBE.OpenDatabase(strChemin, True, False, ";PWD=test;")

'...
' TRAITEMENT
'...

oDb.Close
Set oDb = Nothing
Set oDBE = Nothing
End Sub

Afin de ne pas surcharger davantage les blocs de code de ce document, les fermetures et les libérations d'objets ont été volontairement retirées.

II-D - Maintenance d'une base de données

Les opérations de maintenance de la base de données ont été réduites puisqu'avant il était possible d'y inclure tout ce qui concernait la gestion des utilisateurs, des groupes et des permissions. Nous allons ici aborder le compactage ainsi que la mise en place d'un mot de passe de niveau système.

II-D-1 - Compactage de la base de données

Le compactage d'un fichier accdb ou accde est rendu possible grâce à l'appel de la méthode CompactDatabase de l'objet DBEngine.

Prototype :

.CompactDatabase(SrcName, DstName, DstLocale, Options, password)

- SrcName : chemin source de la base de données ;

- DstName : chemin de destination ;

- DstLocale : ordre de tri, voir le paramètre Locale de CreateDatabase ;

- Options : version du fichier de données, voir le paramètre Options de CreateDatabase ;

- Password : mot de passe de la base de données sous la forme ;PWD=motdepasse;

Notons que pour être compactable, une base de données doit absolument être fermée. Dans le cas contraire, l'erreur 3704 sera levée.

Exemple :

Sub compacter()
Dim strCheminSrc As String
Dim strCheminDest As String

strCheminSrc = "c:\dbtest.accdb"
strCheminDest = "c:\dbtest-comp.accdb"
Application.DBEngine.CompactDatabase strCheminSrc, strCheminDest
End Sub

Si vous souhaitez compacter le fichier sur lui-même, il est nécessaire de procéder en deux étapes :

1. Compacter dans un fichier temporaire ;

2. Renommer le fichier.

L'utilisation des fonctions de création de fichiers temporaires de l'API Windows peut alors s'avérer utile.

Declare Function GetTempFileName Lib "kernel32" Alias "GetTempFileNameA" _
(ByVal lpszPath As String, ByVal lpPrefixString As String, _
ByVal wUnique As Long, ByVal lpTempFileName As String) As Long

Declare Function GetTempPath Lib "kernel32" Alias "GetTempPathA" _
(ByVal nBufferLength As Long, ByVal lpBuffer As String) As Long

Ces déclarations sont à placer au début d'un module.

Sub Compacter2()
Dim strCheminSrc As String
Dim strCheminDest As String
Dim strTempPath As String

strCheminSrc = "c:\dbTest.accdb"

'Récupère un nom de fichier temporaire
strTempPath = String(100, 0)
strCheminDest = String(260, 0)
GetTempPath 100, strTempPath
GetTempFileName strTempPath, "tmp", 0, strCheminDest
strCheminDest = Left$(strCheminDest, InStr(1, strCheminDest, Chr$(0)) - 1)
Kill strCheminDest
'Compacte
Application.DBEngine.CompactDatabase strCheminSrc, strCheminDest
'Déplace
FileCopy strCheminDest, strCheminSrc
End Sub

Exemple de compactage d'une base sécurisée :

Sub compacter3()
Dim strCheminSrc As String
Dim strCheminDest As String

strCheminSrc = "c:\dbClient.accdb"
strCheminDest = "c:\dbClient-comp.accdb"

Application.DBEngine.CompactDatabase strCheminSrc, strCheminDest, , , ";PWD=test;"
End Sub

II-D-2 - Changement du mot de passe

La méthode NewPassword de l'objet DAO.Database permet de changer le mot de passe d'une base de données ouverte en mode exclusif.

Prototype :

.NewPassword(bstrOld, bstrNew)

- bstrOld : ancien mot de passe ;

- bstrNew : nouveau mot de passe.

La taille des mots de passe est fixée à 20 caractères maximum. Tous les caractères ASCII sont utilisables à l'exception du caractère NULL (vbNullChar).

Dans le cas où vous souhaitez créer un mot de passe sur une base de données qui n'en disposait pas, fixez la valeur du paramètre bstrOld à "" (chaîne vide).

Dans le cas où vous souhaitez supprimer un mot de passe, fixez la valeur de bstrNew à "" (chaîne vide).

Exemple :

Sub Passe()

Dim oDb As DAO.Database
Set oDb = CurrentDb

oDb.NewPassword "Ancien", "Nouveau"
End Sub

La méthode NewPassword peut retourner les erreurs suivantes :

- 3621 : impossible de modifier le mot de passe d'une base de données ouverte en mode partagé ;

- 3031 : mot de passe non valide. Soit l'ancien mot de passe est incorrect, soit le nouveau dépasse la longueur maximum autorisée.

III - Structure des données

III-A - Rappels

Avant d'aborder plus en détail les spécificités d'Access 2007 et supérieurs, il est nécessaire de rappeler l'architecture des objets d'une base de données dont le squelette reste quasiment inchangé depuis l'apparition de DAO.

Architecture du modèle DAO

La collection TableDefs regroupe l'ensemble des tables (TableDef). La collection Fields regroupe l'ensemble des champs (Field) d'une table. Le schéma a été volontairement amputé de la partie traitant des index et des relations puisque ces objets n'ont pas subi de modification. Pour de plus amples détails, vous pouvez consulter le document Définition et manipulation des données.

Les propriétés de chaque objet sont accessibles via sa collection Properties selon le modèle suivant :

La collection Properties

La procédure PropDescription de l'exemple ci-dessous lit la propriété Description de la table Commande.

Public Function ExistProperty(ColTProperties As DAO.Properties, _
strTNom As String) As Boolean
On Error GoTo err
Dim oTmpPrp As DAO.Property
Set oTmpPrp = ColTProperties(strTNom)
ExistProperty = True
err:
End Function

Sub PropDescription()
Dim oDb As DAO.Database
Dim oTbl As DAO.TableDef
Set oDb = CurrentDb
Set oTbl = oDb.TableDefs("Commande")
If ExistProperty(oTbl.Properties, "description") Then
MsgBox oTbl.Properties("description").Value
Else
MsgBox "Cette propriété n'existe pas"
End If
oDb.Close
Set oDb = Nothing
Set oTbl = Nothing
End Sub

Certaines propriétés ne sont pas automatiquement définies par Access lors de la création d'un objet. C'est le cas notamment de la propriété DisplayControl d'un champ qui n'est pas ajoutée à la collection Properties tant que la valeur par défaut (Zone de texte) n'a pas été modifiée.

La propriété DisplayControl dans l'interface Access

Il est alors nécessaire de la créer en prenant garde au type attendu par Access : si vous créez la propriété DisplayControl avec le type dbText au lieu de dbInteger, la propriété sera ignorée. Une astuce consiste à créer d'abord la propriété via l'interface graphique et à l'interroger via Debug.Print avant de se lancer dans sa programmation en VBA dans l'application finale.

Sub ajout()
On Error GoTo err
Dim oDb As DAO.Database
Dim oTbl As DAO.TableDef
Dim oFld As DAO.Field
Set oDb = CurrentDb
Set oTbl = oDb.TableDefs("Commande")
Set oFld = oTbl.Fields("Statut")
'Crée et ajoute la nouvelle propriété
oTbl.Properties.Append oTbl.CreateProperty("DisplayControl", dbInteger, acComboBox)
'Rafraîchit la liste des propriétés
oTbl.Properties.Refresh
MsgBox "Propriété ajoutée"
Exit Sub
'Gère les erreurs
err:
Select Case err.Number
Case 3367: MsgBox "La propriété existe déjà"
Case Else: MsgBox "Une erreur inconnue est survenue"
End Select
End Sub

Dans la suite de ce document et pour une meilleure clarté du code, nous utiliserons la fonction EcrirePropriete ci-dessous :

Function EcrirePropriete(oObjet As Object, strNom As String, Optional intType As DataTypeEnum, Optional vValeur) As Integer
On Error GoTo err
oObjet.Properties(strNom) = vValeur
EcrirePropriete = 1

fin:
Exit Function

err:
'Dans le cas où la propriété n'existe pas,
'il faut la créer
If err.Number = 3270 Then
With oObjet
.Properties.Append .CreateProperty(strNom, intType, vValeur)
End With
Else
err.Raise err.Number, , err.Description
End If
EcrirePropriete = 2
Resume fin
End Function

Exemple d'utilisation :

EcrirePropriete oFld, "TextFormat", dbInteger, 1

Avant de passer en revue la liste des nouveautés apparues en termes de structure de base de données depuis Office 2007, terminons les rappels par la création d'une table. Celle-ci doit être réalisée en plusieurs étapes :

1. Création de l'objet TableDef avec la méthode CreateTableDef de l'objet Database ;

2. Création des objets Field avec la méthode CreateField de l'objet TableDef créé à la première étape ;

3. Ajout des objets Field créés à la collection Fields de l'objet TableDef via la méthode Append de la collection ;

4. Éventuellement, création et ajout des index ;

5. Ajout de l'objet TableDef à la collection TableDefs de l'objet Database via la méthode Append de la collection.

Exemple :

Sub CreerTable()
Dim oDb As DAO.Database
Dim oNouvelleTable As DAO.TableDef
Dim oChamp As DAO.Field
Dim oIndex As DAO.Index
'Instancie la base de données
Set oDb = CurrentDb
'Crée la nouvelle table
Set oNouvelleTable = oDb.CreateTableDef("Clients")
'Crée le champ IDClient
Set oChamp = oNouvelleTable.CreateField("IDClient", dbLong)
'Définit le champ en numero_auto
oChamp.Attributes = dbAutoIncrField
'Ajoute le champ à la table
oNouvelleTable.Fields.Append oChamp
'Crée le champ nomClient et l'ajoute
oNouvelleTable.Fields.Append oNouvelleTable.CreateField("NomClient", _
dbText, 15)
'Crée le champ PrenomClient et l'ajoute
oNouvelleTable.Fields.Append oNouvelleTable.CreateField("PrenomClient", _
dbText, 25)
'définit la clé primaire sur l'IDClient
Set oIndex = oNouvelleTable.CreateIndex("PK_IDClient")
oIndex.Primary = True
oIndex.Fields.Append oIndex.CreateField("IdClient")
'Ajoute l'index à la table
oNouvelleTable.Indexes.Append oIndex
'Ajoute la table à la base de données
oDb.TableDefs.Append oNouvelleTable

'Libère les variables
oDb.Close
Set oIndex = Nothing
Set oChamp = Nothing
Set oNouvelleTable = Nothing
Set oDb = Nothing
End Sub

Il est important de noter que certaines propriétés (plus particulièrement les objets Property qui ne sont pas repris dans la liste des propriétés par l'explorateur d'objet) ne peuvent être créées avant que l'objet ne soit ajouté à la collection du même nom. Ainsi, si vous tentez de créer la propriété DisplayControl d'un objet Field qui n'a pas été ajouté à la collection Fields de la table, une erreur n°3219 (Opération non valide) sera levée.

III-B - Le champ mémo

Déjà existant dans les versions précédentes, le champ mémo, qui peut contenir jusqu'à 65535 caractères, s'est enrichi de plusieurs fonctionnalités qui ne sont pas directement liées au moteur de base de données mais qui, toutefois, méritent d'être traitées dans ce document.

La première fonctionnalité concerne la possibilité de stocker du texte enrichi (HTML) qui est restitué avec sa mise en forme spécifique dans les zones de texte des formulaires et des états Access.

Champ mémo en mode création

Comme indiqué par l'image ci-dessus, cette nouveauté d'Access 2007 se traduit par :

- la définition d'un type : Mémo, dbMemo ;

- La définition d'une propriété : Format du texte, TextFormat.

La propriété TextFormat peut prendre deux valeurs :

- 0 : Texte brut ;

- 1 : Texte enrichi.

En VBA, cela se traduit par le code ci-dessous :

Sub CommentaireClient()
Dim oDb As DAO.Database
Dim oTbl As DAO.TableDef
Dim oFld As DAO.Field

'Accès à la base de données courante
Set oDb = CurrentDb
'Accès à la table tblClient
Set oTbl = oDb.TableDefs("tblClient")
'Création du champ
Set oFld = oTbl.CreateField("CommentaireClient", dbMemo)
'Ajout du champ à la table
oTbl.Fields.Append oFld

'Format du texte = Texte Enrichi
EcrirePropriete oFld, "TextFormat", dbInteger, 1

End Sub

la fonction EcrirePropriete est disponible dans la section rappel juste au-dessus.

La seconde fonctionnalité réside dans l'historique des valeurs. Désormais, lorsqu'un champ mémo subit des modifications, les anciennes valeurs restent consultables ultérieurement.

Historique d'un champ mémo

Pour activer la conservation des données modifiées, il est nécessaire de fixer la propriété Ajouter Uniquement à Oui. En VBA, il s'agit de la propriété AppendOnly de l'objet Field de type dbMemo.

Sub CommentaireClient2()
Dim oDb As DAO.Database
Dim oTbl As DAO.TableDef
Dim oFld As DAO.Field

'Accès à la base de données courante
Set oDb = CurrentDb
'Accès à la table tblClient
Set oTbl = oDb.TableDefs("tblClient")
'Accès au champ CommentaireClient
Set oFld = oTbl.Fields("CommentaireClient")

'Historique des modifications à Oui
EcrirePropriete oFld, "AppendOnly", dbBoolean, True

End Sub

Soyez vigilant, si vous basculez la propriété AppendOnly à False alors que des données sont conservées dans l'historique, elles seront irrémédiablement perdues.

III-C - Les champs à valeurs multiples

Seuls les champs de type Texte et Numérique acceptent les valeurs multiples. La capacité d'un tel champ à stocker plusieurs valeurs est donc d'abord conditionnée par son type puis par la valeur de plusieurs propriétés.

Champ multivalué en mode création

Au niveau du type, cela se traduit par l'arrivée de nouvelles constantes dans l'énumération DataTypeEnum.

Le tableau suivant lie les nouvelles constantes au type de données qu'elles représentent :

Type

Champ à valeur unique

Champ à valeurs multiples

Texte

dbText

dbComplexText

Numérique, octet

dbByte

dbComplexByte

Numérique, entier

dbInteger

dbComplexInteger

Numérique, entier long

dbLong

dbComplexLong

Numérique, réel simple

dbSingle

dbComplexSingle

Numérique, réel double

dbDouble

dbComplexDouble

Numérique, décimal

dbDecimal

dbComplexDecimal

Numérique, N° de réplication

dbGUID

dbComplexGUID

En ce qui concerne les propriétés, vous trouverez ci-dessous la liste des éléments modifiables par VBA.

Contrôle de l'affichage, DisplayControl :

Définit le type de contrôle de saisie mis à disposition de l'utilisateur afin de saisir les données dans les tables. Trois valeurs numériques sont disponibles :

- Zone de texte : acTextBox en VBA. Incompatible avec une saisie multiple ;

- Zone de liste : acListBox en VBA ;

- Zone de liste déroulante : acComboBox en VBA.

Origine Source, RowSourceType :

Définit le type de source d'enregistrement du contrôle DisplayControl. Trois valeurs de type alphanumérique sont possibles :

- Table/Requête : Table/Query en VBA ;

- Liste Valeurs : Value List en VBA ;

- Liste Champs : Field List en VBA.

Dans la mesure où il s'agit de valeurs de type texte et où la moindre erreur d'orthographe provoquera l'échec de la définition de la propriété, il est recommandé d'utiliser des constantes déclarées dans un module.

Const ROWSOURCETYPE_TABLE = "Table/Query"
Const ROWSOURCETYPE_VALUES = "Value List"
Const ROWSOURCETYPE_FIELD = "Field List"

Contenu, RowSource :

Définit la source d'enregistrement du contrôle DisplayControl en lien avec la propriété RowSourceType. Il s'agit d'une propriété de type Texte limitée à 255 caractères répondant à :

- si RowSourceType = Table/Query : nom d'une table, d'une requête ou une instruction SQL valide ;

- si RowSourceType = Value List : liste de valeurs séparées par un point-virgule ;

- si RowSourceType = Field List : nom d'une table ou d'une requête.

Colonne liée, BoundColumn :

Définit le numéro de la colonne d'une zone de liste ou d'une zone de liste déroulante qui sera effectivement stockée dans le champ. La première colonne possède l'index 1.

Dans l'exemple suivant, bien que la donnée affichée soit le nom de l'agence, Microsoft Access stocke l'id de celle-ci.

Exemple de colonne liée

Nbre colonnes, ColumnCount :

Définit le nombre de colonnes affichées dans le contrôle DisplayControl. Sa valeur est de type Integer.

En-têtes colonnes, ColumnHeads :

Définit si le nom des colonnes doit être affiché en haut de la zone de liste ou de la zone de liste déroulante. Sa valeur est de type booléen.

- Dans le cas où RowSourceType est égal à Table/Query, le nom des colonnes est celui des noms des champs de la requête ou de la table source. Utilisez le mot clé AS pour les renommer dans la clause SELECT de la requête.

- Dans le cas où RowSourceType est égal à Value List, les premières valeurs qui devaient constituer la première ligne de la zone de liste sont transformées en en-têtes.

Largeurs Colonnes, ColumnWidths :

Définit la largeur des colonnes du contrôle DisplayControl. Il s'agit d'une propriété de type Texte où les largeurs des colonnes exprimées en twips sont séparées par des points-virgules. Cette propriété est à mettre en lien avec Largeur Liste.

Largeur List, ListWidth :

Définit la largeur de la zone de liste du contrôle DisplayControl. Il s'agit d'une propriété de type Texte (dbText) où la largeur est exprimée en twips. Curieusement et contrairement à ColumnWidth, l'unité est précisée. Exemple :

oFld.Properties("ListWidth") = "4000twip"

Lignes affichées, ListRows :

Définit le nombre de lignes affichées dans le contrôle DisplayControl. Si le nombre de lignes total est supérieur à la valeur de ListRows, une barre de défilement verticale est ajoutée à la zone de liste. Cette propriété est de type Entier (dbInteger).

Limiter à Liste, LimitToList :

Définit si la saisie de l'utilisateur doit appartenir exclusivement aux valeurs contenues dans le contrôle DisplayControl. Dans le cas contraire, prenez garde à l'intégrité référentielle : l'utilisateur pourrait saisir une référence de produit qui n'existe pas dans la table produit. Cette propriété est de type Booléen.

Autoriser plusieurs valeurs, AllowMultipleValues :

Définit la capacité du champ à stocker plusieurs valeurs.

- True : le champ accepte la saisie multiple ;

- False : le champ n'accepte pas la saisie multiple.

Autoriser les modifications de la liste, AllowValueListEdits :

Dans le cas où cette propriété est fixée à True, Microsoft Access offre la possibilité à l'utilisateur de modifier la liste des choix disponibles dans la zone de liste. Pour les mêmes raisons que la propriété LimitToList, cette fonctionnalité est généralement désactivée et si des modifications doivent avoir lieu, elles sont réalisées par le biais de formulaires spécifiques.

L'exemple suivant ajoute le champ IdAgencesPreferees à la table tblClient. Il permettra à l'utilisateur de stocker la liste des agences commerciales privilégiées par un client.

Propriété

Valeur

Origine Source

Table/Requête

Contenu

SELECT NomAgence, IdAgence FROM tblAgence;

Colonne liée

2

Nombre de colonnes

2

En-tête de colonne

Non

Largeurs colonnes

7 cm ; 0 cm

Largeur de liste

7 cm

Autoriser plusieurs valeurs

Oui

Sub CreationAgencesPreferees()
Dim oDb As DAO.Database
Dim oTbl As DAO.TableDef
Dim oFld As DAO.Field

'Accès à la base de données courante
Set oDb = CurrentDb
'Accès à la table tblClient
Set oTbl = oDb.TableDefs("tblClient")
'Création du champ
Set oFld = oTbl.CreateField("IdAgencesPreferees", dbComplexInteger)
'Ajout du champ à la table
oTbl.Fields.Append oFld

'Écriture des propriétés
EcrirePropriete oFld, "DisplayControl", dbInteger, acComboBox
EcrirePropriete oFld, "RowSourceType", dbText, "Table/Query"
EcrirePropriete oFld, "RowSource", dbText, "SELECT NomAgence, IdAgence FROM tblAgence;"
EcrirePropriete oFld, "BoundColumn", dbInteger, 2
EcrirePropriete oFld, "ColumnCount", dbInteger, 2
EcrirePropriete oFld, "ColumnHeads", dbBoolean, False
EcrirePropriete oFld, "ColumnWidths", dbText, 7 * 567 &amp; ";0"
EcrirePropriete oFld, "ListWidth", dbText, "3969twip"
EcrirePropriete oFld, "AllowMultipleValues ", dbBoolean, True

End Sub

Cet autre exemple, ajoute un champ ModePaiement à la table tblClient. Un client peut choisir plusieurs modes de paiement lors de la création de son compte parmi : CB, Chèque, Espèces, Traite.

Propriété

Valeur

Nom du champ

ModePaiement

Taille du champ

20

Type

Texte

Contrôle d'affichage

Zone de liste

Origine Source

Liste de valeurs

Contenu

CB ; Chèque ; Espèces ; Traite

Autoriser plusieurs valeurs

Oui

Sub CreationModePaiement()
Dim oDb As DAO.Database
Dim oTbl As DAO.TableDef
Dim oFld As DAO.Field

'Accès à la base de données courante
Set oDb = CurrentDb
'Accès à la table tblClient
Set oTbl = oDb.TableDefs("tblClient")
'Création du champ
Set oFld = oTbl.CreateField("ModePaiement", dbComplexText, 20)
'Ajout du champ à la table
oTbl.Fields.Append oFld

'Écriture des propriétés
EcrirePropriete oFld, "DisplayControl", dbInteger, acListBox
EcrirePropriete oFld, "RowSourceType", dbText, "Value List"
EcrirePropriete oFld, "RowSource", dbText, "CB;Chèque;Espèces;Traite"
EcrirePropriete oFld, "BoundColumn", dbInteger, 1
EcrirePropriete oFld, "AllowMultipleValues", dbBoolean, True

End Sub

III-D - Les champs pièces jointes

La création d'un champ acceptant les pièces jointes répond à une nouvelle constante de DataTypeEnum : dbAttachment.

Sous Access 2007, seule la propriété ValidationRule représentait un minime intérêt puisqu'elle permettait de restreindre le type de fichiers autorisés. Depuis Access 2010, les champs de type pièce jointe ne sont plus paramétrables. Leur création se limite donc à la seule définition de leur type, soit :

Sub CreationPJ()
Dim oDb As DAO.Database
Dim oTbl As DAO.TableDef
Dim oFld As DAO.Field

'Accès à la base de données courante
Set oDb = CurrentDb
'Accès à la table tblClient
Set oTbl = oDb.TableDefs("tblClient")
'Création du champ
Set oFld = oTbl.CreateField("JustificatifsClient", dbAttachment)
'Ajout du champ à la table
oTbl.Fields.Append oFld

End Sub

III-E - Les champs calculés

Le type champ calculé est l'exception qui confirme la règle. En effet, si jusque-là il était primordial d'ajouter le champ à la table avant de modifier ses propriétés, il faut procéder ici à l'inverse. L'explication réside peut-être dans le fait que, l'argument type de la méthode CreateField définit le type de résultat du champ et que c'est en fait la propriété Expression de l'objet Field qui, si elle est remplie et valide, transforme le champ classique en champ calculé lors de l'appel de la méthode Append de la collection Fields.

Pour résumer, sous DAO, un champ calculé n'est rien d'autre qu'un champ simple (texte, numérique, booléen, etc.) qui possède une formule de calcul dans une de ces propriétés. C'est-à-dire, le contraire de ce que peut laisser penser l'interface graphique Access où Calculé semble être un type et Type de résultat semble être une propriété.

Champ calculé en mode création

Le code suivant ajoute un champ DepartementClient à la table tblClient dans le but d'extraire les deux premiers caractères du code postal.

Sub CreationCalc()
Dim oDb As DAO.Database
Dim oTbl As DAO.TableDef
Dim oFld As DAO.Field

'Accès à la base de données courante
Set oDb = CurrentDb
'Accès à la table tblClient
Set oTbl = oDb.TableDefs("tblClient")
'Création du champ
Set oFld = oTbl.CreateField("DepartementClient", dbText)
EcrirePropriete oFld, "Expression", dbText, "Left(CPClient,2)"
'Ajout du champ à la table
oTbl.Fields.Append oFld
End Sub

En VBA, le séparateur des paramètres des fonctions est la virgule (,). Dans l'interface d'Access ou QBE, il s'agit du point-virgule (;).

III-F - L'objet DAO.Field2

Jusque-là, un champ de table correspondait à un objet DAO.Field. En y regardant de plus près et plus particulièrement avec un espion dans la fenêtre de débogage, il est possible de constater que les nouveautés apportées par le moteur ACE correspondent en réalité à des objets DAO.Field2.

Sub VerificationField2()
Dim oDb As DAO.Database
Dim oTbl As DAO.TableDef
Dim oFld As Object

'Accès à la base de données courante
Set oDb = CurrentDb
'Accès à la table tblClient
Set oTbl = oDb.TableDefs("tblClient")
'Accès à un champ
Set oFld = oTbl.Fields("DepartementClient")
Stop
End Sub

Fenêtre d'exécution

L'objet Field2 offre peu de fonctionnalités supplémentaires à son homologue DAO.Field en ce qui concerne la création de la structure de la base de données, hormis l'accès à quelques propriétés via l' autocomplétion. En revanche, certaines méthodes sont incontournables dans le cas de l'accès aux données qui va être détaillé dans les pages suivantes.

IV - Accès aux données

IV-A - Rappels

L'accès aux données d'une ou plusieurs tables est réalisé à partir d'un objet DAO.Recordset communément appelé curseur (francisé de CURSOR, CURrent Set Of Rows) ou jeu d'enregistrements. Il s'agit de la représentation en mémoire du résultat d'une requête de sélection qui peut être n'importe quelle instruction SQL SELECT valide (exceptées les requêtes SELECT INTO qui ne sont pas des requêtes sélection, mais des requêtes de création de table).

D'un point de vue didactique, il faut imaginer le Recordset comme une sélection dans une feuille Excel. Il est possible d'y lire une ou plusieurs lignes, d'en ajouter, d'en supprimer, d'en modifier ainsi que de s'y déplacer ligne par ligne, ou par saut.

La méthode OpenRecordset appliquée à un objet Database, TableDef ou QueryDef (requête) procède à la mise en mémoire des données issues d'une instruction SQL valide ou plus simplement d'un nom de table ou d'une requête. Il existe plusieurs types de Recordset, chacun offrant un mode d'accès spécifique aux données. Afin de ne pas surcharger ce document, vous pourrez retrouver tous les détails de l'ouverture d'un Recordset à l'adresse suivante : Les différents types de Recordset.

Les méthodes MoveFirst, MoveNext, MovePrevious et MoveLast permettent de se déplacer respectivement au premier enregistrement, au suivant, au précédent et au dernier. La méthode Move X, quant à elle autorise des sauts en avant ou en arrière (valeur X négative) depuis l'enregistrement courant. Combinées aux propriétés EOF et BOF qui identifient le début et la fin du curseur, ces instructions rendent ainsi possibles tous les déplacements dans le Recordset (à l'unique condition que ces déplacements soient autorisés par le type de Recordset : un Recordset ouvert en mode En avant seulement n'autorise pas les déplacements arrières).

La collection Fields de l'objet Recordset regroupe l'ensemble des colonnes de la ligne courante. Ainsi, .Fields("IdClient").Value retourne l'identifiant (IdClient) du client courant.

Les méthodes AddNew et Edit permettent de passer le curseur en mode écriture. Modifiez les valeurs des champs concernés et validez la mise à jour en appelant la méthode Update ou annulez avec CancelUpdate. Enfin, la méthode Delete supprime la ligne actuelle.

Étapes de mise à jour d'un Recordset

L'objet Recordset offre des possibilités sans limites grâce à des fonctionnalités de copie, de filtre, de recherche, etc. Toutes celles-ci sont consultables dans le premier tutoriel : Définition et manipulation de données avec DAO.

Exemples d'utilisation de Recordset :

Lecture :

Sub r01()
Dim oRst As DAO.Recordset
Dim oDb As DAO.Database
Set oDb = CurrentDb
Set oRst = oDb.OpenRecordset("SELECT idClient,NomClient FROM tblClient WHERE idClient=2", dbOpenDynaset)
MsgBox "Le nom du client est : " &amp; Rst.Fields("idClient").Value

'Libération des objets
oRst.Close
oDb.Close
Set oRst = Nothing
Set oDb = Nothing
End Sub

Modification :

Sub r02()
Dim oRst As DAO.Recordset
Dim oDb As DAO.Database
Set oDb = CurrentDb
Set oRst = oDb.OpenRecordset("tblClient", dbOpenTable)
While Not oRst.EOF
'Passe en mode modification
oRst.Edit
'Affecte la date actuelle
oRst.Fields("DateEntreeClient").Value = Date
'Met à jour
oRst.Update
'Passe au suivant
oRst.MoveNext
Wend

'Libération des objets
oRst.Close
oDb.Close
Set oRst = Nothing
Set oDb = Nothing
End Sub

Ajout :

Sub r03()
Dim oRst As DAO.Recordset
Dim oDb As DAO.Database
Set oDb = CurrentDb
Set oRst = oDb.OpenRecordset("tblClient", dbOpenTable)
'Passe en mode Ajout
oRst.AddNew
'Affecte les différents champs
oRst.Fields("NomClient").Value = "MARTIN"
oRst.Fields("PrenomClient").Value = "Maxence"
'Met à Jour
oRst.Update

'Libération des objets
oRst.Close
oDb.Close
Set oRst = Nothing
Set oDb = Nothing
End Sub

IV-B - L'objet QueryDef

Inutile de chercher des nouveautés du côté de l'objet QueryDef dans les dernières versions d'Access, il n'en existe pas. Cette section est uniquement destinée à un court rappel en ce qui concerne ces objets bien trop souvent ignorés ou mal utilisés. L'objet QueryDef est la représentation sous forme d'objet VBA d'une requête enregistrée dans Access. Bon nombre de développeurs considèrent ces composants comme les outils du néophyte et pensent que le mélange à outrance du SQL dans du code VBA est la preuve irréfutable de leur professionnalisme. Mais qu'ils ont tort ! Tout d'abord, d'un point de vue performance, les requêtes enregistrées de Microsoft Access bénéficient de l'effet RushMore décrit par Microsoft comme l'optimisation de l'utilisation des index par le moteur de base de données. Ensuite d'un point de vue pratique, qui n'a jamais râlé devant : """ & strNom & "*""" et les définitions de chaînes SQL de plus de 10 lignes ou bien encore sur 300 colonnes parce qu'ils ne connaissent pas non plus l'underscore _ ?

Si vous êtes adeptes de la fonction suivante :

Function FonctionExistClient(strNom As String) As Boolean
Dim strSQL As String
Dim oDb As DAO.Database
Dim oRst As DAO.Recordset
strSQL = "SELECT Count(idClient) FROM tblClient WHERE nomClient LIKE """ & strNom & "*"""

Set oDb = CurrentDb
Set oRst = oDb.OpenRecordset(strSQL)

FonctionExistClient = oRst.Fields(0) > 0

oRst.Close
Set oRst = Nothing
End Function

Nous vous invitons donc à procéder autrement. Dans un premier temps, créez la requête rqtClientParNom :

PARAMETERS pNom Text;
SELECT *
FROM tblClient
WHERE NomClient Like pNom & "*"

Puis modifiez la fonction VBA définie plus haut pour obtenir les lignes qui suivent :

Function FonctionExistClient(strNom As String) As Boolean
Dim strSQL As String
Dim oDb As DAO.Database
Dim oQdf As DAO.QueryDef
Dim oRst As DAO.Recordset

Set oDb = CurrentDb
Set oQdf = oDb.QueryDefs("rqtClientParNom")
With oQdf
'Fixe la valeur du paramètre pNom dans la requête
.Parameters("pNom") = strNom
'Ouvre le curseur
Set oRst = .OpenRecordset
FonctionExistClient = Not oRst.EOF
oRst.Close
End With

Set oRst = Nothing
Set oQdf = Nothing
End Function

Certes, cette pratique engendre des lignes de code supplémentaires. Toutefois, dans une optique de factorisation du code, l'objet QueryDef pourra être utilisé ailleurs dans l'application et surtout, si dans X opérations de maintenance, certains champs venaient à être modifiés, il suffira de modifier les requêtes enregistrées sans avoir à fouiner dans les milliers de lignes de code VBA devenues illisibles.

IV-C - Les champs mémo

D'un point de vue contenu de la base de données, les nouveautés apportées au champ de type mémo, que cela soit l'enrichissement du texte ou la mise en place d'un historique des modifications, n'ont engendré aucune modification au niveau des Recordset. Cela ne signifie cependant pas que les données ne sont pas accessibles via VBA puisque l'objet Access.Application s'est vu doté de méthodes spécifiques.

Ainsi, la méthode Application.PlainText permet de transformer du contenu enrichi (format HTML) en contenu brut.

Prototype :

.PlainText(RichText, Length)

Où :

- RichText(String) : texte à convertir ;

- Length (Integer) : nombre de caractères à retourner. Ne spécifiez pas ce paramètre si vous souhaitez retourner toute la chaîne, indiquez une valeur numérique N afin de tronquer la chaîne à N caractères.

Exemple :

...
Set oQdf = oDb.QueryDefs("rqtClientParNom")
With oQdf
.Parameters("pNom") = "Dupont"
Set oRst = .OpenRecordset

Debug.Print oRst.Fields("CommentaireClient").Value
Debug.Print Application.PlainText(oRst.Fields("CommentaireClient").Value)
End With
...

Fenêtre d'exécution :

<div>Un client sérieux mais <u>difficilement joignable</u>.</div>
Un client sérieux mais difficilement joignable.

Pour l'écriture, il suffit d'envoyer le code HTML dans la propriété Values du champ. Exemple :

Set oQdf = oDb.QueryDefs("rqtClientParNom")
With oQdf
.Parameters("pNom") = "Dupont"
Set oRst = .OpenRecordset

With oRst
If Not .EOF Then
.Edit
.Fields("CommentaireClient").Value = "<div><b>Franco de port pour commande >200€</b></div>"
.Update
End If
End With
End With

La méthode Application.HTMLEncode permet de convertir les signes spéciaux en code HTML valide afin qu'ils ne soient pas interprétés comme des balises de texte enrichi. Ainsi, Debug.Print Application.HtmlEncode("<200") affiche &lt;200 dans la fenêtre d'exécution.

En ce qui concerne l'historique des modifications apportées à un champ mémo, la consultation via VBA est peu convaincante. Si la méthode Application.ColumnHistory, à utiliser comme une fonction de domaine telle que DFirst ou DLookUp, a le mérite d'exister, les données qu'elle retourne sont, pour le moins, indigestes. Jugez plutôt :

Debug.Print Application.ColumnHistory("tblClient", "CommentaireClient", "IdClient=7")

Fenêtre d'exécution :

[Version : 27/12/2013 11:13:25 ] Un client sérieux mais difficilement joignable. Par portable uniquement
[Version : 27/12/2013 11:13:30 ] Un client sérieux mais difficilement joignable.
[Version : 27/12/2013 11:14:11 ] Un client sérieux mais difficilement joignable.
[Version : 27/12/2013 11:14:31 ] Un client sérieux mais difficilement joignable.
[Version : 28/12/2013 21:19:16 ] Franco de port pour commande >200€
[Version : 28/12/2013 21:20:43 ] Franco de port pour commande <200€
[Version : 28/12/2013 21:31:39 ] Franco de port pour commande >200€

Prototype de la fonction :

Application.ColumnHistory(TableName, ColumnName, queryString)

Où :

- TableName : nom de la table où se trouve le champ mémo ;

- ColumnName : nom du champ mémo ;

- queryString : expression représentant un critère permettant d'identifier la ligne concernée dans TableName.

Ainsi, dans l'exemple plus haut, la fonction ColumnHistory retourne l'historique du champ CommentaireClient de la table tblClient pour l'enregistrement possédant l'idClient n°7. Le résultat, bien loin de ce que l'on pourrait s'attendre d'un SGBD, a tout de même l'avantage d'être formaté :[Version : <date heure> ] <Texte> Saut de ligneSi vous devez travailler sur les données historisées, attendez-vous donc à devoir parser cette chaîne de caractères afin d'en obtenir une présentation tabulaire, soit sous forme de table soit sous forme de collection ou de tableau VBA. Cela aurait été pourtant si agréable de traiter tout cela directement sous forme de recordset !

IV-D - Les champs multivalués

L'accès aux valeurs multiples d'un même champ est comparable à une table dans la table :

Valeurs d'un champ multivalué - sous-table

La valeur du champ IdAgencePreferees est une table avec un seul champ Value contenant les identifiants des agences sélectionnées. La lecture des IdAgencesPreferees peut être réalisée de deux manières :

Par une sous-table comme ci-dessus à l'aide de la requête suivante :

SELECT
idClient,
NomClient,
PrenomClient,
IdAgencesPreferees
FROM
tblClient

Valeurs d'un champ multivalué

Ou ligne par ligne avec la requête ci-dessous :

SELECT
idClient,
NomClient,
PrenomClient,
IdAgencesPreferees.Value
FROM
tblClient

Plusieurs valeurs traduites en lignes

Nous nous intéresserons essentiellement à la première méthode. Soit le code d'ouverture d'un Recordset basé sur la première requête nommée rqtAgenceParClient :

Dim oDb As DAO.Database
Dim oRst As DAO.Recordset

Set oDb = CurrentDb
Set oRst = oDb.OpenRecordset("rqtAgenceParClient")

Dans le tableau ci-dessous figurent les champs de l'objet oRst espionnés dans l'éditeur VBA.

Id

Nom

Type

0

IdClient

Integer

1

NomClient

String

2

PrenomClient

String

3

IdAgencesPreferees

Recordset2

Les valeurs multiples d'un champ sont donc représentées en mémoire par un Recordset directement accessible depuis le champ concerné. Il est alors possible d'écrire :

Dim oDb As DAO.Database
Dim oRstClient As DAO.Recordset
Dim oRstAgence As DAO.Recordset

Set oDb = CurrentDb
Set oRstClient = oDb.OpenRecordset("rqtAgenceParClient")
Set oRstAgence = oRstClient.Fields("IdAgencesPreferees").Value
With oRstAgence
While Not .EOF
Debug.Print .Fields("Value").Value
.MoveNext
Wend
End With

Dans le prototype .Fields("Value").Value, .Fields("Value") correspond au champ nommé Value du Recordset et .Value correspond à la valeur de ce champ.

Toutes les opérations applicables aux Recordsets de type Dynaset sont utilisables sur les Recordsets issus des champs acceptant des valeurs multiples. En ce qui concerne les ordres de mise à jour (ajout, modification, suppression), le Recordset principal doit être en mode mise à jour (AddNew ou Update). En effet, l'ajout d'une nouvelle agence pour le client Dupont consiste avant tout en une modification de la fiche client de M. Dupont.

Vous trouverez dans les lignes suivantes quelques exemples de programmation autour du champ IdAgencesPreferees.

Ajout d'une nouvelle agence

Dim oDb As DAO.Database
Dim oRstClient As DAO.Recordset
Dim oRstAgence As DAO.Recordset

Set oDb = CurrentDb
Set oRstClient = oDb.OpenRecordset("rqtAgenceParClient")
With oRstClient
Set oRstAgence = .Fields("IdAgencesPreferees").Value
.Edit
With oRstAgence
'Ajoute l'agence n°3
.AddNew
.Fields("Value").Value = 3
.Update
End With
.Update
End With

Recherche d'une agence

Dim oDb As DAO.Database
Dim oRstClient As DAO.Recordset
Dim oRstAgence As DAO.Recordset

Set oDb = CurrentDb
Set oRstClient = oDb.OpenRecordset("rqtAgenceParClient")
Set oRstAgence = oRstClient.Fields("IdAgencesPreferees").Value
With oRstAgence
'Recherche l'agence n°3
.FindFirst "Value = 3"
If .NoMatch Then
MsgBox "Non trouvé"
Else
MsgBox "Trouvé"
End If
End With

Suppression de toutes les agences

Dim oDb As DAO.Database
Dim oRstClient As DAO.Recordset
Dim oRstAgence As DAO.Recordset

Set oDb = CurrentDb
Set oRstClient = oDb.OpenRecordset("rqtAgenceParClient")
With oRstClient
Set oRstAgence = .Fields("IdAgencesPreferees").Value
.Edit
With oRstAgence
While Not .EOF
.Delete
.MoveNext
Wend
End With
.Update
End With

Interrogation du nombre d'agences

Dim oDb As DAO.Database
Dim oRstClient As DAO.Recordset

Set oDb = CurrentDb
Set oRstClient = oDb.OpenRecordset("rqtAgenceParClient")
With oRstClient.Fields("IdAgencesPreferees").Value
If Not .EOF Then .MoveLast
Debug.Print "Nb agences : " & .RecordCount
End With

L'ajout et la modification d'une valeur au sein d'une liste multiple peut nécessiter la mise en place d'une gestion d'erreur dans le cas où le nouvel élément n'appartient pas à la table liée.

Intégrité référentielle

IV-E - Les champs pièce jointe

Les pièces jointes

La fenêtre de relations illustre relativement bien les enjeux des champs de type pièce jointe.

Structure d'un champ pièce jointe

Le champ pièce jointe est un champ multivalué (DAO.Field2) où la « table fille » dispose de 3 champs :

- FileData : données binaires du fichier ;

- FileName : nom du fichier ;

- FileType : type/extension du fichier.

Les champs FileName et FileType sont accessibles comme n'importe quel autre champ de Recordset.

Dim oDb As DAO.Database
Dim oQdf As DAO.QueryDef
Dim oRstClient As DAO.Recordset
Dim oRstPJ As DAO.Recordset2

Set oDb = CurrentDb

Set oQdf = oDb.QueryDefs("rqtClientParId")
With oQdf
.Parameters("pId").Value = "7"
Set oRstClient = .OpenRecordset
If Not oRstClient.EOF Then
Set oRstPJ = oRstClient.Fields("JustificatifsClient").Value
With oRstPJ
While Not .EOF
Debug.Print "Fichier " & .Fields("FileType").Value & "->" & .Fields("FileName").Value
.MoveNext
Wend
End With
End If
End With

L'exemple précédent affiche la liste des justificatifs (extensions et noms de fichiers) du client portant l'ID n°7 grâce au parcours du recordset oRstPJ contenu dans le champ JustificatifsClient de oRstClient.

Pour manipuler les données binaires, l'objet Field2 dispose de deux méthodes LoadFromFile et SaveToFile qui permettent respectivement d'envoyer un fichier dans la table et d'enregistrer des données sur le disque dur (ou un autre support). Ces méthodes n'étant pas disponibles dans l'objet Field, soyez vigilant dans vos déclarations, faute de quoi vous risquez de rencontrer des erreurs de compilation et vous ne pourrez pas bénéficier de l'autocomplétion du code VBA.

IV-E-1 - Lecture des données (enregistrement sur le disque)

Prototype :

Field2.SaveToFile(FileName)

L'argument FileName peut être :

- le chemin d'accès complet du fichier sous la forme X:\Dossier\NomFichier.Extension ;

- le chemin d'accès à un dossier sous la forme X:\Dossier. Dans ce cas, le fichier portera le nom et l'extension comme défini dans la base de données par les valeurs des champs FileName et FileType.

L'exemple suivant enregistre tous les documents du client n°7 dans le dossier de l'application (CurrentProject.Path).

Dim oDb As DAO.Database
Dim oQdf As DAO.QueryDef
Dim oRstClient As DAO.Recordset
Dim oRstPJ As DAO.Recordset2

Set oDb = CurrentDb

Set oQdf = oDb.QueryDefs("rqtClientParId")
With oQdf
.Parameters("pId").Value = "7"
Set oRstClient = .OpenRecordset
If Not oRstClient.EOF Then
Set oRstPJ = oRstClient.Fields("JustificatifsClient").Value
With oRstPJ
While Not .EOF
.Fields("FileData").SaveToFile CurrentProject.Path
.MoveNext
Wend
End With
End If
End With

La méthode SaveToFile peut lever plusieurs erreurs dont notamment l'erreur 3839 : le fichier existe déjà. La méthode SaveToFile n'écrase pas les fichiers existants. Procéder à la résolution des éventuels conflits en amont de l'appel.

IV-E-2 - Écriture des données (envoi du fichier dans la table)

Prototype :

Field2.LoadFromFile(FileName)

La méthode LoadFromFile apportant une modification au champ FileData et donc par ricochet au champ pièce jointe parent, il est impératif de placer les recordset « père » et « fils » en mode mise à jour (soit par l'appel d'AddNew, soit par l'appel d'Edit).

L'exemple suivant ajoute le document c:\scanner\temp312.pdf au client n°7 et le renomme en Rib 2014.

Dim oDb As DAO.Database
Dim oQdf As DAO.QueryDef
Dim oRstClient As DAO.Recordset
Dim oRstPJ As DAO.Recordset2

Set oDb = CurrentDb

Set oQdf = oDb.QueryDefs("rqtClientParId")
With oQdf
.Parameters("pId").Value = "7"
Set oRstClient = .OpenRecordset
oRstClient.MoveFirst
If Not oRstClient.EOF Then
'Passe le recordset oRstClient en mode mise à jour
oRstClient.Edit
Set oRstPJ = oRstClient.Fields("JustificatifsClient").Value
With oRstPJ
'Ajoute un nouveau fichier
.AddNew
.Fields("filedata").LoadFromFile "c:\scanner\temp312.pdf"
.Fields("filename").Value = "Rib 2014.pdf"
.Update
End With
'Confirme la mise à jour de la fiche client
oRstClient.Update
End If
End With

Pièce jointe ajoutée

le champ FileType n'est pas modifiable. Son contenu est défini par le moteur de base de données à partir de la valeur de FileName. Il est donc très important de préciser l'extension dans le nom du fichier sans quoi Access sera incapable de choisir le programme à utiliser pour son ouverture.

La méthode LoadFromFile accepte aussi les URL afin de récupérer des fichiers directement depuis Internet.
Exemple : .Fields("filedata").LoadFromFile http://www.monsite.com/template/images/logo.png

Les lignes suivantes, suppriment l'ancien Rib (nommé Rib.pdf) du client n°7 :

Dim oDb As DAO.Database
Dim oQdf As DAO.QueryDef
Dim oRstClient As DAO.Recordset
Dim oRstPJ As DAO.Recordset2

Set oDb = CurrentDb

Set oQdf = oDb.QueryDefs("rqtClientParId")
With oQdf
.Parameters("pId").Value = "7"
Set oRstClient = .OpenRecordset
oRstClient.MoveFirst
If Not oRstClient.EOF Then
'Passe le recordset oRstClient en mode mise à jour
oRstClient.Edit
Set oRstPJ = oRstClient.Fields("JustificatifsClient").Value
With oRstPJ
'Recherche le fichier Rib.pdf
.FindFirst "FileName=" & Chr(34) & "Rib.pdf" & Chr(34)
'Si trouvé, le supprimer
If Not .NoMatch Then .Delete
End With
'Confirme la mise à jour de la fiche client
oRstClient.Update
End If
End With

Pour supprimer tous les fichiers, d'une même fiche client, cet autre exemple utilise une requête SQL de suppression nommée rqtSupprimerPJClient.

Code SQL de la requête :

PARAMETERS pId Long;
DELETE JustificatifsClient.FileName
FROM tblClient
WHERE IdClient=pId;

Code VBA associé :

Dim oDb As DAO.Database
Dim oQdf As DAO.QueryDef

Set oDb = CurrentDb

Set oQdf = oDb.QueryDefs("rqtSupprimerPJClient")
With oQdf
.Parameters("pId").Value = "7"
.Execute dbFailOnError
Debug.Print "Suppression de " & .RecordsAffected & " fichier(s)"
End With

IV-F - Les champs calculés

Les champs calculés sont, par définition, non modifiables. Si vous essayez de le faire avec DAO en VBA, vous obtiendrez une erreur 3164 : le champ ne peut pas être mis à jour. Vous vous demandez alors quel est l'intérêt de traiter de tels champs dans ce document… En réalité, ce n'est pas tant à la valeur des champs calculés que nous souhaitons nous intéresser, mais plutôt au moment où elle est calculée. Autrement dit : quand la valeur d'un champ calculé est-elle disponible ? Comme pour un champ de type NuméroAuto, la valeur calculée est disponible avant l'appel d'Update, dès que les champs utilisés dans l'expression ont été changés. Prenez garde toutefois aux étapes intermédiaires dans le cas où la formule de calcul est basée sur plusieurs champs à modifier. Pour illustrer ces propos, prenons un champ calculé [TotalLigne] dont la valeur est égale à [PrixUnitaire] * [Quantite] et le code suivant :

With oRst
.Edit
.Fields("PrixUnitaire").Value = 2.5
.Fields("Quantite").Value = 3
.Update
End With

Le tableau suivant espionne les différents champs du recordset oRst :

Ligne VBA

PrixUnitaire

Quantite

TotalLigne

.Edit

2

4

8

.Fields("PrixUnitaire").Value = 2.5

2.5

4

10

.Fields("Quantite").Value = 3

2.5

3

7.5

V - Conclusion

Comme vous avez pu le constater, le « successeur » de Jet a su amener avec lui son lot de nouveautés dans le modèle DAO. Il n'aurait d'ailleurs pas pu en être autrement tant Microsoft Access et DAO sont étroitement liés. Cependant, avec maintenant plusieurs années de recul sur les nouveautés, force est de constater qu'elles sont peu utilisées par le développeur aguerri. Je pense particulièrement aux champs à valeurs multiples en contradiction avec les méthodes de modélisation enseignées. Rares sont ceux dont les besoins sont tels que les nouvelles fonctionnalités doivent être manipulées en VBA et c'est sans aucun doute pour cette raison que ce document n'a été rédigé que si tardivement. Cela doit être aussi pour ces raisons que les ouvrages payants ignorent tout ou partie de ces façons de faire. Pourtant, le développeur devra en passer par là s'il souhaite retranscrire, par exemple, la gestion des pièces jointes dans sa propre interface ou bien encore créer des formulaires déconnectés permettant la saisie de valeurs multiples.

Remerciements : omen999 pour sa relecture technique et phanloga pour ses corrections orthographiques.

VI - Lire aussi

Définition et manipulation de données avec DAO
Comportement des événements de table dans une transaction
Comportement des événements de table en environnement multiutilisateurs
Le recordset ADO
Forum Access - VBA. Posez vos questions
La FAQ Access. Toutes les questions/réponses
Les sources Access : des exemples de codes et d'applications à télécharger