IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)

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

Image non disponible

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

Commentez Donner une note à l´article (5) 

Article lu   fois.

L'auteur

Profil ProSite personnel

Liens sociaux

Viadeo Twitter Facebook Share on Google+   

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
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 :

 
Sélectionnez
Application.DBEngine

Ou, plus rapidement par :

 
Sélectionnez
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 :

 
Sélectionnez
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 :

 
Sélectionnez
.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 :

 
Sélectionnez
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 :

 
Sélectionnez
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.

 
Sélectionnez
Sub Ouverture()
            
            
Dim oDb As DAO.Database
            
Set oDb = Application.CurrentDb
End Sub

Ou :

 
Sélectionnez
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 :

 
Sélectionnez
.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 :

 
Sélectionnez
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.

 
Sélectionnez
Sub Creation()
            
            
Dim oDb As DAO.Database
Dim strChemin As String
            
strChemin = "c:\dbTestS.accdb"
Set oDb = DBEngine.CreateDatabase(strChemin, dbLangGeneral & ";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.

 
Sélectionnez
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 :

 
Sélectionnez
.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 :

 
Sélectionnez
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.

 
Sélectionnez
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.

 
Sélectionnez
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 :

 
Sélectionnez
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 :

 
Sélectionnez
.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 :

 
Sélectionnez
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
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éesDé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 collection Properties

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

 
Sélectionnez
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
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.

 
Sélectionnez
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 :

 
Sélectionnez
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 :

 
Sélectionnez
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 :

 
Sélectionnez
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
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 :

 
Sélectionnez
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
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.

 
Sélectionnez
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
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.

 
Sélectionnez
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
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 :

 
Sélectionnez
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
 
Sélectionnez
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 & ";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
 
Sélectionnez
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 :

 
Sélectionnez
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
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.

 
Sélectionnez
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.

 
Sélectionnez
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
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 RecordsetLes 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.

Etapes de mise à jour d'un Recordset
É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 DAODéfinition et manipulation de données avec DAO.

Exemples d'utilisation de Recordset :

Lecture :

 
Sélectionnez
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 : " & Rst.Fields("idClient").Value
            
'Libération des objets
oRst.Close
oDb.Close
Set oRst = Nothing
Set oDb = Nothing
End Sub

Modification :

 
Sélectionnez
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 :

 
Sélectionnez
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 :

 
Sélectionnez
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 :

 
Sélectionnez
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 :

 
Sélectionnez
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 :

 
Sélectionnez
.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 :

 
Sélectionnez
...
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 :

 
Sélectionnez
<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 :

 
Sélectionnez
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 :

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

Fenêtre d'exécution :

 
Sélectionnez
[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 :

 
Sélectionnez
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
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 :

 
Sélectionnez
SELECT 
 idClient, 
 NomClient,
 PrenomClient, 
 IdAgencesPreferees 
FROM 
 tblClient
Valeurs d'un champ multivalué
Valeurs d'un champ multivalué

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

 
Sélectionnez
SELECT 
 idClient, 
 NomClient,
 PrenomClient, 
 IdAgencesPreferees.Value 
FROM 
 tblClient
Plusieurs valeurs traduites en lignes
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 :

 
Sélectionnez
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 :

 
Sélectionnez
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

 
Sélectionnez
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

 
Sélectionnez
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

 
Sélectionnez
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

 
Sélectionnez
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
Intégrité référentielle

IV-E. Les champs pièce jointe

Les pièces-jointes
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
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.

 
Sélectionnez
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 :

 
Sélectionnez
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).

 
Sélectionnez
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 :

 
Sélectionnez
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.

 
Sélectionnez
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
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 :

 
Sélectionnez
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 :

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

Code VBA associé :

 
Sélectionnez
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 :

 
Sélectionnez
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 : omen999omen999 pour sa relecture technique et phanlogaphanloga pour ses corrections orthographiques.

VI. Lire aussi

Vous avez aimé ce tutoriel ? Alors partagez-le en cliquant sur les boutons suivants : Viadeo Twitter Facebook Share on Google+   

Copyright © 2014 Christophe WARIN. Aucune reproduction, même partielle, ne peut être faite de ce site ni 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. Droits de diffusion permanents accordés à Developpez LLC.