odbc access

Connexion ODBC

par Christophe Le Fustec (http://ledzeppii.developpez.com)

Date de publication : 22/12/2008

Dernière mise à jour : 22/12/2008

Ce tutoriel a pour but de montrer comment établir et utiliser une connexion ODBC avecMS-Access. Les points traités sont la création de sources de données ODBC (DSN), detables liées ODBC, de requêtes SQL-Direct, et l'utilisation de recordsets DAO avec desconnexions ODBC.

http://www.developpez.com

http://www.developpez.net/forums/member.php?u=81594

http://ledzeppii.developpez.com/

Connexion ODBC par Christophe Le Fustec (http://ledzeppii.developpez.com)

- 2 -Copyright © 2008 - Christophe Le Fustec. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu :textes, documents, images, etc sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à 3 ans de prison et jusqu'à 300000 E de dommages et intérêts.

http://ledzeppii.developpez.com/odbc-access/

I - Introduction..............................................................................................................................................................3II - Qu'est-ce qu'ODBC ?.............................................................................................................................................3III - Sources de données ODBC (DSN)...................................................................................................................... 3

III-A - L'administrateur de sources de données.....................................................................................................4III-B - Création d'une source de données..............................................................................................................5III-C - Création d'une source de données en Visual Basic avec DAO.................................................................. 6III-D - Création d'une source de données avec l'API ODBC.................................................................................9III-E - Au final....................................................................................................................................................... 14

IV - Utilisation d'une source de données ODBC pour lier une table.........................................................................14IV-A - Par l'intermédiaire du menu d'Access....................................................................................................... 15IV-B - Par code VBA............................................................................................................................................18

IV-B-1 - Avec TransferDatabase.....................................................................................................................19IV-B-2 - Avec la bibliothèque DAO.................................................................................................................20

V - Modification d'une table liée ODBC.................................................................................................................... 21V-A - Par l'intermédiaire du menu d'Access........................................................................................................ 21V-B - Par code VBA.............................................................................................................................................22

V-B-1 - Avec TransferDatabase......................................................................................................................22V-B-2 - Avec la bibliothèque DAO..................................................................................................................22

VI - Autres utilisations d'une source donnée ODBC................................................................................................. 23VI-A - Requête SQL direct...................................................................................................................................23VI-B - Recordset DAO ODBC.............................................................................................................................. 24VI-C - Recordset ADO......................................................................................................................................... 25

VII - Chaîne de connexion ODBC sans DSN........................................................................................................... 26VIII - Conclusion........................................................................................................................................................ 26IX - Remerciements................................................................................................................................................... 27








I - Introduction

Avec ce tutoriel, j'ai essayé de mettre par écrit mon expérience d'utilisateur/programmeur dans le domaine desconnexions ODBC.Par le biais d' ODBC il est possible à Access d'accéder à des données d'autres sources, telles des SGBDcomme Oracle, MySQL, ou SQL Server, pour les plus connus.On élargit ainsi le champ d'action d'une application développée avec Access en lui permettant d'accéder aux donnéesd'un serveur de base de données.Il suffit pour cela de disposer du pilote ODBC adéquat.

II - Qu'est-ce qu'ODBC ?

ODBC (Open DataBase Connectivity) est une interface de programmation (API) universelle, indépendante de lasource de données. Le développeur utilise les mêmes fonctions de l'API ODBC quelle que soit la source de donnéeciblée. Le pilote se charge de mener à bien l'action demandée, en fonction du type de source de données.

Si on se réfère à l'illustration ci-dessus, Access est l'application utilisant l'API ODBC.En tant qu'utilisateur ou développeur Access, on se contente de désigner la source donnée, et de fournir lesparamètres de connexion propres à celle-ci.Cette tâche peut être encore plus simplifiée en utilisant le gestionnaire de sources de données ODBC de Windows.L'ensemble des paramètres de connexion se résume à un nom : celui que l'on donne à une source de données (DSNen anglais pour Data Source Name).

III - Sources de données ODBC (DSN)

On peut définir une source de données ODBC comme un jeu de paramètres auquel on donne un nom.





http://dico.developpez.com/html/3049-Generalites-ODBC-Open-DataBase-Connectivity.php

http://dico.developpez.com/html/786-Generalites-SGBD-Systeme-de-Gestion-de-Bases-de-Donnees.php




Ces paramètres (variables en fonction du pilote ODBC) permettent d'établir la connexion avec la base de donnéesciblée.Par la suite, dans des applications compatibles ODBC, on fait référence à ce jeu de paramètres en utilisant son nom,c'est-à-dire le nom de la source de données (data source name, abrégé en DSN).

III-A - L'administrateur de sources de données

C'est un programme utilitaire qui permet de gérer les sources de données ODBC (Création, Modification,Suppression) de l'ordinateur.

On y accède par le Panneau de configuration, dans les Outils d'administration (2000 et XP).

Ou plus simplement en faisant Démarrer -> Exécuter, en tapant odbccp32.cpl, puis en cliquant sur OK.

Il y a principalement deux catégories de sources de données : utilisateur et système.








Les sources de données utilisateur ne sont visibles que par l'utilisateur Windows qui les a créées, tandis que lessources de données systèmes sont visibles pour tous les utilisateurs de l'ordinateur.Les informations sont stockées dans la base de registre :

HKEY_CURRENT_USER\Software\ODBC\ODBC.INI pour les sources utilisateurHKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI pour les sources système

Les sources de données fichier (troisième catégorie), quant à elles, sont enregistrées dans un fichier.

La liste des pilotes ODBC installés se trouve dans l'onglet Pilotes ODBC

III-B - Création d'une source de données

Tout d'abord il faut que le pilote ODBC correspondant au type de données que l'on veut utiliser soit installé sur le PC.

• Sélectionner l'onglet "Source de données utilisateur" ou l'onglet "Source de données système" puis cliquersur le bouton Ajouter.

• Sélectionner le pilote ODBC souhaité, puis cliquer sur Terminer.La boîte de dialogue de configuration de la source de données dépend du pilote choisi.Voici un exemple avec MyODBC (pilote pour MySQL) :








Voici un autre exemple avec SQL Server :

• Entrer le nom de la source de données. On peut mettre ce que l'on veut sauf ces caractères interdits : [ ] { }( ) , ; ? * = ! @ \

• Le champ Description est un commentaire.• Les autres paramètres dépendent du type de données.

Pour accéder à un serveur de base de données par exemple, il faudra fournir le nom du serveur ou sonadresse IP, le nom de la base de données, le nom de l'utilisateur et son mot de passe.

III-C - Création d'une source de données en Visual Basic avec DAO

Grâce à la bibliothèque DAO, il est possible de définir une source de données utilisateur avec du code Visual Basic.On utilise pour cela la méthode RegisterDatabase de la bibliothèque.

Vérifiez qu'il existe bien une référence à la bibliothèque DAO.Dans l'éditeur Visual Basic, allez dans Outils->Références.Puis, si ce n'est déjà fait, cochez Microsoft DAO x.x Object Library, où x.x est le numéro de version.








Syntaxe de RegisterDatabase

DBEngine.RegisterDatabase dbname, driver, silent, attributes

argumentsdbname Nom de la source de données ODBC (DSN) à

créer ou modifierdriver Nom du pilote ODBC tel qu'il apparaît l'onglet

"pilotes ODBC" de l'Administrateur de sources dedonnées ODBC

silent Valeur True/False pour indiquer si oui ou nonon souhaite masquer la boîte de dialogue deconfiguration de la source de données du piloteODBC.

attributes Liste d'attributs sous la forme Mot-clé=Valeur(*)

séparés par des retours chariot (vbCr). Les mots-clés sont ceux écrits dans la base de registre. Ilsvarient en fonction du pilote ODBC.

(*) Les caractères [ ] { } ( ) , ; ? * = ! @ sont interdits. Si une valeur à passer en attribut contient l'un de ces caractères,il faut la mettre entre accolades { }.

Voici une liste des mots-clés les plus courants :

mot-clé SignificationSERVER Nom du serveurDATABASE Nom de la base de donnéesUID Nom de l'utilisateurPWD Mot de passe de l'utilisateur








Ce tableau récapitule les attributs utilisés dans les exemples, ainsi que les valeurs associées :

MySQL SQL Server OracleSERVER LZ2 LZ2 TESTSVRDATABASE test EMM UID Utilisateur UtilisateurPWD MotDePasse MotDePasse

Exemple pour MySQL

Sub RegDB_mysql()Dim strDSN As String, strAttr As String, strODBCDrv As String

' Nom de la source de données ODBCstrDSN = "ESSAI MySQL"' Nom du pilote ODBCstrODBCDrv = "MySQL ODBC 3.51 Driver"' Attributs' OPTION est spécifique à MySQL dans cet exemplestrAttr = "SERVER=LZ2" & vbCr & "DATABASE=test" & vbCr & "OPTION=3" & vbCr & _ "UID=Utilisateur" & vbCr & "PWD=MotDePasse"

DBEngine.RegisterDatabase strDSN, strODBCDrv, True, strAttr

End Sub

Exemple pour SQL Server

Selon la version et le type d'installation de SQL Server, le nom du serveur pourra prendre deux formes : NomServeurtout seul ou NomServeur\NomInstance.J'utilise la forme sans le nom de l'instance.

Le contrôle d'accès au serveur met en oeuvre deux méthodes d'authentification : SQL Server ou Windows.Avec l'authentification SQL Server on utilise les mots-clés UID et PWD pour établir une connexion, tandis qu'avecl'authentification Windows (Compte utilisateur Windows) on utilise le mot-clé Trusted_Connection auquel on affectela valeur yes (UID et PWD ne sont pas utilisés).J'utilise l'authentification SQL Server.Il y a cependant une particularité avec le pilote ODBC pour SQL Server. Lorsque l'on crée une source de données, iln'enregistre pas le nom d'utilisateur et le mot de passe. Cela génère même une erreur, si la chaîne d'attributs contientUID et PWD.

Sub RegDB_sqlsvr()Dim strDSN As String, strAttr As String, strODBCDrv As String

' Nom de la source de données ODBCstrDSN = "Essai SQLSVR"' Nom du pilote ODBCstrODBCDrv = "SQL Server"' Attributs' UID et PWD ne sont pas acceptés par le pilote SQL ServerstrAttr = "SERVER=LZ2" & vbCr & "DATABASE=EMM" ' Si Serveur SQL avec authentification Windows,' activer la ligne ci-dessous :'strAttr = strAttr & vbCr & "Trusted_Connection=Yes"

DBEngine.RegisterDatabase strDSN, strODBCDrv, True, strAttr

End Sub








Exemple pour SQL Oracle

Le mot-clé SERVER correspond au nom d'un service réseau Oracle, également appelé alias. Celui-ci intègre lesinformations concernant le serveur Oracle et la base de données.Pour cette raison, le mot-clé DATABASE n'est pas employé avec le pilote ODBC de Microsoft pour Oracle.Dans cet exemple, TESTSVR est un alias déclaré dans le fichier tnsnames.ora (C:\oracle\ora81\network\ADMIN si"Oracle Client" a été installé dans C:\oracle\ora81)

TESTSVR = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (COMMUNITY = tcp.world)(PROTOCOL = TCP)(Host = 172.111.111.111)(Port = 1234)) ) (CONNECT_DATA = (SID = PEE)) )

Dans ce fichier tnsnames.ora, le nom du serveur (ou son adresse IP) est identifié par le mot-clé Host. La base dedonnée est identifiée par SID.

Pour revenir à DAO, voici un exemple de création de DSN Oracle :

Sub RegDSN_ora()Dim strDSN As String, strDriver As String, strAttr As String

' Nom de la source de données ODBCstrDSN = "ESSAI Oracle"' Nom du pilote ODBCstrDriver = "Microsoft ODBC for Oracle"' Attributs' SynonymColumns est spécifique à ce pilotestrAttr = "UID=Utilisateur" & vbCr & "PWD=MotDePasse"strAttr = strAttr & vbCr & "SERVER=TESTSVR"strAttr = strAttr & vbCr & "SynonymColumns=0"

DBEngine.RegisterDatabase strDSN, strDriver, True, strAttr

End Sub

Les pilotes pour MySQL et Oracle inscrivent dans la base de registre le nom de l'utilisateuret le mot de passe. Ce n'est pas terrible d'un point de vue sécurité.Il vaut mieux se contenter de fournir les informations relatives au serveur, à la base dedonnées, plus d'éventuels paramètres optionnels spécifiques au pilote.Le nom d'utilisateur et le mot de passe pourront être fournis ultérieurement lors del'utilisation de la source de données ODBC.

III-D - Création d'une source de données avec l'API ODBC

Il existe une autre méthode que DAO, pour créer une source de données avec du code Visual Basic. Celle-ci fait appelà l'API ODBC. Elle offre, en outre, l'avantage de pouvoir créer les deux types de sources de données : utilisateuret système.Pour pouvoir utiliser l'API ODBC, il faut au préalable déclarer quelques constantes et fonctions de la bibliothèquedynamique odbccp32.dllCela se fait en mettant du code dans la section Déclarations d'un module de code.Cette section se situe entre le début du module et la première procédure (Sub) ou la première fonction (Function).Illustration :








Voici le code pour déclarer les constantes et les fonctions de l'API ODBC dont nous aurons besoin :

Déclarations

Public Const ODBC_ADD_DSN As Long = 1 ' Ajoute DSN utilisateurPublic Const ODBC_ADD_SYS_DSN As Long = 4 ' Ajoute DSN système

Public Const ODBC_REMOVE_DSN As Long = 3 ' Supprime DSN utilisateurPublic Const ODBC_REMOVE_SYS_DSN As Long = 6 ' Supprime DSN système

Public Declare Function SQLConfigDataSource Lib "odbccp32.dll" _ (ByVal hWndParent As Long, ByVal fRequest As Long, _ ByVal lpszDriver As String, ByVal lpszAttributes As String) As Long

Public Const SQL_SUCCESS As Long = 0Public Const SQL_SUCCESS_WITH_INFO As Long = 1

Public Declare Function SQLInstallerError Lib "odbccp32.dll" _ (ByVal iError As Integer, _ ByRef pfErrorCode As Long, _ ByVal lpszErrorMsg As String, _ ByVal cbErrorMsgMax As Long, _ ByRef pcbErrorMsg As Long) As Long

Avec la fonction SQLConfigDataSource, on va pouvoir créer ou supprimer une source de données ODBC (DSN).

arguments de SQLConfigDataSourcehWndParent Handle de fenêtre Windows ou 0 (zéro).

Par exemple Application.hWndAccessApp ouMe.Hwnd depuis un formulaire

fRequest ODBC_ADD_DSN pour ajouter un DSN utilisateurODBC_ADD_SYS_DSN pour ajouter un DSNsystèmeODBC_REMOVE_DSN pour supprimer un DSNutilisateur








ODBC_REMOVE_SYS_DSN pour supprimer unDSN système

lpszDriver Nom du pilote ODBClpszAttributes Liste de paramètres mot clé=valeur(*). Chaque

paire mot clé/valeur est terminée par un caractèrenul et la liste elle-même est terminée par uncaractère nul.On a donc en bout de chaîne deux caractèresnuls.

(*) Les caractères [ ] { } ( ) , ; ? * = ! @ sont interdits. Si une valeur à passer en attribut contient l'un de ces caractères,il faut la mettre entre accolades { }.

La fonction SQLInstallerError permet de lire d'éventuelles erreurs consécutives à l'exécution deSQLConfigDataSource.

Les attributs sont les mêmes qu'avec DAO, à la différence près que le nom de la source de données (DSN) fait partiede la liste des attributs.

mot-clé SignificationDSN Nom de la source de donnéesSERVER Nom du serveurDATABASE Nom de la base de donnéesUID Nom de l'utilisateurPWD Mot de passe de l'utilisateur

Attributs utilisés dans les exemples qui suivent :

MySQL Sql Server OracleDSN ESSAI MySQL ESSAI SQLSVR ESSAI OracleSERVER LZ2 LZ2 TESTSVRDATABASE test EMM UID Utilisateur UtilisateurPWD MotDePasse MotDePasse

Exemple d'utilisation avec MySQL

Sub apiRegDSN_mysql1()Dim strDriver As String, strAttr As String, retVal As LongDim pfErrorCode As Long, pcbErrorMsg As Long, lpszErrorMsg As String, i As Integer

' Nom du pilote ODBCstrDriver = "MySQL ODBC 3.51 Driver" & vbNullChar

' Minimum fonctionnel requisstrAttr = "DSN=" & "ESSAI MySQL" & vbNullChar

' facultatifstrAttr = strAttr & "SERVER=LZ2" & vbNullCharstrAttr = strAttr & "DATABASE=test" & vbNullCharstrAttr = strAttr & "Description=ESSAI DSN MySQL" & vbNullCharstrAttr = strAttr & "OPTION=3" & vbNullCharstrAttr = strAttr & "UID=Utilisateur" & vbNullChar

' TerminaisonstrAttr = strAttr & vbNullChar

retVal = SQLConfigDataSource(0, ODBC_ADD_SYS_DSN, strDriver, strAttr)








Exemple d'utilisation avec MySQLIf retVal = 0 Then i = 0 Do i = i + 1 lpszErrorMsg = String(2048, vbNullChar) retVal = SQLInstallerError(i, pfErrorCode, lpszErrorMsg, 2047, pcbErrorMsg) If retVal = SQL_SUCCESS_WITH_INFO Then retVal = SQL_SUCCESS If retVal = SQL_SUCCESS Then MsgBox Left(lpszErrorMsg, pcbErrorMsg) Loop Until (retVal <> SQL_SUCCESS Or i = 8)End If

End Sub

Plutôt que d'utiliser directement la fonction SQLConfigDataSource de l'API ODBC, on peut écrire une fonction unpeu à la manière de celle de DAO, pour en faciliter l'utilisation.Nous appellerons cette fonction apiRegDB.

Syntaxe

apiRegDB(strDSN, strDriver, strAttr, lngDSNtype, blnDeleteBeforeCreate) As Boolean

ArgumentsstrDSN Nom de la source de données ODBC (DSN) à

créer ou modifierstrDriver Nom du pilote ODBC tel qu'il apparaît dans l'onglet

"pilotes ODBC" de l'Administrateur de sources dedonnées ODBC

strAttr chaîne d'attributs mot clé=valeur. Chaque pairemot-clé/valeur est terminée par un caractère nul(vbNullChar).On ne met pas le mot clé DSN dans cette chaîned'attributs puisqu'il est déjà transmis dans strDSN

lngDSNtype constante énumérée (voir déclaration plus bas)dsnUser : source de données UtilisateurdsnSystem : source de données Système

blnDeleteBeforeCreate True : supprime la source de données avant de lacréer

Ajouter dans la section Déclarations, cette déclaration du type énuméré dsnTypes :

apiRegDB

Public Enum dsnTypes dsnUser = 0 dsnSystem = 1End Enum

Code de la fonction :

' ---------------------------------------------------------' Function apiRegDB' ---------------------------------------------------------' Entrée:' ======' strDSN .............. : Nom de la source de données à créer' strDriver ........... : Nom du pilote ODBC' strAttributes ....... : Paramètres' lngDSNtype .......... : dsnUser -> crée DSN utilisateur ' dsnSystem -> crée DSN système' blnDeleteBeforeCreate : True -> supprime DSN avant création








'' Retourne:' ========' True si succès, sinon False' ---------------------------------------------------------Function apiRegDB(strDSN As String, strDriver As String, strAttributes As String, _ Optional lngDSNtype As dsnTypes = dsnTypes.dsnUser, _ Optional blnDeleteBeforeCreate As Boolean = False) As BooleanDim retVal As Long, strAttr As String, strErrMsg As StringDim Add_DSN As Long, Rmv_DSN As LongDim pfErrorCode As Long, pcbErrorMsg As Long, lpszErrorMsg As String, i As Integer

If lngDSNtype = dsnSystem Then ' Source de données système Add_DSN = ODBC_ADD_SYS_DSN Rmv_DSN = ODBC_REMOVE_SYS_DSNElse ' Source de données utilisateur Add_DSN = ODBC_ADD_DSN Rmv_DSN = ODBC_REMOVE_DSNEnd If

If blnDeleteBeforeCreate Then ' Supprime source de données si déjà créée strAttr = "DSN=" & strDSN & vbNullChar & vbNullChar retVal = SQLConfigDataSource(0, Rmv_DSN, strDriver, strAttr)End If

' Construit chaîne d'attributs mot-clé=valeur' Chaque paire mot-clé/valeur est terminée par un caractère nulstrAttr = "DSN=" & strDSN & vbNullCharstrAttr = strAttr & strAttributes' Terminaison de la chaînestrAttr = strAttr & vbNullChar

' Crée source de donnéesretVal = SQLConfigDataSource(0, Add_DSN, strDriver, strAttr)

If retVal = 0 Then i = 0 Do i = i + 1 lpszErrorMsg = String(2048, vbNullChar) retVal = SQLInstallerError(i, pfErrorCode, lpszErrorMsg, 2047, pcbErrorMsg) If retVal = SQL_SUCCESS_WITH_INFO Then retVal = SQL_SUCCESS If retVal = SQL_SUCCESS Then If Len(strErrMsg) > 0 Then strErrMsg = strErrMsg & vbCrLf & vbCrLf strErrMsg = strErrMsg & Left(lpszErrorMsg, pcbErrorMsg) End If Loop Until (retVal <> SQL_SUCCESS Or i = 8) MsgBox strErrMsg, , "SQLConfigDataSource" apiRegDB = FalseElse apiRegDB = TrueEnd If

End Function

Voici maintenant des exemples illustrant l'utilisation de la fonction apiRegDB :.

Exemple pour MySQL

Sub apiRegDSN_mysql2()Dim strDSN As String, strAttr As String, strODBCDrv As StringDim blnSuccess As Boolean

' Nom de la source de données ODBCstrDSN = "ESSAI MySQL"' Nom du pilote ODBCstrODBCDrv = "MySQL ODBC 3.51 Driver"








Exemple pour MySQL' AttributsstrAttr = "SERVER=LZ2" & vbNullChar & "DATABASE=test" & vbNullCharstrAttr = strAttr & "Description=ESSAI DSN MySQL" & vbNullCharstrAttr = strAttr & "OPTION=3" & vbNullCharstrAttr = strAttr & "UID=Utilisateur" & vbNullChar

blnSuccess = apiRegDB(strDSN, strODBCDrv, strAttr, dsnSystem, True)

End Sub

Exemple pour SQL Server

Sub apiRegDSN_sqlserver2()Dim strDSN As String, strAttr As String, strODBCDrv As StringDim blnSuccess As Boolean

' Nom de la source de données ODBCstrDSN = "ESSAI SQLSVR"' Nom du pilote ODBCstrODBCDrv = "SQL Server"' AttributsstrAttr = "SERVER=LZ2" & vbNullChar & "DATABASE=EMM" & vbNullChar & _ "Description=ESSAI DSN SQL Server" & vbNullChar


End Sub

Exemple pour Oracle

Sub apiRegDSN_oracle2()Dim strDSN As String, strAttr As String, strODBCDrv As StringDim blnSuccess As Boolean

' Nom de la source de données ODBCstrDSN = "ESSAI Oracle"' Nom du pilote ODBCstrODBCDrv = "Microsoft ODBC for Oracle"' AttributsstrAttr = "SERVER=TESTSVR" & vbNullChar & "UID=Utilisateur" & vbNullCharstrAttr = strAttr & "Description=" & "ESSAI DSN Oracle" & vbNullChar


End Sub

III-E - Au final

En résumé, on peut créer une source de données ODBC :

• Manuellement avec l'administrateur de sources données ODBC• Par code avec DAO, mais la source est de type utilisateur, uniquement visible de celui qui l'a créée.• Par code avec l'API ODBC.

IV - Utilisation d'une source de données ODBC pour lier une table

Le principal intérêt d'ODBC dans Access est de pouvoir lier des tables ou des vues d'un autre système de basede données.Une fois créées, ces tables liées s'utilisent comme des tables Access, avec certaines restrictions en fonction du piloteODBC.

Convention sur le vocabulaire utilisé dans ce tutoriel :





http://dico.developpez.com/html/1436-Langages-vue.php




"Table liée" désigne une table Access qui pointe vers une autre table."Table Source" désigne la table externe, à laquelle une table liée fait référence.Une "Table liée" est donc une sorte de raccourci vers une autre table, "la table source".

Dans l'illustration ci-dessus, la table Access Clients est une table liée ODBC qui fait référence à la table Clients duSGBD.Lorsqu'on ouvre la table liée, Access va lire les données de la table source au travers d'une liaison ODBC et afficheles enregistrements dans une feuille de données, comme s'il s'agissait d'une table Access locale.La liaison ODBC est totalement transparente.

IV-A - Par l'intermédiaire du menu d'Access

Le procédé pour lier une table ODBC est le même que pour lier une table d'une autre base de données Access, ouencore une feuille Excel.

Sauf indication, le cheminement à travers les menus et les captures d'écran qui suiventcorrespondent à Access 2000~2003

Menu->Fichiers->Données Externes->Lier les tables... :

Choisir type de fichiers "ODBC Databases ()" :








Pour Access 2007 :Données externes->Importer->Plus->Base de données ODBC

Puis, dans la fenêtre qui s'ouvre, sélectionner : Lier à la source de données en créant une tableattachée

Choisir le nom de la source de données :








Si le driver n'a pas suffisamment d'informations pour établir la connexion, il ouvre une boîte de dialogue.Par exemple ici il faut fournir un mot de passe :

Choisir la ou les tables :








Access peut enregistrer le mot de passe de manière à ce qu'il ne vous soit plus demandé par la suite.C'est le seul moment où l'on peut choisir cette option.Si le mot de passe n'est pas enregistré, une boîte de dialogue du pilote ODBC le demandera lorsqu'on voudra accéderaux données de la table liée.Il n'est demandé qu'une fois pour la source de données pendant la durée de la session Access.

Pour chaque table à attacher (lier) n'ayant pas de clé et pas d'index unique, Access demande quels sont les champsà utiliser pour créer une clé (en local dans la base de données Access).

Les données d'une table liée ODBC sans clé ne peuvent pas être modifiées par Access.Autrement dit, une table liée sans clé est une table en lecture seule.A l'inverse, les données d'une table liée ODBC avec clé peuvent être modifiées, sous réserve que l'utilisateur disposede droits suffisants.

Les tables liées ODBC sont représentées dans Access par cet icône ( dans Access 2007).

IV-B - Par code VBA

On peut créer une table liée ODBC avec la méthode TransferDatabase d'Access ou avec la bibliothèque DAO.Dans les deux cas nous aurons besoin d'une chaîne de connexion ODBC.Cette chaîne de connexion commence obligatoirement par "ODBC;" dans Access.Viennent ensuite des paires mot-clé/valeur, séparés par des points-virgules.

ODBC;mot-clé1=valeur1;mot-clé2=valeur2;...;mot-cléi=valeuri

Rappel : si une valeur à passer en attribut contient un de ces caractères interdits [ ] { } ( ) , ; ? * = ! @ , il faut lamettre entre accolades { }Les mots-clés les plus courants sont ceux que nous avons déjà vus précédemment :








mot-clé SignificationDSN Nom de la source de donnéesSERVER Nom du serveurDATABASE Nom de la base de donnéesUID Nom de l'utilisateurPWD Mot de passe de l'utilisateur

Si le nom du serveur et/ou le nom de la base de données sont déjà définis dans la sourcede données (DSN), on peut les omettre

IV-B-1 - Avec TransferDatabase

Syntaxe de la commande TransferDatabase

DoCmd.TransferDatabase typetransfert, typebase, nombase, typeobjet, source, destination, structureseulement, enregcodeconnexion

typetransfert acLink pour créer un lientypebase "Base de données ODBC"nombase chaîne de connexion ODBC "ODBC;..."typeobjet acTablesource Nom de la table dans la base de données ODBCdestination Nom de la table dans Accessstructureseulement Falseenregcodeconnexion True : enregistre le nom d'utilisateur et le mot de

passe pour la connexion ODBC

ExempleOn lie la table MySQL fournisseurs2 et elle s'appellera mysql_fournisseurs2 dans Access.Par conséquent

• table liée = mysql_fournisseurs2• table source = fournisseurs2

On suppose que la source de donnée "ESSAI MySQL" contient déjà les définitions du serveur et de la base dedonnées.Il est donc inutile de les mettre dans la chaîne de connexion.

Exemple TransferDatabase

Dim strConn As String

strConn = "ODBC;" & _ "DSN=ESSAI MySQL;" & _ "UID=Utilisateur;PWD=MotDePasse" DoCmd.TransferDatabase acLink, "Base de données ODBC", strConn, _ acTable, "fournisseurs2", "mysql_fournisseurs2", , True

La table fournisseurs2 n'ayant ni clé ni index unique, Access ouvre la boîte de dialogue invitant l'utilisateur àsélectionner un ou plusieurs champs de manière à créer un identifiant unique d'enregistrement.








Si on n'indique pas d'identifiant unique, la table sera enlecture seule.

IV-B-2 - Avec la bibliothèque DAO

Principe :On crée une nouvelle définition de table (objet TableDef).Le nom qu'on lui donne sera celui de la table liée dans Access.On renseigne les propriétés Connect et SourceTableName de l'objet TableDef.

Connect chaîne de connexion ODBC "ODBC;..."(la même que celle utilisée avecTransferDatabase)

SourceTableName Nom de la table dans la base de donnéesODBC

Si on veut qu'Access sauvegarde le nom d'utilisateur et le mot de passe on ajoute l'attributdbAttachSavePWD à la propriété Attributes de l'objet TableDef.

Enfin, on ajoute la nouvelle définition de table à la collection TableDefs de la base de données Access :

Exemple DAO

Dim strConn As String, db As DAO.Database, td As DAO.TableDef

Set db = CurrentDb' Chaîne de connexionstrConn = "ODBC;" & _ "DSN=ESSAI MySQL;" & _ "UID=Utilisateur;PWD=MotDePasse"' Création nouvelle définition de tableSet td = db.CreateTableDef("mysql_fournisseurs2")' Optionnel : Sauver mot de passetd.Attributes = td.Attributes Or dbAttachSavePWD' Chaîne de connexion ODBC pour DAOtd.Connect = strConn' Nom de la table sourcetd.SourceTableName = "fournisseurs2"' Ajouter à la collectiondb.TableDefs.Append td

Set td = Nothingset db=Nothing' Actualiser fenêtre base de données pour que la nouvelle table apparaisseApplication.RefreshDatabaseWindow

Contrairement à TransferDatabase, la méthode DAO ne demande pas à l'utilisateur de sélectionner le ou les champsformant un identificateur unique d'enregistrement.








Il faut donc ajouter un index par code.Ce n'est pas possible avec DAO : on ne peut pas ajouter d'index à une définition de table liée.On va donc le faire avec une instruction SQL de création d'index.

CREATE UNIQUE INDEX nom_indexON nom_table (champ1 ASC [,champ2 ASC,..., champi ASC]) WITH PRIMARY

Voir CREATE INDEX sur office.microsoft.comEn VBA, pour l'exemple précédent, cela donne ceci :

Dim db As DAO.Database, strIndex As StringSet db = CurrentDb

' Ajout index clé primairestrIndex = "CREATE UNIQUE INDEX __uniqueindex " & _ "ON mysql_fournisseurs2 ([N° fournisseur] ASC) WITH PRIMARY"db.Execute strIndex, dbFailOnError

Si la table de la base de données ODBC possède une clé ou un index unique il n'y a (saufexception) pas de problème, car Access et DAO créent automatiquement la clé en localdans la base Access.

V - Modification d'une table liée ODBC

Dans le chapitre précédent nous avons vu comment créer une table liée ODBC, soit à l'aide du menu Access, soitpar code Visual Basic.Nous allons maintenant voir comment modifier une table liée (les caractéristiques du lien).Cela peut s'avérer utilise s'il faut modifier un des attributs de la chaîne de connexion.

V-A - Par l'intermédiaire du menu d'Access

Comme avec une table liée Access, on utilise le gestionnaire de tables liées.Outils > Utilitaires de base de données > Gestionnaire de tables liées :

Si on ne coche pas "Toujours demander un nouvel emplacement", la table liée est recrée sans poser de question(si le DSN est toujours valide).Dans le cas contraire (DSN non valide), la boîte de dialogue du pilote ODBC est ouverte.Si on ferme cette boîte de dialogue sans qu'une connexion puisse être établie, Access nous invite à sélectionnerune source de données ODBC.





http://office.microsoft.com/fr-fr/access/HA012314391036.aspx




Si on coche "Toujours demander un nouvel emplacement", Access nous invite à re-sélectionner une source dedonnées ODBC.La table liée est recréée.

Dans les deux cas on ne peut pas changer le nom de la table source, c'est à dire celle se trouvant dans la base dedonnées référencée par le DSN.

V-B - Par code VBA

Nous retrouvons les deux techniques VBA déjà utilisée, TransferDatabase et DAO.

V-B-1 - Avec TransferDatabase

TransferDatabase ne permet pas de modifier directement une table liée ; il faut donc la détruire, puis la recréer.

V-B-2 - Avec la bibliothèque DAO

Avec DAO, on peut modifier la propriété Connect de l'objet TableDef, mais pas la propriété SourceTable.Par exemple, la table liée mysql_fournisseurs2 créée un peu plus tôt a pour table source la table fournisseurs2Si on veut changer le nom de la table source il faudra détruire la table liée mysql_fournisseurs2 et la recréer.En revanche, on peut modifier ce que l'on veut dans la propriété Connect.Le changement prend effet après appel de méthode RefreshLink de l'objet TableDef.Cette méthode recrée la table liée si bien que l'on perd l'index unique si on en avait créé un.Il faut penser à le recréer si cela se produit.Dans cet exemple, on va changer le nom d'utilisateur (UID) et le mot de passe (PWD).

Sub ChgODBClink_DAO2()Dim strConn As String, db As DAO.Database, td As DAO.TableDefDim strParts() As String, i As IntegerDim strTable As String, strUID As String, strPWD As StringDim errX As DAO.Error, strErrMsg As StringDim strCreateIdx As String, strUniqueRecord As String

strTable = "mysql_fournisseurs2"strUniqueRecord = "[N° fournisseur] ASC"strUID = "NvUtilisateur"strPWD = "NvMotDePasse"

Set db = CurrentDbOn Error Resume NextSet td = db.TableDefs(strTable)On Error GoTo 0If td Is Nothing Then strErrMsg = "La Table '" & strTable & "' est introuvable" GoTo QUITEnd If

' Si la table est bien une table liée ODBCIf td.Attributes And dbAttachedODBC <> 0 Then ' Crée un tableau des éléments de la chaîne de connexion ODBC actuelle strParts = Split(td.Connect, ";") ' recompose la chaîne de connexion ODBC For i = LBound(strParts) To UBound(strParts) If strParts(i) Like "UID=*" Then ' nouvel attribut UID strConn = strConn & "UID=" & strUID & ";"

ElseIf strParts(i) Like "PWD=*" Then ' Nouvel attribut PWD strConn = strConn & "PWD=" & strPWD & ";"








Else strConn = strConn & strParts(i) & ";" End If Next strConn = Left(strConn, Len(strConn) - 1) ' Modifie la propriété Connect avec la nouvelle chaîne de connexion ODBC td.Connect = strConn ' Met à jour la liaison On Error GoTo ERRH td.RefreshLink ' RefreshLink recrée la table liée. ' ==> on perd l'index unique si on en avait créé un. ' Ajout index clé primaire If Len(strUniqueRecord) > 0 Then strCreateIdx = "CREATE UNIQUE INDEX __uniqueindex " & _ "ON [" & strTable & "] (" & strUniqueRecord & ") WITH PRIMARY" db.Execute strCreateIdx, dbFailOnError End If

Else strErrMsg = "La Table '" & strTable & "' n'est pas une table liée ODBC"End If

QUIT:Set td = NothingSet db = NothingIf Len(strErrMsg) <> 0 Then MsgBox strErrMsgExit Sub

ERRH:strErrMsg = "Erreur N° " & CStr(Err.Number) & " : " & Err.DescriptionSelect Case Err.Number Case 3146, 3151, 3154, 3155, 3156, 3157, 3231, 3232, 3234, 3225, 3238, 3247, 3254 strErrMsg = strErrMsg & vbCrLf & vbCrLf & _ ">>> Erreurs complémentaires DAO :" & vbCrLf & _ "======================" 'Récupérations Erreur(s) driver ODBC via DAO For Each errX In DBEngine.Errors strErrMsg = strErrMsg & vbCrLf & Format(errX.Number, "00000") & " : " & errX.Description NextEnd SelectResume QUITEnd Sub

VI - Autres utilisations d'une source donnée ODBC

Les tables liées ne sont pas la seule façon d'exploiter une source ODBC. Elle permet également de faire du SQLdirect, ou bien de générer des recordset DAO ou ADO.

VI-A - Requête SQL direct

Une requête "SQL direct" permet d'exécuter une instruction SQL directement depuis le serveur de base de données(SQL Server, MySQL, Oracle, ...).La syntaxe SQL est celle du SGBD (SQL Server, MySQL, Oracle, ...).Pour créer une requête SQL Direct, on procède comme pour créer une requête normale, sauf qu'on n'ajoute aucunetable.On ferme la boîte de dialogue "Ajouter une table" et on fait :Menu principal > Requête > Spécifique SQL > SQL DirectouClic-droit dans la moitié supérieure de la requête > Spécifique SQL > SQL DirectEnsuite, on définit la chaîne de connexion ODBC. Elle se trouve dans les propriétés de la requête.C'est la même que celle utilisée pour lier des tables.Exemple








"ODBC;DSN=ESSAI MySQL;UID=Utilisateur;PWD=MotDePasse"

Ce type de requête ne dispose d'aucun moyen de conception visuelle.On entre juste le texte de l'instruction SQL.

Les données renvoyées sont en lecture seule.

VI-B - Recordset DAO ODBC

En plus des espaces de travail Microsoft Jet, DAO propose des espaces de travail ODBC.Contrairement à Jet, il n'y a pas d'espace de travail par défaut ODBC.On commence donc par créer un espace de travail ODBC.

• Dans cet espace de travail ODBC, on ouvre une connexion vers la source de données.Là encore il faut une chaîne de connexion ODBC (même syntaxe que celle utilisée pour lier une table ODBC)

• A partir de la connexion, on ouvre un recordset.

Ci-dessous, un exemple de code avec un DSN SQL Server :

Exemple recordset DAO ODBC

Sub odbcDAOconn_SqlServer1()Dim Wksp As DAO.Workspace, Conn As DAO.Connection, strODBCconn As StringDim rs As DAO.Recordset, strSQL As String, lgIdFournisseur As LongDim strUID As String, strPWD As StringDim errX As DAO.Error, strErrMsg As String

strUID = "Utilisateur"strPWD = "MotDePasse"

On Error GoTo ERRH

' Créer un nouveau workspace de type ODBCSet Wksp = DBEngine.CreateWorkspace("MonWkspODBC", "Admin", "", dbUseODBC)' Chaîne de connexion ODBC' - on ne précise pas le nom du serveur (SERVER=NomServeur) car il est' déjà enregistré dans la source de données ESSAI SQLSVR (DSN)' - on précise le nom de la base de données, ' l'utilisateur et le mot de passestrODBCconn = "ODBC;" & _ "DSN=ESSAI SQLSVR;" & _ "DATABASE=EMM;" & _ "UID=" & strUID & ";PWD=" & strPWD' Ouverture connexion ODBCSet Conn = Wksp.OpenConnection("sqlserver", dbDriverNoPrompt, False, strODBCconn)

' Ouverture d'un recordsetstrSQL = "SELECT * FROM fournisseurs"' - sans l'option dbExecDirect le recordset est en lecture seule








Exemple recordset DAO ODBC' - avec MySQL on est toujours en lecture seule (pilote 3.51.23)Set rs = Conn.Database.OpenRecordset(strSQL, dbOpenDynaset, dbExecDirect, dbPessimistic)If Not rs.EOF Then Debug.Print rs.Fields(0), rs.Fields(1)End If

' ajoute un enregistrementIf rs.Updatable Then rs.AddNew rs("Société") = "Nouveau fournisseur" rs.Update rs.MoveLast lgIdFournisseur = rs("N° fournisseur")End If

QUIT:If Not rs Is Nothing Then rs.CloseIf Not Conn Is Nothing Then Conn.CloseIf Not Wksp Is Nothing Then Wksp.CloseExit Sub

ERRH:

strErrMsg = "Erreur N° " & CStr(Err.Number) & " : " & Err.DescriptionSelect Case Err.Number ' principaux codes d'erreurs impliquant ODBC Case 3146, 3151, 3154, 3155, 3156, 3157, 3231, 3232, 3234, 3225, 3238, 3247, 3254 strErrMsg = strErrMsg & vbCrLf & vbCrLf & _ ">>> Erreurs complémentaires DAO :" & vbCrLf & _ "======================" 'Récupérations Erreur(s) driver ODBC via DAO For Each errX In DBEngine.Errors strErrMsg = strErrMsg & vbCrLf & Format(errX.Number, "00000") & " : " & errX.Description NextEnd Select

MsgBox strErrMsgResume QUIT

End Sub

VI-C - Recordset ADO

Avec ADO, on dispose généralement d'un fournisseur de données propre à la base de données à laquelle on seconnecte.SQLOLEDB pour SQL Server, MSDAORA pour oracle, Microsoft.Jet.OLEDB.4.0 pour MS-Access, ...En principe, on utilise le fournisseur de données ODBC (MSDASQL) lorsque l'on n'a pas d'autre choix.Par exemple c'est ce que l'on fera avec une base de données MySQL si on ne dispose que du pilote ODBC

Exemple recordset ADO ODBC

Dim oCnMySQL As ADODB.Connection, rs As ADODB.Recordset

' Connexion à base MySQL' Crée objet ConnectionSet oCnMySQL = New ADODB.Connection' Définit chaîne de connexion' * Le provider MSDASQL (ODBC) étant celui par défaut, on peut l'omettreoCnMySQL.ConnectionString = "Provider=MSDASQL;" & _ "DSN=ESSAI MySQL;" & _ "UID=Utilisateur;PWD=MotDePasse"' Ouvre la connexionoCnMySQL.Open

' Crée recordsetSet rs = New ADODB.Recordset' Ouvrir recordset sur table fournisseurs








Exemple recordset ADO ODBCrs.Open "SELECT * FROM fournisseurs", oCnMySQL, adOpenStatic, adLockOptimistic, adCmdText

Debug.Print "AddNew", rs.Supports(adAddNew)Debug.Print "Update", rs.Supports(adUpdate)Debug.Print "MovePrevious", rs.Supports(adMovePrevious)Debug.Print "Find", rs.Supports(adFind)

' Fermeture recordsetrs.CloseSet rs = Nothing' Fermeture connexionoCnMySQL.CloseSet oCnMySQL = Nothing

VII - Chaîne de connexion ODBC sans DSN

Il est tout à fait possible d'utiliser des chaînes de connexions ODBC sans DSN (Source de données ODBC).Dans ce cas la chaîne de connexion doit incorporer le nom du pilote ODBC à utiliser, à l'aide du mot-clé DRIVER.Par ailleurs, on ne peut plus omettre les éléments SERVER et DATABASE comme on pouvait le faire en utilisantun DSN.Le format de la chaîne de connexion ODBC ne change pas

ODBC;mot-clé1=valeur1;mot-clé2=valeur2;...;mot-cléi=valeuri

Rappel : si une valeur à passer en attribut contient un de ces caractères interdits [ ] { } ( ) , ; ? * = ! @ , il faut lamettre entre accolades { }.Les mots-clés les plus courants :

mots-clés SignificationDRIVER Nom du pilote ODBCSERVER Nom du serveurDATABASE Nom de la base de donnéesUID Nom de l'utilisateurPWD Mot de passe de l'utilisateur

Voici quelques exemples de chaînes de connexions ODBC :

Chaîne de connexion ODBC pour MySQL

ODBC;DRIVER=MySQL ODBC 3.51 Driver;SERVER=LZ2;DATABASE=test;UID=Utilisateur;PWD=MotDePasse;OPTION=3

Chaîne de connexion ODBC pour SQL Server

ODBC;DRIVER=SQL Server;SERVER=LZ2;DATABASE=EMM;UID=Utilisateur;PWD=MotDePasse

Chaîne de connexion ODBC pour Oracle

ODBC;DRIVER=Microsoft ODBC for Oracle;SERVER=TESTSVR;UID=Utilisateur;PWD=MotDePasse

VIII - Conclusion

J'espère que, grâce à ce tutoriel, vous pourrez tirer profit des liaisons ODBC et qu'il vous aidera à les mettre enapplication.Bien qu'ODBC ne soit plus au goût du jour (il y a maintenant OLE DB, ADO), cela reste encore un moyen simple etpratique de se connecter à d'autres systèmes de gestion de base de données.C'est particulièrement vrai en ce qui concerne les tables liées.








Pensez aux requêtes SQL Direct. Si l'on a besoin uniquement de lire/extraire des données d'un SGBD, les requêtesSQL Direct permettent de faire exécuter les instructions SQL par le serveur de base de données. Cela décharge leclient (PC sur lequel Access s'exécute) et le réseau (moindre trafic).Même remarque concernant les espaces de travail ODBC de DAO.Pour des connexions purement logicielles (recordsets) j'opterai plutôt pour ADO et un fournisseur de donnéesapproprié au SGBD, de manière à bénéficier de plus de fonctionnalités.Toutefois, pour certains SGBD, ODBC est la seule alternative possible (ou tout du moins la seule gratuite).

Liens• Microsoft ODBC : L'API Microsoft ODBC• connectionstrings.com : Nombreux exemples de chaînes de connexions

IX - Remerciements

Dolphy35 pour son aide dans mes démarches.Heureux-oli pour sa relecture.Antoun pour sa relecture experte, et son aide rédactionnelle.L'équipe MS-Office de developpez.com pour l'intérêt manifesté à l'égard de mon tuto.Nono40 pour son outil de création d'article et developpez.com pour leur hébergement.





http://msdn.microsoft.com/en-us/library/ms710252(VS.85).aspx

http://www.connectionstrings.com/

http://dolphy35.developpez.com/

http://heureuxoli.developpez.com/

http://antoun.developpez.com/

odbc access

Documents