Connexion ODBC

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 les sources de données systèmes sont visibles pour tous les utilisateurs de l'ordinateur.
Les informations sont stockées dans la base de registre :

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

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 cliquer sur 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 son adresse IP, le nom de la base de données, le nom de l'utilisateur et son mot de passe.

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.

DBEngine.RegisterDatabase dbname, driver, silent, attributes

^(*) 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 :

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

Exemple pour MySQL

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

' Nom de la source de données ODBC
strDSN = "ESSAI MySQL"
' Nom du pilote ODBC
strODBCDrv = "MySQL ODBC 3.51 Driver"
' Attributs
' OPTION est spécifique à MySQL dans cet exemple
strAttr = "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 : NomServeur tout 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'avec l'authentification Windows (Compte utilisateur Windows) on utilise le mot-clé Trusted_Connection auquel on affecte la 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, il n'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 contient UID et PWD.

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

' Nom de la source de données ODBC
strDSN = "Essai SQLSVR"
' Nom du pilote ODBC
strODBCDrv = "SQL Server"
' Attributs
' UID et PWD ne sont pas acceptés par le pilote SQL Server
strAttr = "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 les informations 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 de donné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 ODBC
strDSN = "ESSAI Oracle"
' Nom du pilote ODBC
strDriver = "Microsoft ODBC for Oracle"
' Attributs
' SynonymColumns est spécifique à ce pilote
strAttr = "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'utilisateur et 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 de donné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 de l'utilisation de la source de données 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 : utilisateur et système.
Pour pouvoir utiliser l'API ODBC, il faut au préalable déclarer quelques constantes et fonctions de la bibliothèque dynamique odbccp32.dll
Cela 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 :

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

Public Const ODBC_REMOVE_DSN As Long = 3       ' Supprime DSN utilisateur
Public 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 = 0
Public 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).

^(*) 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 de SQLConfigDataSource.

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 partie de la liste des attributs.

Attributs utilisés dans les exemples qui suivent :

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

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

' Minimum fonctionnel requis
strAttr = "DSN=" & "ESSAI MySQL" & vbNullChar

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

' Terminaison
strAttr = strAttr & vbNullChar

retVal = SQLConfigDataSource(0, ODBC_ADD_SYS_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 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 un peu à la manière de celle de DAO, pour en faciliter l'utilisation.

Nous appellerons cette fonction apiRegDB.

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

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

Public Enum dsnTypes
        dsnUser = 0
        dsnSystem = 1
End 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 Boolean
Dim retVal As Long, strAttr As String, strErrMsg As String
Dim Add_DSN As Long, Rmv_DSN As Long
Dim 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_DSN
Else
   ' Source de données utilisateur
   Add_DSN = ODBC_ADD_DSN
   Rmv_DSN = ODBC_REMOVE_DSN
End 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 nul
strAttr = "DSN=" & strDSN & vbNullChar
strAttr = strAttr & strAttributes
' Terminaison de la chaîne
strAttr = strAttr & vbNullChar

' Crée source de données
retVal = 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 = False
Else
   apiRegDB = True
End If

End Function

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

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

' Nom de la source de données ODBC
strDSN = "ESSAI MySQL"
' Nom du pilote ODBC
strODBCDrv = "MySQL ODBC 3.51 Driver"
' Attributs
strAttr = "SERVER=LZ2" & vbNullChar & "DATABASE=test" & vbNullChar
strAttr = strAttr & "Description=ESSAI DSN MySQL" & vbNullChar
strAttr = strAttr & "OPTION=3" & vbNullChar
strAttr = strAttr & "UID=Utilisateur" & vbNullChar

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

End Sub

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

' Nom de la source de données ODBC
strDSN = "ESSAI SQLSVR"
' Nom du pilote ODBC
strODBCDrv = "SQL Server"
' Attributs
strAttr = "SERVER=LZ2" & vbNullChar & "DATABASE=EMM" & vbNullChar & _
          "Description=ESSAI DSN SQL Server" & vbNullChar

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

End Sub

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

' Nom de la source de données ODBC
strDSN = "ESSAI Oracle"
' Nom du pilote ODBC
strODBCDrv = "Microsoft ODBC for Oracle"
' Attributs
strAttr = "SERVER=TESTSVR" & vbNullChar & "UID=Utilisateur" & vbNullChar
strAttr = strAttr & "Description=" & "ESSAI DSN Oracle" & vbNullChar

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

End Sub

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.

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, ou encore une feuille Excel.

Sauf indication, le cheminement à travers les menus et les captures d'écran qui suivent correspondent à 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 table attaché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éder aux 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 dispose de droits suffisants.

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

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 la mettre entre accolades { }

Les mots-clés les plus courants sont ceux que nous avons déjà vus précédemment :

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

IV-B-1. Avec TransferDatabase▲

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

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

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

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

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

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

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

Dim db As DAO.Database, strIndex As String
Set db = CurrentDb

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

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électionner une source de données ODBC.

Si on coche "Toujours demander un nouvel emplacement", Access nous invite à re-sélectionner une source de donné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 de données référencée par le DSN.

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

V-B-1. Avec TransferDatabase▲

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

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

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

Set db = CurrentDb
On Error Resume Next
Set td = db.TableDefs(strTable)
On Error GoTo 0
If td Is Nothing Then
   strErrMsg = "La Table '" & strTable & "' est introuvable"
   GoTo QUIT
End If

' Si la table est bien une table liée ODBC
If 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 = Nothing
Set db = Nothing
If Len(strErrMsg) <> 0 Then MsgBox strErrMsg
Exit Sub

ERRH:
strErrMsg = "Erreur N° " & CStr(Err.Number) & " : " & Err.Description
Select 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
        Next
End Select
Resume QUIT
End Sub

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 aucune table.
On ferme la boîte de dialogue "Ajouter une table" et on fait :
Menu principal > Requête > Spécifique SQL > SQL Direct
ou
Clic-droit dans la moitié supérieure de la requête > Spécifique SQL > SQL Direct

Ensuite, 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.

En plus des espaces de travail Microsoft Jet, DAO propose (jusqu'à Access 2003) 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.

A partir d'Access 2007, les espaces de travail ODBC ne sont plus supportés dans DAO.
Il est cependant possible d'ouvrir une base de données ODBC, depuis l'espace de travail Microsoft Jet par défaut.
Il suffit pour cela, de fournir une chaîne de connexion ODBC à la méthode OpenDatabase.

Comparaison des deux méthodes :

Ci-dessous, un exemple de code avec un espace de travail ODBC, et un DSN SQL Serveur :

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

strUID = "Utilisateur"
strPWD = "MotDePasse"

On Error GoTo ERRH

' Créer un nouveau workspace de type ODBC
Set 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 passe
strODBCconn = "ODBC;" & _
              "DSN=ESSAI SQLSVR;" & _
              "DATABASE=EMM;" & _
              "UID=" & strUID & ";PWD=" & strPWD
' Ouverture connexion ODBC
Set Conn = Wksp.OpenConnection("sqlserver", dbDriverNoPrompt, False, strODBCconn)

' Ouverture d'un recordset
strSQL = "SELECT * FROM fournisseurs"
' -  sans l'option dbExecDirect le recordset est en lecture seule
' -  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 enregistrement
If rs.Updatable Then
   rs.AddNew
   rs("Société") = "Nouveau fournisseur"
   rs.Update
   rs.MoveLast
   lgIdFournisseur = rs("N° fournisseur")
   Debug.Print lgIdFournisseur, rs("Société")
End If

QUIT:
If Not rs Is Nothing Then rs.Close
If Not Conn Is Nothing Then Conn.Close
If Not Wksp Is Nothing Then Wksp.Close
Exit Sub

ERRH:

strErrMsg = "Erreur N° " & CStr(Err.Number) & " : " & Err.Description
Select 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
        Next
End Select

MsgBox strErrMsg
Resume QUIT

End Sub

Le même exemple avec l'espace de travail Microsoft Jet par défaut :

Sub odbcDAOconn_SqlServer2()
Dim dbodbc As DAO.Database, strODBCconn As String
Dim rs As DAO.Recordset, strSQL As String, lgIdFournisseur As Long
Dim strUID As String, strPWD As String
Dim strErrMsg As String

strUID = "Utilisateur"
strPWD = "MotDePasse"

On Error GoTo ERRH

' Chaîne de connexion ODBC
strODBCconn = "ODBC;" & "DSN=ESSAI SQLSVR;" & "Database=EMM;" & _
              "Uid=" & strUID & ";Pwd=" & strPWD
              
' Ouverture Base ODBC
'   DBEngine(0) équivaut à DBEngine.Workspaces(0)
'   1er argument (dbname) = Nom d'un DSN ou rien
Set dbodbc = DBEngine(0).OpenDatabase("", dbDriverNoPrompt, False, strODBCconn)

' Ouverture d'un recordset
strSQL = "SELECT * FROM fournisseurs"
'
Set rs = dbodbc.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges, dbOptimistic)
If Not rs.EOF Then
   Debug.Print rs.Fields(0), rs.Fields(1)
End If

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

QUIT:
If Not rs Is Nothing Then rs.Close
If Not dbodbc Is Nothing Then dbodbc.Close

Exit Sub

ERRH:
strErrMsg = "Erreur N° " & CStr(Err.Number) & " : " & Err.Description
MsgBox strErrMsg
Resume QUIT

End Sub

Avec ADO, on dispose généralement d'un fournisseur de données propre à la base de données à laquelle on se connecte.
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

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

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

' Crée recordset
Set rs = New ADODB.Recordset
' Ouvrir recordset sur table fournisseurs
rs.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 recordset
rs.Close
Set rs = Nothing
' Fermeture connexion
oCnMySQL.Close
Set oCnMySQL = Nothing