Définition et manipulation de données avec DAO

Lecture seule.

Lorsque cette propriété est égale à True, cela signifie que la propriété ne fait pas partie de l'objet même mais qu'elle est héritée d'un autre objet.
Imaginons un objet QueryDef Q1 auquel nous ajoutons une propriété personalisée P1. P1 a été créée pour Q1 donc, la propriété P1.Inherited est égale à False. Si par la suite nous ouvrons un recordset R1 sur Q1, la propriété P1 se retrouvera dans la collection Properties de R1. P1 sera alors héritée et P1.Inherited sera égale à True.

Dans ce cas, ces deux expressions seront vérifiées :

Q1.Properties("P1").Inherited=False

Et :

R1.Properties("P1").Inherited=True

Cette propriété correspond au nom de la propriété. Celui-ci doit être unique au sein de la collection Properties de l'objet concerné, sans quoi une erreur sera levée lorsque vous tenterez d'ajouter le nouvel objet Property à la collection Properties.

Utilisez des noms précis et en relation avec ce que la propriété modélise. De même éviter d'utiliser des mots réservés par Visual Basic. Ainsi, si la propriété représente une date de vérification, nommez la DateVerification et non pas simplement Date.

Il s'agit du type de la propriété. Sa valeur doit être une des constantes suivantes :

Cette propriété correspond à la valeur de l'objet Property. Cette valeur doit être compatible avec le type spécifié dans la propriété Type.

Type : Long

La propriété Attributes caractérise la table correspondante. Il s'agit d'une (ou une combinaison) des constantes suivantes :

Type : String

Il s'agit de la chaîne de connexion utilisée pour une table liée. Sa valeur est nulle pour une table non liée. Cette chaine de connexion prend en compte plusieurs paramètres comme par exemple le nom de l'utilisateur, son mot de passe, etc ... Vous pouvez la consulter visualisant les propriétés de votre table en mode création.

Cette propriété est en lecture seule pour les objets TableDef présents dans la collection Database.TableDefs

Lecture seule.

Type : Date

La propriété DataCreated renvoie la date de création de la table.

Type : Collection

Cette collection regroupe l'ensemble des champs de la table. Vous pouvez les modifier, en ajouter et en supprimer.

Type : Collection

Collection regroupant l'ensemble des index de la table. Vous pouvez les modifier, en ajouter et en supprimer.

Lecture seule.

Type : Date

La propriété LastUpdated retourne la date et l'heure de la dernière modification apportée à la structure de la table concernée.

Type : String

La prorpiété Name correspond au nom de la table. Ce nom doit être unique au sein de la collection TableDefs de l'objet Database.

Vous trouverez plus loin dans ce chapitre (section 4.3.4.2) un exemple montrant comment renommer une table dans une base de données.

Type : Collection

La prorpiété Properties est une collection qui regroupe l'ensemble des objets Property de l'objet TableDef.

Lecture seule.

Type : Long

La propriété RecordCount renvoie le nombre d'enregistrements de la table.

Type : String

Il s'agit du nom d'origine de la table liée. Si l'objet TableDef n'est pas une table liée, cette propriété est en lecture seule et sa valeur est égale à "".

Lecture seule.

Type : Boolean

La propriété de Updatable définit si les données provenant de la table peuvent être modifiées.

Type : String

La propriété ValidationRule définit la règle de validation qui sera utilisée pour valider les données lors d'une mise à jour (Insert compris).

Ainsi, pour obliger l'utilisateur a remplir le champ MonChamp lorsque que le champ Booleen MaCase est coché, nous aurions la règle suivante :

([MonChamp] IS NOT NULL AND [MaCase]) OR NOT ([MaCase])

Type : String

La propriété ValidationText correspond au message qui sera affiché lors d'une mise à jour si les données ne vérifient pas la règle spécifiée dans ValidationRule.

La méthode CreateField crée et retourne un nouvel objet Field qui sera à ajouter par la suite à la collection Database.Tabledefs.

Syntaxe :

Set field = TableDef.CreateField (name, type, size)

Cette méthode sera traitée plus en détail dans la sous partie dédiée aux objets Field.

La méthode CreateIndex crée un nouvel Index dans la table. Cet Index devra ensuite être ajouté à la collection Indexes de l'objet TableDef pour prendre effet.

Syntaxe :

Set index = tabledef.CreateIndex (name)

La méthode CreateProperty crée et retourne un nouvel objet Property qu'il vous faudra ensuite ajouter à la collection Properties de l'objet TableDef.

La syntaxe de la méthode est celle illustrée dans la partie : Les propriétés spécifiques.

La méthode OpenRecordset ouvre un recordset contenant l'ensemble des enregistrements de la table.

Syntaxe :

Set recordset = object.OpenRecordset (type, options, lockedits)

La méthode RefreshLink n'est utilisable que sur des tables liées. Elle permet, aprés avoir modifié la propriété Connect, de rafraîchir les informations de connexion entre la table attachée et la source de données.

Syntaxe :

tabledef.RefreshLink

Je vous propose quelques codes utiles pour lister l'ensemble des tables d'une base de données. Nous ferons la distinction entre les tables systèmes et les tables liées. En effet, la propriété Attributes est un masque binaire. L'opérateur AND entre une constante recherchée et la valeur de la propriété effectue une opération binaire renvoyant un nombre égal à 0 si la constante n'est pas utilisée dans la propriété. Ainsi, pour savoir si une table est une table système, il est possible d'utiliser :

If monObjet.Attributes AND dbSystemObjet then .....

Pour lister, l'ensemble des tables systèmes, il suffit donc de parcourir un à un les éléments de la collection TableDefs et de de vérifier si l'expression booléene est vraie ou non.

Sub Lister()
Dim oDb As DAO.Database
Dim oTbl As DAO.TableDef
Set oDb = CurrentDb
'Pour chaque table
For Each oTbl In oDb.TableDefs
 'Si la table est système alors afficher son nom
 If oTbl.Attributes And dbSystemObject Then MsgBox oTbl.Name
Next oTbl
End Sub

Si nous souhaitons lister les tables liées, nous aurons :

Sub Lister()
Dim oDb As DAO.Database
Dim oTbl As DAO.TableDef
Set oDb = CurrentDb
'Pour chaque table
For Each oTbl In oDb.TableDefs
 'Si la table est liée alors afficher son nom
 If oTbl.Attributes And dbAttachedTable Then MsgBox oTbl.Name
Next oTbl
End Sub

Pour réunir les deux listes, il suffit d'utiliser l'opérateur logique OR (Ou). Ainsi, le code suivant affichera les tables systèmes ainsi que les tables liées.

Sub Lister()
Dim oDb As DAO.Database
Dim oTbl As DAO.TableDef
Set oDb = CurrentDb
'Pour chaque table
For Each oTbl In oDb.TableDefs
 'Si la table est liée ou système alors afficher son nom
 If oTbl.Attributes And (dbAttachedTable Or dbSystemObject) Then MsgBox oTbl.Name
Next oTbl
End Sub

Ce code n'est pas à confondre avec :

Sub Lister()
Dim oDb As DAO.Database
Dim oTbl As DAO.TableDef
Set oDb = CurrentDb
'Pour chaque table
For Each oTbl In oDb.TableDefs
 'Si la table est liée et système alors afficher son nom
 If oTbl.Attributes And (dbAttachedTable And dbSystemObject) Then MsgBox oTbl.Name
Next oTbl
End Sub

En effet ce dernier liste les tables qui sont à la fois systèmes et liées.

Renommer une table est une opération extrémement simple. Toutefois, il ne faut pas oublier que le nom de la table doit être unique. Aussi, il faudra implémenter une gestion d'erreur lors d'une telle opération.

Les objets Relations dépendants de la table renommées sont automatiquement mis à jour par le moteur Jet.

Private Function RenommerTable(oBaseDeDonnees As DAO.Database, strAncienNom As String, _
 strNouveauNom As String) As Boolean
 On Error GoTo err
 Dim oTbl As DAO.TableDef
 'Cherche la table à modifier
 Set oTbl = oBaseDeDonnees.TableDefs(strAncienNom)
 'Renomme la table
 oTbl.Name = strNouveauNom
 'Rafraîchit la collection
 oBaseDeDonnees.TableDefs.Refresh
 'Renvoie true
 RenommerTable = True
Exit Function
err:
Select Case err.Number
  Case 3265: MsgBox "Impossible de trouver la table à renommer"
  Case 3010: MsgBox "La table " & strNouveauNom & " existe déjà"
  Case Else: MsgBox "Une erreur inattendue est survenue"
End Select
End Function

Utilisation :

Dim oDb As DAO.Database
Dim oTbl As DAO.TableDef
Set oDb = CurrentDb
If RenommerTable(oDb, "Commande", "Commande_Archive") Then
 MsgBox "Table renommée avec succès"
End if

La suppression d'une table nécessite quelques précautions. Hormis le fait que la table doit exister pour être supprimer, il ne faut pas perdre de vue que la suppression d'une table engendre la suppression des relations qui en dépendent. Aussi dans le cas d'une mauvaise manipulation, vous risquez de supprimer des objets Relations sans vous en rendre compte. Dans ce cas, je préconise d'envoyer un message à l'utilisateur lui demandant confirmation. Il nous faut donc savoir si la table fait partie d'un objet relation ou non. Pour cela, nous utiliserons la fonction ExistRelation suivante :

Private Function ExistRelation(oBaseDeDonnees As DAO.Database, _
  strNomTable As String) As Integer
Dim oRlt As DAO.Relation
'Pour chaque objet relation
For Each oRlt In oBaseDeDonnees.Relations
  'Si la table est présente dans la relation
  If oRlt.Table = strNomTable Or oRlt.ForeignTable = strNomTable Then _
    ExistRelation = ExistRelation + 1
Next oRlt
End Function

Cette fonction renvoie le nombre de relations dépendantes de la table passée en paramètre.

Nous pouvons donc écrire la fonction de suppression de table suivante :

Private Function SupprimerTable(oBaseDeDonnee As DAO.Database, _
 strNomTable As String)
On Error GoTo err
 Dim intNbRlt As Integer
 'Compte le nombre de relations dépendantes
 intNbRlt = ExistRelation(oBaseDeDonnee, strNomTable)
 'Si la table est présente dans au moins une relation
 If NbRlt <> 0 Then
   'si l'utilisateur ne souhaite pas continuer, sortir de la fonction
   If MsgBox("La table " & strNomTable & " est utilisée dans " & _
     intNbRlt & " relation(s)." & vbCrLf & "La suppression de cette table entrainera la" & _
     " suppression de cette(ces) relation(s)." & vbCrLf & vbCrLf & "Voulez vous continuer ?", _
     vbQuestion + vbYesNo, "Suppression de table") = vbNo Then Exit Function
 End If
 'Supprime la table
 oBaseDeDonnee.TableDefs.Delete (strNomTable)
 'Rafraichit la collection
 oBaseDeDonnee.TableDefs.Refresh
 'Renvoie true
 SupprimerTable = True
 Exit Function
err:
'Gére les erreurs
Select Case err.Number
  Case 3265: MsgBox "Impossible de trouver la table à supprimer"
  Case Else: MsgBox "Une erreur inattendue est survenue"
End Select
End Function

Utilisation :

Dim oDb As DAO.Database
Dim oTbl As DAO.TableDef
Set oDb = CurrentDb
If SupprimerTable(oDb, "Commande") Then
 MsgBox "Table supprimée"
End If

La création d'une table n'ayant que peu d'intéret sans la création de champs, je vous propose d'aborder avant les parties concernant les champs et les index puis de revenir ensuite sur cet exemple.

Dans cet exemple, nous allons créer une table nommée Client possédant trois champs :

• IDClient Clé primaire. Type : Long, numéro automatique
• NomClient Type : Texte. Longueur : 15
• PrenomClient Type : Texte. Longueur : 25

Voici le code correspondant :

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

Type : Boolean

La propriété AllowZeroLength indique si un champ texte accepte les chaines vides. Si la valeur de cette propriété est égale à True, ces valeurs sont acceptées. Dans le cas échéant, une erreur sera levée lorsque que vous tenterez de mettre à jour les données.

Type : Long

La propriété Attributes définit les options du champ. Il s'agit d'une une (ou une combinaison) des constantes suivantes :

Type : Long

Il s'agit de la méthode utilisée par le moteur pour comparer les valeurs du champ si celui-ci est de type texte.

Les valeurs possibles sont :

Lecture seule.

Type : Boolean

Lorsque la propriété DataUpdatable est égale à False, il vous est impossible de modifier les valeurs de ce champ. C'est notamment le cas d'un champ auto-incrémenté.

Type : Variant

Cette propriété correspond à la valeur par défaut du champ. Elle est en lecture/écriture pour les champs issus de TableDef et en lecture seule pour ceux issus de QueryDef et Recordset. Elle n'est pas prise en charge par les objets Fields des collections Database.Indexes et Database.Relations.

Pour un champ de type Long vous pouvez attribuer à cette propriété la valeur : GenUniqueID(). Ainsi à chaque nouvel enregistrement ajouté, le moteur Jet attribue une valeur unique et aléatoire au nouvel enregistrement. Cette valeur reproduit le comportement d'une numérotation automatique aléatoire.

Lecture seule.

Type : Long

La propriété FieldSize, correspond à la taille utilisée dans la base par un champ de type binaire ou mémo.
Cette valeur est exprimée en nombre de caractères pour un champ mémo et en nombre d'octet pour un champ de type binaire.

Type : String

La propriété ForeignName n'est à utiliser que pour les champs d'un objet de type Relation. Elle définit le nom du champ dans la table externe de la relation (clé étrangère). Cette propriété est en lecture/écriture dans le cas d'objets Relation non ajoutés à la collection Database.Relations et en lecture seule pour les objets déjà présents au sein de cette même collection.

Type : String

Il s'agit du nom du champ. Le nom d'un champ doit être unique au sein d'une table.

Type : Integer

La propriété OrdinalPosition permet de définir la position du champ dans la collection Fields de l'objet TableDef. Le premier élément possède la position 0. Cette position conditionne l'ordre d'apparition des champs dans une requête SELECT * FROM ... Notons que si deux champs possèdent la même valeur pour la propriété OrdinalPosition, ces champs sont alors classés par ordre alphabétique.
Cette propriété est en lecture seule pour les objets QueryDef.

Type : Collection

Collection regroupant l'ensemble des objets Property de l'objet Field.

Type : Boolean

La propriété Required indique si le champs accepte les valeurs nulles. Si cette propriété est égale à True, une erreur sera levée si vous tentez d'affecter une valeur nulle à ce champ.

Type : Integer

La propriété Size définit la taille du champ comme vous pouvez le faire en mode création. Cette propriété n'est en lecture écriture que pour les objets Field qui n'ont pas encore été ajoutés à la collection Fields.

Lecture seule.

Type : String

Cette propriété en lecture seule indique dans un objet QueryDef le nom d'un champ et non son alias. Soit une requête nommée R01 :

SELECT NomClient as LeNom FROM Client

Le code suivant affichera LeNom :

Dim oDb As DAO.Database
Dim oQR As DAO.QueryDef
Set oDb = CurrentDb
Set oQR = oDb.QueryDefs("R01")
MsgBox oQR.Fields(0).Name

Cet autre code affichera NomClient :

Dim oDb As DAO.Database
Dim oQR As DAO.QueryDef
Set oDb = CurrentDb
Set oQR = oDb.QueryDefs("R01")
MsgBox oQR.Fields(0).SourceField

Nous pouvons donc écrire la fonction suivante qui retourne le nom d'un champ en fonction de son alias :

Function NomChamp(oBaseDeDonnee As DAO.Database, strRequete As String, strAlias As String) As String
On Error GoTo err
Dim Qdf As DAO.QueryDef
Set Qdf = oBaseDeDonnee.QueryDefs(strRequete)
NomChamp = Qdf.Fields(strAlias).SourceField
Exit Function
'Gère les erreurs
err:
Select Case err.Number
 Case 3265
  'Si qdf est vide alors la requete n'existe pas
  If Qdf Is Nothing Then
    MsgBox "Impossible de trouver la requête " & strRequete
  Else
    
    MsgBox "Impossible de trouver le champ " & strAlias & " dans la requete " & _
    strRequete
  End If
  Case Else: MsgBox "Une erreur inattendue est survenue"
End Select
End Function

Utilisation :

Dim oDb As DAO.Database
Set oDb = CurrentDb
MsgBox NomChamp(oDb, "R01", "LeClient")

Lecture seule.

Type : String

Cette propriété repose sur le même principe que la précédente. Elle permet de savoir de quelle table provient un champ d'une requête.

Ainsi, avec l'exemple précédent, voici comment connaître la table d'origine du champ LeClient :

Dim oDb As DAO.Database
Dim oQR As DAO.QueryDef
Set oDb = CurrentDb
Set oQR = oDb.QueryDefs("R01")
MsgBox oQR.Fields(0).SourceTable

Type : Integer

Cette propriété définit le type du champ. Il s'agit d'une des constantes suivantes :

Type : String

La propriété ValidationRule définit la règle de validation qui sera utilisée pour valider les données lors d'une mise à jour (Insert compris).

Type : String

La propriété ValidationText correspond au message qui sera affiché lors d'une mise à jour si les données ne vérifient pas la règle spécifiée dans ValidationRule.

Type : Variant

Pour un objet Recordset, la propriété Value retourne la valeur du champ pour l'enregistrement courant.

Type : Variant

Cette propriété est utilisable uniquement dans un environnement ODBCDirect. Elle renvoit la valeur actuelle d'un champ lors d'un traitement par lot. Cette valeur est donc plus récente que celle retournée par la propriété OriginalValue

La méthode AppendChuk permet d'ajouter des données à la fin d'un champ de type mémo ou binaire. (Collection Recordsets exclusivement) Elle évite notamment de lire le début du champ, de concaténer et de renvoyer la nouvelle valeur. En utilisant la méthode AppendChunk, vous limiter donc le traffic d'informations entre la base de données et le moteur Jet.

Syntaxe :

field.AppendChunk source

L'argument source est de type Variant.

La méthode CreateProperty crée et retourne un nouvel objet Property qu'il vous faudra ensuite ajouter à la collection Properties de l'objet Field.

La syntaxe de la méthode est celle illustrée dans la partie : Les propriétés spécifiques.

La méthode GetChunk s'applique aux champs des objets Recordset. Elle renvoie le contenu (ou une partie) d'un champ mémo ou binaire dans une variable de type String.

Syntaxe :

Set variable = field.GetChunk (offset, numbytes)

Le paramètre offset de type Long correspond à la position du caractère (ou de l'octet) à partir duquel doit commencer la lecture.

L'argument numbytes détermine le nombre de caractères (ou d'octet) à lire.

Tout comme c'était déjà le cas avec les tables, cette opération nécessite que le nouveau nom soit unique dans la collection Fields de l'objet TableDef.

Les objets Relation et Index utilisant ce champ sont automatiquement mis à jour par le moteur Jet.

Private Function RenommerChamp(oBaseDeDonnees As DAO.Database, strNomTable As String, _
  strAncienNomChamp As String, strNouveauNomChamp As String) As Boolean
 
On Error GoTo err
 Dim Tbl As DAO.TableDef
 'Récupère la table
 Set Tbl = oBaseDeDonnees.TableDefs(strNomTable)
 'Renomme le champ
 Tbl.Fields(strAncienNomChamp).Name = strNouveauNomChamp
 'Renvoie True
 RenommerChamp = True
 'Gère les erreurs
 Exit Function
err:
Select Case err.Number
 'Si impossible de trouver l'élément dans la collection
 Case 3265
  'Si tbl=nothing alors tbl est la cause de l'erreur
  If Tbl Is Nothing Then
    MsgBox "Impossible de trouver la table : " & strNomTable
  Else
    MsgBox "Impossible de trouver le champ : " & strAncienNomChamp
  End If
  Case 3010, 3191: MsgBox "Le champ " & strNouveauNomChamp & " existe déjà"
  Case Else: MsgBox "Une erreur inattendue est survenue"
End Select
End Function

Utilisation :

Dim oDb As DAO.Database
Set oDb = CurrentDb
If RenommerChamp (oDb, "Commande", "Reference", "Nouvelle_Reference") then
  msgbox "Opération terminée"
End if

La suppression d'un champ est trés délicate. En effet, plusieurs erreurs peuvent être levées au cours de cette opération :

• 3265 : Le champ n'existe pas
• 3280 : Le champ est utilisé par un index
• 3303 : Le champ est utilisé dans une ou plusieurs relations

L'erreur 3303 est difficile à maitriser car même si l'erreur est levée, le champ est quand même supprimé. Toutefois les relations sont toujours présentes et si vous rajoutez par la suite le champ supprimé, la relation va "renaître", ce qui provoque un comportement inattendu.

Il nous faut donc savoir si le champ est l'objet de relations.

Private Function ChampDansRelation(oBaseDeDonnees As DAO.Database, _
 strNomTable As String, strNomChamp As String) As Integer
On Error GoTo err
 Dim bolUtilise As Boolean
 Dim oRlt As DAO.Relation
 Dim oFld As DAO.Field
  'Parcours les relations
  For Each oRlt In oBaseDeDonnees.Relations
   bolUtilise = False
   If oRlt.Table = strNomTable Then
     'Pour chaque champ, contrôle le nom du champ
     For Each oFld In oRlt.Fields
       If oFld.Name = strNomChamp Then bolUtilise = True
     Next oFld
  ElseIf oRlt.ForeignTable = strNomTable Then
    'pour chaque champ, contrôle le nom étranger
    For Each oFld In oRlt.Fields
       If oFld.ForeignName = strNomChamp Then bolUtilise = True
    Next oFld
  End If
  'Si la relation utilise le champ, on incremente le resultat
  If bolUtilise Then ChampDansRelation = ChampDansRelation + 1
 Next oRlt
Exit Function
err:
ChampDansRelation = -1
End Function

Cette fonction renvoie le nombre de relations qui utilisent le champ passé en paramètre.

Utilisation :

Dim oDb as DAO.Database
Set oDb=CurrentDB
MsgBox ChampDansRelation(oDb, "Commande", "Reference")

Cet exemple affichera le nombre de relations utilisant le champ Reference de la table Commande.

La fonction retourne -1 si une erreur est survenue.

Nous pouvons donc écrire la fonction de suppression suivante :

Private Function SupprimerChamp(oBaseDeDonnees As DAO.Database, strNomTable As String, _
  strNomChampASupprimer As String) As Boolean
 
On Error GoTo err
 Dim oTbl As DAO.TableDef
 'Verifie que le champ ne fait pas partie d'une relation
 If ChampDansRelation(oBaseDeDonnees, strNomTable, strNomChampASupprimer) = 0 Then
  'Récupère la table
   Set oTbl = oBaseDeDonnees.TableDefs(strNomTable)
  'Supprime le champ
  oTbl.Fields.Delete (strNomChampASupprimer)
  'Renvoie True
  SupprimerChamp = True
 Else
  MsgBox "Impossible de supprimer le champ, il est utilisé par une ou plusieurs relations"
 End If
Exit Function
err:
Select Case err.Number
 'Si impossible de trouver l'élément dans la collection
 Case 3265
  'Si tbl=nothing alors tbl est la cause de l'erreur
  If Tbl Is Nothing Then
    MsgBox "Impossible de trouver la table : " & strNomTable
  Else
    MsgBox "Impossible de trouver le champ : " & strNomChampASupprimer
  End If
  Case 3280: MsgBox "Impossible de supprimer un champ utilisé par un index"
  Case Else: MsgBox "Une erreur inattendue est survenue"
End Select
End Function

Utilisation :

SupprimerChamp oDb, "Commande", "Reference"

La création d'un champ se décompose en trois étapes :

Un exemple sera certainement plus parlant que de longues phrases, je vous propose de créer un champ AdresseClient dans une table nommée Client.
Ce champ aura pour cractéristiques :

• Type : Texte
• Longueur : 120 caractères
• Chaine vide autorisée : Non
• Null interdit : Oui

Dim oDb As DAO.Database
Dim oTbl As DAO.TableDef
Dim oFld As DAO.Field
Set oDb = CurrentDb
Set oTbl = oDb.TableDefs("Client")
'Etape 1 : Créer le champ
Set oFld = oTbl.CreateField("AdresseClient", dbText, 120)
'Etape 2 : Définit les propriétés
oFld.AllowZeroLength = False 'Chaine vide autorisée : Non
oFld.Required = True         'Null interdit : Oui
'Etape 3 : Ajout du champ à la table
oTbl.Fields.Append Fld
'Rafraichit la collection
oTbl.Fields.Refresh

Il est important de respecter l'ordre de chaque étape. Si vous ajouter le champ à la collection Fields avant de lui avoir affecter l'ensemble de ses propriétés, vous vous apercevrez que certaines de ces propriétés ne sont plus accessibles en écriture.

Nous n'avons pas traité ici la gestion d'erreur. Celle ci exactement la même que pour la fonction qui renomme un champ. En effet, le plus grand risque pour ce programme est d'utiliser un nom de champ qui existe déjà.

Je vous propose ici un code qui peut s'avérer utile lorsque vous souhaiter créer un champ identique à un autre. Le principe est simple, nous partons de deux objets Field. Le premier sera utilisé comme source du clonage. Nous allons donc parcourir chaque propriété (exceptée la propriété Name) du champ source et les réécrire dans le champ de destination.

Certaines de ces propriétés risquent d'être en lecture seule. Il nous faut donc déleguer la lecture et l'écriture des propriétés à une autre procédure afin de ne pas faire stopper le programme à la première erreur rencontrée.

Public Sub CloneChamp(oFldSource As DAO.Field, oTableDestination As DAO.TableDef, _
  strNouveauNom As String)
On Error GoTo err
Dim oFld As DAO.Field
Dim oPrp As DAO.Property
'Crée le champ
Set oFld = oTableDestination.CreateField(strNouveauNom)
'Pour chaque propriete
For Each oPrp In oFldSource.Properties
 'copier la propriété s'il ne s'agit pas du nom
 If oPrp.Name <> "Name" Then _
        EcrirePropriete oPrp, oFld
Next oPrp
'Ajoute le champ
oTableDestination.Fields.Append oFld
Exit Sub
err:
Select Case err.Number
   Case 3010, 3191: MsgBox "Le champ " & strNouveauNom & " existe déjà"
   Case Else: MsgBox "Une erreur inattendue est survenue"
End Select
End Sub


Private Sub EcrirePropriete(oPropriete As DAO.Property, _
  oChampDestination As DAO.Field)
On Error GoTo err
'Copie la propriété
With oChampDestination.Properties
 .Item(oPropriete.Name) = oPropriete.Value
 .Refresh
End With
'On ne traite pas les erreurs
err:
End Sub

Pour dupliquer le champ AdresseClient de la table Client dans la table SauvegardeClient nous utiliserons :

Dim oDb As DAO.Database
Dim oTblSource As DAO.TableDef, oTblDestination As DAO.TableDef
Dim oFld As DAO.Field
Set oDb = CurrentDb
'Stocke les deux tables
Set oTblSource = oDb.TableDefs("Client")
Set oTblDestination = oDb.TableDefs("SauvegardeClient")
'Stocke le champ
Set oFld = oTblSource.Fields("AdresseClient")
'Clone le champ
CloneChamp oFld, oTblDestination, "AdresseClient2"

Lecture seule.

Type : Long

La propriété DistinctCount renvoie le nombre de valeurs uniques présentes dans la table. Dans le cas d'un index mono-champ, il s'agit du nombre de valeurs distinctes dans ce champ (équivalent d'une requête SELECT DISTINCT MonChamp ...). Dans le cas d'un index composé de plusieurs champs, cette valeur correspond au nombre de combinaisons distinctes des valeurs de ces champs au sein de la table (équivalent d'une requête SELECT DISTINCT MonChamp1,MonChamp2 ...).

Type : Collection

Cette collection regroupe l'ensemble des champs composant l'index. Chaque objet Field de cette collection doit posséder le même nom qu'un objet Field de la table concernée.

Lecture seule.

Type : Boolean

Lorsque la propriété Foreign d'un index est égale à True, cela signifie que l'index est une clé étrangère. C'est notamment le cas pour les index ajoutés automatiquement par Access lorsque vous créez des relations entre les tables.

Type : Boolean

Si vous définissez la propriété IgnoreNulls à True, les valeurs nulles ne seront pas indéxées. Cela peut être utile si vous prévoyer d'enregistrer un grand nombre de valeur nulles.

Type : String

Cette propriété correspond au nom de l'index. Ce nom doit être unique dans la collection Indexes de l'objet TableDef correspondant.

Prévoyez des noms d'index aussi clairs que possible. Aussi certains préfixent le nom de leurs index par les initiales de leur rôle. Ainsi, une clé primaire sera notée PK_MonChamp (PK = Primary Key).

Type : Collection

Collection regroupant l'ensemble des objets Property de l'objet Index.

Type : Boolean

Si la propriété Required est égale à True, cela signifie que les champs de l'index doivent tous recevoir une valeur, sans quoi, lors des mises à jour, une erreur sera levée.

Type : Boolean

Lorsque la propriété Unique est égale à True, les doublons sont interdits dans les champs de l'index.

Dans un index multi-colonnes, les valeurs A A et A B ne sont pas des doublons.

Cette méthode est la même que celle appliquée aux objets TableDef, à la différence prés quelle ne propose qu'un seul paramètre : le nom du champ. Ce nom doit être identique à celui du champ de la table sur lequel repose l'index.

Syntaxe :

Set field = index.CreateField (name)

Cette méthode n'est plus à présenter, et pour cause, nous la retrouvons à chaque chapitre de ce tutoriel. Aussi, je vous renvoie vers le chapitre des 4.2.5 Propriétés spécifiques.

Soit une table Client possédant plusieurs champs dont NumeroClient. Nous allons créer une clé primaire sur ce champ.

Dim oDb As DAO.Database
Dim oTbl As DAO.TableDef
Dim oFld As DAO.Field
Dim oInd As DAO.Index
'Instancie la base
Set oDb = CurrentDb
'Instancie la table
Set oTbl = oDb.TableDefs("Client")
'Crée l'index en le nommant PK_Client
Set oInd = oTbl.CreateIndex("PK_Client")
'Cree le champs dans l'index
Set oFld = oInd.CreateField("NumeroClient")
'Ajoute le champ à la collection Fields
oInd.Fields.Append oFld
'Définit la clé primaire
oInd.Primary = True
'Ajoute l'index à la table
oTbl.Indexes.Append oInd
'Rafraichit la collection
oTbl.Indexes.Refresh

Afin d'illustrer la suppression d'un index je vous propose de traiter le cas, où nous souhaitons supprimer la clé primaire de la table. Il nous faut donc parcourir l'ensemble des index de l'objet TableDef et les supprimer si leur propriété Primary est égale à True.

Private Sub SupprimerClePrimaire(oTbl As DAO.TableDef)
On Error GoTo err
Dim oInd As DAO.Index
'Parcours les index
For Each oInd In oTbl.Indexes
  'S'il s'agit d'une clé primaire, alors le supprimer
  If oInd.Primary Then
    oTbl.Indexes.Delete (oInd.Name)
  End If
Next oInd
Exit Sub
err:
MsgBox "Une erreur inattendue est survenue"
End Sub

Utilisation :

Dim oDb As DAO.Database
Dim oTbl As DAO.TableDef
'Instancie la base
Set oDb = CurrentDb
'Instancie la table
Set oTbl = oDb.TableDefs("Client")
'Supprime la clé primaire
SupprimerClePrimaire oTbl

Type : Long

La propriété Attributes correspond au type de la contrainte représentée par l'objet Relation. La valeur de cette propriété doit être une (ou une combinaison) des constantes suivantes :

Type : Collection

Cette collection regroupe l'ensemble des champs utilisés par la relation. Vous devez utiliser la méthode Relation.CreateField pour créer de nouveaux champs dans la relation et les ajouter à la collection Fields à l'aide de la méthode Append.

Chaque objet Field de la relation doit posséder le nom du champ concerné dans la table spécifiée dans la propriété Table. Ce champ aura comme propriété ForeignName, le nom d'un champ de la table spécifiée dans la propriété ForeignTable.

Type : String

La propriété ForeignTable correspond au nom de la table externe de la relation. Ce nom doit être celui d'un objet TableDef présent dans la collection TableDefs de l'objet Database.

Type : String

La propriété Name représente le nom de la relation. Il doit être unique au sein de la collection Database.Relations.

L'utilisation de nom précis facilitera les opérations de maintenance sur la base de données. Ainsi, une relation entre une table commande et une table client sera nommée Rlt_CommandeClient. Evitez d'appeler vos objets Relation1, Table1, Requete1...

Type : Collection

La collection Properties regroupe l'ensemble des propriétés de l'objet Relation.

Type : String

La propriété Table correspond au nom de la table principale sur laquelle porte la relation. Il s'agit de la table possédant la clé primaire utilisée dans la relation. A contrario, la propriété ForeignName correspond à la table possédant la clé étrangère de la relation.

Reprenons l'exemple ci-dessus. Je vous livre le code correspondant à une telle relation.

La première méthode consiste à créer la relation et affecter à chacune des propriétés sa valeur correspondante.

Dim oDb As DAO.Database
Dim oRlt As DAO.Relation
Dim oFld As DAO.Field
Set oDb = CurrentDb
'Crée la relation
Set oRlt = oDb.CreateRelation
'Définit les propriétés
oRlt.Attributes = dbRelationUpdateCascade
oRlt.ForeignTable = "Commande"
oRlt.Name = "Rlt_CommandeClient"
oRlt.Table = "Client"
'Crée le champ dans la relation
Set oFld = oRlt.CreateField("NumClient")
'Définit le nom de la clé externe
oFld.ForeignName = "IDClient"
'Ajoute le champ
oRlt.Fields.Append oFld
'Rafraîhit la collection Relations
oDb.Relations.Refresh
'Ajoute la relation
oDb.Relations.Append oRlt
'Rafraîhit la collection Relations
oDb.Relations.Refresh
'Ferme la base de données
oDb.Close
Set oDb = Nothing

La seconde méthode consite à utiliser l'ensemble des arguments de la méthode CreateProperty. En effet celle-ci permet d'initialiser le nom de la relation, de la table, de la table externe ainsi que les attributs.

Syntaxe :

Set relation = database.CreateRelation (name, table, foreigntable, attributes)

Notre code devient alors :

Dim oDb As DAO.Database
Dim oRlt As DAO.Relation
Dim oFld As DAO.Field
Set oDb = CurrentDb
'Crée la relation
Set oRlt = oDb.CreateRelation("Rlt_CommandeClient", "Client", _
  "Commande", dbRelationUpdateCascade)
'Crée le champ
Set oFld = oRlt.CreateField("NumClient")
'Définit le nom de la clé externe
oFld.ForeignName = "IDClient"
'Ajoute le champ
oRlt.Fields.Append oFld
'Rafraîhit la collection Relations
oDb.Relations.Refresh
'Ajoute la relation
oDb.Relations.Append oRlt
'Rafraîhit la collection Relations
oDb.Relations.Refresh
'Ferme la base de données
oDb.Close
Set oDb = Nothing

Cette fonction a déjà été utilisé lors de l'opération qui consistait à supprimer un champ dans un objet TableDef.

Le principe est simple, un champ est utilisé par une relation si :

• La table dont il provient est présent dans la propriété Table de la relation et que le nom d'un champ de la relation est égal au nom de ce champ.
• La table dont il provient est présent dans la propriété ForeignTable de la relation et qu'au moins un champ de cette relation possède une propriété ForeignName égale au nom de ce champ.

Il nous suffit donc de boucler sur l'ensemble des objets Relation de la base de données.

Private Function ChampDansRelation(oBaseDeDonnees As DAO.Database, _
 strNomTable As String, strNomChamp As String) As Integer
On Error GoTo err
 Dim bolUtilise As Boolean
 Dim oRlt As DAO.Relation
 Dim oFld As DAO.Field
  'Parcours les relations
  For Each oRlt In oBaseDeDonnees.Relations
   bolUtilise = False
   If oRlt.Table = strNomTable Then
     'Pour chaque champ, contrôle le nom du champ
     For Each oFld In oRlt.Fields
       If oFld.Name = strNomChamp Then bolUtilise = True
     Next oFld
  ElseIf oRlt.ForeignTable = strNomTable Then
    'pour chaque champ, contrôle le nom étranger
    For Each oFld In oRlt.Fields
       If oFld.ForeignName = strNomChamp Then bolUtilise = True
    Next oFld
  End If
  'Si la relation utilise le champ, on incremente le resultat
  If bolUtilise Then ChampDansRelation = ChampDansRelation + 1
 Next oRlt
Exit Function
err:
ChampDansRelation = -1
End Function

Il devient alors possible de connaître le nombre de relations utilisant le champ NumClient de la table Client.

Utilisation :

Dim oDb as DAO.Database
Set oDb=CurrentDB
MsgBox ChampDansRelation(oDb, "Client", "NumClient")

Il peut s'avérer par fois utile de supprimer les relations portant sur une table avant de pouvoir modifier la structure de la table. Cette opération peut s'avérer fastidieuse et je vous suggère de créer une fonction automatisant cette tâche.

Public Function SupprimerRelationTable(oBaseDeDonnees As _
 DAO.Database, strNomTable As String) As Integer
Dim oRlt As DAO.Relation
'Pour chaque relation,
For Each oRlt In oBaseDeDonnees.Relations
  'si la table est utilisée
 If oRlt.Table = strNomTable Or oRlt.ForeignTable = strNomTable Then
   'Supprime la relation
   oBaseDeDonnees.Relations.Delete oRlt.Name
   oBaseDeDonnees.Relations.Refresh
   'Incrémente le compteur
   SupprimerRelationTable = SupprimerRelationTable + 1
 End If
Next oRlt
End Function

La fonction SupprimerRelationTable retourne le nombre de relations supprimées.

Utilisation :

Dim oDb As DAO.Database
Dim oRlt As DAO.Relation
Set oDb = CurrentDb
'Supprime les relations de la table Client
MsgBox SupprimerRelationTable(oDb, "Client")

Lecture seule.

Type : String

La propriété Connect renvoie la chaine de connexion utilisée par la requête lorsque celle ci provient d'une autre base de données.

Lecture seule.

Type : Date

Cette propriété renvoie la date à laquelle a été créé la requête.

Lecture seule.

Type : Collection

Une nouvelle fois, nous rencontrons cette collection qui regroupe l'ensemble des champs retournés par la requête.

Notez que vous pouvez différencier le nom d'un champ et son alias en utilisant la propriété SourceField de l'objet Field.

Lecture seule.

Type : Date

Cette propriété renvoie la date à laquelle a été modifiée la requête pour la dernière fois.

Type : Long

La propriété MaxRecords correspond au nombre d'enregistrements à renvoyer au maximum. Cette propriété peut être utilisée lorsque le poste client ne dispose pas de ressources suffisantes afin de traiter l'ensemble des données.

Cette propriété est disponible uniquement pour les requêtes utilisant une source de données ODBC.

Type : String

La propriété Name représente le nom de la requête. Il doit être unique au sein de la collection Database.QueryDefs.

Lorsque vous utilisez la méthode CreateQueryDef et que vous ne spécifiez pas l'argument Name, la requête n'est pas sauvegardée dans la base de données à la fin du traitement.

Type : Collection

Cette collection regroupe l'ensemble des paramètres d'un objet QueryDef. Il est impossible d'ajouter ou de supprimer des éléments de cette collection.

Un exemple d'utilisation de requête paramétrée est disponible dans les définitions des QueryDefs ci-dessus.

Type : Collection

La collection Properties regroupe l'ensemble des propriétés de l'objet QueryDef.

Lecture seule.

Type : Long

Dans le cas d'une requête action (Insert,Update,Delete), la propriété RecordsAffected retourne le nombre d'enregistrements affectés par l'ordre SQL.

Type : Boolean

Cette propriété définit ou renvoie une valeur indiquant si une requête retourne des enregistrements.

La propriété ReturnsRecords est disponible uniquement pour les requêtes SQL directes.

Type : String

La propriété SQL correspond au code SQL de la requête.

Cette propriété correspond à l'argument SQL de la méthode Database.CreateQueryDef.

Type : Long

La propriété Type renvoie le type de la requête. Cette propriété est définie automatiquement lorsque vous créez l'objet QueryDef. Toutefois, elle peut vous permettre de recenser les objets QueryDef suivant leur rôle.

Les valeurs possibles sont :

Lecture seule.

Type : Boolean

Cette propriété renvoie True si les données de la requête ne peuvent être modifiées.

La méthode CreateProperty crée un nouvel objet Property. Plus de détails sont disponibles dans le chapitre 4.2.5 Propriétés spécifiques.

La méthode Execute éxecute une requête Action. Une erreur sera levée si vous tentez d'exécuter une requête de sélection.

Syntaxe :

querydef.Execute options

L'argument options doit être une (ou une combinaison) des constantes suivantes :

Vous pouvez utiliser la propriété RecordsAffected pour connaître le nombre d'enregistrements modifiés.

La méthode OpenRecordset ouvre un jeu d'enregistrements sur le résultat de la requête.

Syntaxe :

Set recordset = object.OpenRecordset (type, options, lockedits)

Cette méthode sera largement abordée dans le chapitre suivant.

Dans notre exemple nous allons créer une requête de sélection complète de la table clients. Il s'agit d'une requête simple et la méthode à appliquer est la même pour tout type de requête.

Sub CreerRequete()
On Error GoTo Err
Dim oDb As DAO.Database
Dim strCodeSql As String
'Instancie l'objet database
Set oDb = CurrentDb
'Stocke le code SQL dans une variable
strCodeSql = "SELECT * FROM Client"
'Crée la requête
oDb.CreateQueryDef "R01_SelectionClient", strCodeSql

Finally:
oDb.Close
Set oDb = Nothing

Err:
'Gére les erreurs
Select Case Err.Number
  Case 3012: MsgBox "La requête existe déjà"
  Case Else: MsgBox "Une erreur inconue est survenue"
End Select
Resume Finally
End Sub

Afin de modifier le code SQL, il faut procéder en deux étapes :

• Récuperer l'objet QueryDef correspondant (S'il existe).
• Modifier la propriété SQL de l'objet.

Public Function ModifierSQL(oBaseDeDonnees As DAO.Database, _
  strNomRequete As String, strCodeSQL As String) As Boolean
On Error GoTo Err
Dim oQdf As DAO.QueryDef
'Instancie l'objet QueryDef
Set oQdf = oBaseDeDonnees.QueryDefs(strNomRequete)
'Modifie le code SQL
oQdf.SQL = strCodeSQL
'Renvoie True
ModifierSQL = True
Exit Function
Err:
'Gere les erreurs
Select Case Err.Number
  Case 3265: MsgBox "Impossible de trouver la requête à modifier"
  Case Else: MsgBox "Une erreur inattendue est survenue"
End Select
End Function

Utilisation :

Dim oDb As DAO.Database
Set oDb = CurrentDb
If ModifierSQL(oDb, "R01_SelectionClient", "SELECT NumClient FROM Client") Then
 MsgBox "Le code SQL de la requête a été modifié avec succés"
End If

La propriété Type retourne le type d'un objet QueryDef. Il est donc possible de lister l'ensemble des requêtes de sélection.

Dim oDb As DAO.Database
Dim oQdf As DAO.QueryDef
'Instancie l'objet Database
Set oDb = CurrentDb
'Boucle sur les objets QueryDef
For Each oQdf In oDb.QueryDefs
  'S'il s'agit d'une requête de sélection, alors afficher son nom
  If oQdf.Type = dbQSelect Then MsgBox oQdf.Name, , "Sélection"
Next oQdf