Se connecter à des tables Oracle depuis Access

Une base Access peut se connecter à d’autres moteurs de bases de données (SQL Server, Oracle, MySQL, PostgreSQL, etc.) par différents mécanismes. Dans cet article, nous allons voir comment nous connecter à un serveur Oracle au travers d’une passerelle ODBC.

Les outils nécessaires

Si votre Access réside sur la même machine que le serveur Oracle, vous n’aurez rien de particulier à installer (les pilotes ODBC et leur configuration sont déjà en place). Dans ce cas, vous pouvez déjà passer au paragraphe Réglage d’un DSN Oracle, plus bas.

On va supposer que votre machine ne comporte aucun outil Oracle, ce qui est d’ailleurs plus réaliste (Access va être utilisé en tant que client du serveur Oracle). Notre principal objectif est donc d’installer le pilote ODBC pour Oracle. Pour ça, Oracle fournit plusieurs programmes d’installation ; le plus direct (encore que…) est le driver dit « Instant Client« . Pour cela, connectez-vous au site Oracle et téléchargez :

  • Un premier fichier nommé « Instant Client Basic For Win32 » ;
  • Un second fichier nommé « Instant Client Package – ODBC« .

Choisissez bien sûr les fichiers adaptés à votre version d’Oracle. Vous devrez probablement disposer d’un compte Oracle Technology Network (OTN, inscription gratuite) pour télécharger certains de ces fichiers (Oracle n’aime pas faire les choses de façon simple et directe 😉 ).

Installation du pilote ODBC pour Oracle

  1. Une fois les 2 fichiers téléchargés, décompressez-les dans un dossier spécifique (par exemple : C:OracleODBC). Le contenu des 2 fichiers doit être décompressé dans le même dossier.
  2. Dans le dossier en question, double-cliquez sur le fichier odbc_install.exe.
    Le pilote proprement dit est alors installé (ça prend 1 nanoseconde).

Paramétrage de l’accès au serveur

Le pilote ODBC a besoin de 3 réglages supplémentaires pour accéder correctement au serveur Oracle :

Le fichier tnsnames.ora

Créez un fichier tnsnames.ora, que vous placerez dans le même dossier que votre pilote ODBC (donc C:OracleODBC, si vous avez suivi l’exemple plus haut). Ce fichier est un fichier texte qui décrit l’accès au serveur (nom du serveur, de la base, protocole…).
Ce fichier peut avoir ce type de structure :

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = XYZ)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )
  • ORCL est le nom de la base créée par défaut sous Oracle.
    Modifiez-le (ainsi que le service associé, après SERVICE_NAME), si vous utilisez un autre nom.
  • Remplacez XYZ par le nom du serveur (ex. : MONSERVEUR) ou par son adresse IP (ex. : 192.168.0.100).
  • Modifiez également le port si nécessaire (la valeur 1521 est celle par défaut d’Oracle).
  • Pour plus de détails, consultez la documentation Oracle concernant les fichiers tnsnames.
Attention
Vérifiez bien que l’extension du fichier est .ora et non pas .txt (méfiez-vous des extensions masquées par défaut sous Windows !).

La variable d’environnement TNS_ADMIN

Reste à indiquer au pilote où trouver ce fichier tnsnames.ora. Pour cela, vous devez encore configurer une variable d’environnement nommée TNS_ADMIN. Voici comment faire sous Windows XP (c’est sensiblement identique sous Vista) :

  1. Faites un clic du bouton droit sur le Poste de travail, choisissez Propriétés.
  2. Cliquez sur l’onglet Avancé.
  3. En bas de la boîte, cliquez sur le bouton Variables d’environnement.
  4. Cliquez sur le bouton Nouveau, dans cette boîte (dans la partie Variables système, pour notre exemple).
  5. Tapez TNS_ADMIN dans la zone « Nom de la variable« , et le chemin de votre pilote ODBC dans la zone « Valeur de la variable« .
  6. Validez tout ça !

La variable d’environnement NLS_LANG

Si votre base Oracle travaille en jeu de caractères français, vous risquez de ne pas voir les accents dans Access. Pour les retrouver, ajoutez également la variable d’environnement suivante :

  • Nom de la variable : NLS_LANG
  • Valeur de la variable : FRENCH_FRANCE

La variable NLS_LANG prend en principe une valeur de la forme LANGUE_TERRITOIRE.JEUDECARACTERES. Ici, LANGUE = FRENCH, TERRITOIRE = FRANCE, et le jeu de caractères est omis. Pour plus de détails, consultez la documentation Oracle.
Le pilote ODBC est en place une fois pour toutes !

Réglage d’un DSN pointant vers la base Oracle

Il reste à définir un DSN ODBC. Un DSN (Data Source Name, « nom de source de données ») peut se résumer à un nom qui servira de raccourci pour pointer vers une base de données, au travers du pilote ODBC.
Dans notre exemple, nous allons construire un DSN avec les informations suivantes :

  • Data Source Name (nom de la source de données): OracleVid
  • Description : ce que vous voulez !
  • TNS Service Name : nom de la base (ou du service à employer ; correspond au ORCL vu plus haut).
  • User Id (nom d’utilisateur) : nom d’un utilisateur ayant des droits d’accès à la base Oracle
    Le mot de passe de cet utilisateur n’est pas stocké ici, il est demandé à chaque « première connexion ».

Le bas de la boîte permet d’effectuer des réglages plus fins (encore une fois, pour les détails, consultez la documentation Oracle !).
Parmi ces réglages, cochez notamment la case Read-Only Connection si vous souhaitez qu’Access puisse lire les données mais ne puisse pas les mettre à jour.

Un bouton Test Connection vous permet de vérifier si tout est ok. Si la réponse est Connection successful, c’est terminé ! Sinon, reprenez un par un tous les réglages ci-dessus, et testez systématiquement. Consultez le screencast #1 pour revoir la procédure…

Attention
Une amorce de pilote Oracle est déjà installée par défaut sur votre machine (il s’appelle Microsoft ODBC for Oracle). Ce n’est pas lui qu’il faut utiliser pour votre DSN, mais bien le pilote que vous avez installé plus haut, nommé Oracle in instantclient_xy).

Connexion Access – Oracle

On peut maintenant repasser à Access :

  1. Ouvrez ou créez une base de données MDB.
  2. Cliquez sur le menu Fichier / Données externes / Lier les tables (un clic droit dans la fenêtre Base de données marche aussi).
  3. Dans la zone Types de fichiers, choisissez ODBC Databases.
  4. Une boîte vous demande de sélectionner votre source de données : reprenez le fameux DSN créé plus haut (OracleVid), qui devrait se trouver sous l’onglet Sources de données machine.
  5. Après avoir tapé votre mot de passe, vous obtenez la liste des tables disponibles sous Oracle. Sélectionnez celles qui vous conviennent, elles apparaîtront sous forme de tables liées dans votre base Access.

Une fois tout ceci en place, les tables liées sont vues par Access comme si elles faisaient partie du fichier MDB. Ca veut dire que vous pouvez maintenant construire des requêtes à partir de ces tables liées, des formula
ires, des états. Vous pouvez également ajouter du code VBA pour les manipuler. Consultez le screencast #2 pour revoir la liaison des tables Oracle.

Conseil
Vous pouvez renommer les tables liées pour alléger leur gestion dans Access. Il s’agit seulement de liens vers les tables Oracle, vous ne changerez pas pour autant le nom des tables sur le serveur Oracle !
Attention
Pour que la liaison de tables se passe correctement, vos tables Oracle doivent systématiquement avoir une clef primaire. Attention également aux types de données « longs », dans Oracle : BLOB, CLOB, NLOB ; le driver ODBC peut ne pas les gérer (dans ce cas, le symptôme est une erreur à l’ouverture de la table liée, dans Access, et une apparition d’erreurs dans chaque champ de table).

Vous aimerez aussi...

12 réponses

  1. bernard dit :

    Encore un tuto au top comme bcp sur votre site !!
    merci pour tous vos partages 🙂

  2. Hervé Inisan dit :

    mina > Je ne vois pas toute la question, notamment en ce qui concerne le client. Il s’agit d’un client Access installé sur un « poste client », c’est bien ça ?

    Si oui, c’est indépendant du serveur. Si celui-ci autorise un accès, et que tu as le droit d’installer des applications sur le poste client, on doit pouvoir connecter le client au serveur, même sans accès au serveur. Mais, encore une fois, il faut avoir un accès autorisé (un identifiant / mot de passe) au serveur.

    Ou bien…?

  3. mina dit :

    Bonjour

    Une question un peu bête : si je n’ai pas le droit de toucher au serveur de la base de données (c’est a dire je n’ai pas le droit d’installer d’autre client ou autre chose), est il possible de faire l’installation du client sur un autre ordinateur qui se trouve dans le même réseau local ??

    Merci pour votre réponse

  4. pyloupylou dit :

    Hello,
    Pour indiquer et stocker le mot de passe il suffit de renseigner soit:
    en cas d’utilisation de fichier dsn, dans le DSN creer une ligne avec PWD=mot de passe,
    soit en cas d’utilisation de source utilisateur, creer une entree PWD avec comme valeur le mot de passe au niveau de la clef de registre de la ruche HKEY_CURRENT_USER/software/odbc/obdc.ini/<nom de la source utilisateur odbc>

    et hop

  5. Hervé Inisan dit :

    Kornet > Ce chemin serait celui du pilote Oracle (le dossier qui contient odbc_install.exe). Dans mon exemple : C:OracleODBC.

  6. Kornet dit :

    Bonjour,

    Question de novice…

    Je ne sais pas quoi mettre comme valeur dans la variable d’environnement TNS_ADMIN.

    Pourriez vous donner un exemple ? je ne sais pas quel fichier est concerné ni où il se trouve.

    Merci,

    Cordialement,

    Vincent Crispoul

  7. A_PAUL dit :

    Question subsidiaire : à chaque fois que je sollicite les tables Oracle, je suis obligé de (re)saisir le mot de passe, peut-on éviter cela ?

    Dim dbsJet As Database

    Set dbsJet = ….
    Set tdf_Connect = dbsJet.CreateTableDef(« CLIENTS », dbAttachSavePWD)
    dbsJet.TableDefs.Append tdf_Connect

    paul.

  8. Hervé Inisan dit :

    dani > Au cas où, tu es sûr qu’il s’agit de la même base, des mêmes tables, des mêmes vues, etc. ?

  9. dani dit :

    bonjour,
    sur ma machine quand je fais une liaison ODBC dans MS ACESS avec une table sous Oracle, les valeurs affichées dans ACCESS ne sont pas les même affichées dans oracle discovrer.

    je m’explique les chiffres dans oracle discovrer sont mise à jour (les données de la veille) alors que l’image sous access me donne des valeurs très antérieures.

  10. Hervé Inisan dit :

    HUSET > Je n’ai pas eu le temps de faire des tests détaillés, du coup pas de piste sérieuse pour l’instant. Mais je ne sais pas si c’est un vrai problème, pour 2 raisons :

    • Le mot de passe n’est demandé qu’une seule fois par « session Access », donc ce n’est pas trop gênant.
    • Le fait de stocker le mot de passe serait une faiblesse au niveau sécurité.

    Si j’ai une piste malgré tout, je posterai par ici 🙂

  11. HUSET dit :

    Art : Se connecter à des tables Oracle depuis Access

    Bonjour,
    bravo, c’est vraiment bien détaillé.
    Question subsidiaire : à chaque fois que je sollicite les tables Oracle, je suis obligé de (re)saisir le mot de passe, peut-on éviter cela ?

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *