Tatiak

Excel, Vba, Vbs


23 juin 2017

Modélisation Tables Base de données

Modelisation_v0

Il me semble de plus en plus intéressant d'utiliser des tables Access depuis Excel.
En effet, même sans licence Access, il est possible de stocker ses données dans un fichier accdb par exemple pour alleger ses fichiers Excel et/ou 'protéger' les données de modification/effacement malencontreux.

Ceci étant dit, la première étape importante avant de se lancer dans le codage d'une appli Excel<=>accdb est de bien concevoir les tables à utiliser. Deux points sont à soigner : la structure des tables d'une part et les relations entre ces tables.
Pour visualiser ce que vous voulez mettre en place, rien de mieux que de dessiner votre modèle de base de données en faisant figurer vos tables (et leurs contenus) et les liens les reliant.

Pour ce faire, je vous propose aujourd'hui une petite appli Excel de modélisation.
L'utilisation est simple :

  • dans l'onglet 'Tables' vous complétez la structure de chacune de vos tables (colonnes A à H)
  • dans ce même onglet, vous creez les liaisons (colonnes J à L) que vous souhaitez en reprenant le numéro (num) d'ordre de vos champs. Dans l'exemple le champs Id de la tables CLIENTS est relié à l'Id_c de la table FACTURES avec une relation 1 à plusieurs (un client peut avoir plusieurs factures).
  • Dans l'onglet 'Diagramme', 3 boutons :
    •  'Actualiser Tables' : pour dessiner chaque tables. Ces tables pourront être repositionnées comme vous le souhaitez (notamment pour mieux visualiser les relations)
    •  'Actualiser Relations' : pour redessiner les liens entre tables
    •  'Créeer .accdb' : pour générer le fichier Access à la fin de votre modélisation. Lors de cette étape, il vous sera demandé de nommer votre base. A la fin du processus, un fichier log attestera du bon déroulement de la création des tables.
  • Pour repérer les clés sur ce diagramme (voir illustration) :
    • Les clés primaires seront précédées d'un * et seront en gras +soulignées
    • Les clés étrangères seront en gras + soulignées


L'outil, peut-être un peu simpliste, n'a pas d'autre ambition que de modéliser des petites bases de quelques tables pour celui qui de possède pas de licence Access. Il vous restera ensuite la tâche d'écrire le code pour lire et écrire dans ces tables => en utilisant par exempe cette méthode


N'hésitez pas à poster vos commentaires pour faire évoluer cette appli!
Bon téléchargement
Pierre

 

Téléchargement de : Modélisation_Tables_Access_v0.3.xlsm

 

Edit du 27/06/2017 : Pour répondre à la demande de Christophe : voici la version v0.3 qui permet de définir un mot de passe de son choix lors de la création de la base.
Secondairement pour utiliser cette base, il suffira de demander le mot de passe, par exemple à l'ouverture du fichier, puis de le stocker dans une variable 'public' (MDP par exemple). Cette variable 'MDP' sera à utiliser dans la chaine de connexion, du genre :
Cnx.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & BDD & ";Jet OLEDB:Database Password=" & MDP & ";"
(BDD étant ici le nom de la base avec son chemin et son extension)
Avec une méthode de ce genre : pas de mot de passe en clair dans le code.

 

Posté par tatiak à 11:50 - Excel-Base de Données - Commentaires [3] - Permalien [#]

Commentaires sur Modélisation Tables Base de données

    Encore un excellent article de la part de pierre.
    Peut-on créer la base de données (qui sera d'abord vide) en la protégeant à sa création avec un mot de passe (via Jet OLEDBatabase Password=motDePasse puis initialiser chaque table et les champs voulus ?
    Ok le mot de passe sera en clair dans le code vba du requêteur, le but ici ne serait pas de protéger la base d'une intrusion mais plutôt d'une erreur de manipulation, par exemple via une requête vba destinée à une autre base de données (copie d'un code vba fonctionnel pour test sur une autre bdd, mais toutes les références à la bdd d'origine n'auraient pas été 'nettoyées').
    Merci

    Posté par christophe, 27 juin 2017 à 12:33 | | Répondre
    • Je ne sais pas si j'ai bien compris la demande, mais voici la version v0.3 qui permet de définir un mot de passe de son choix lors de la création de la base.
      Secondairement pour utiliser cette base, il suffira de demander le mot de passe, par exemple à l'ouverture du fichier, puis de le stocker dans une variable 'public' (MDP par exemple). Cette variable 'MDP' sera à utiliser dans la chaine de connexion, du genre :
      Cnx.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & BDD & ";Jet OLEDBatabase Password=" & MDP & ";"
      (BDD étant ici le nom de la base avec son chemin et son extension)
      Avec une méthode de ce genre : pas de mot de passe en clair dans le code.
      Pierre

      Posté par tatiak, 27 juin 2017 à 14:51 | | Répondre
  • c'est tout à fait ça pierre, merci beaucoup

    Posté par christophe, 28 juin 2017 à 22:05 | | Répondre
Nouveau commentaire