Création objet ODBC en VBA

Version imprimable

Bonjour

J'essaie de créer un objet ODBC via VBA pour m'affranchir complètement du poste sur lequel je me trouve en vue d'exécuter des requêtes directes SQL sur une base distante AS400.
J'ai trouvé quelques tutos ou posts à ce sujet, et j'essaie de faire une synthèse de tout çà en trouvant un code qui marche.

il y a ce tuto dans la FAQ : çà fonctionne pour une base ACCESS, mais pas pour ma base AS400 distante. Voici mon code (la chaîne StrAttributes a été testée avec ce code, et avec les ';' à la place des ' ') :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 Private Sub OBCD() '---- Créer un DSN --------- Dim strDriver As String Dim strAttributes As String Dim intRet As Long strDriver = "Client Access ODBC Driver (32-bit)" & Chr$(0) strAttributes = "DSN=ODBCKPI" & Chr$(0) strAttributes = strAttributes & "DESCRIPTION=Test DSN par VB6" & Chr$(0) strAttributes = strAttributes & "UID=BAUDP System=LV011BK DATABASE=FGE50C3 SIGNON=1 DBQ=FGE50C3 DFTPKGLIB=FGE50C3 PKG=FGE50C3/DEFAULT(IBM),2,0,1,0,512" intRet = SQLConfigDataSource(vbNull, 1, strDriver, strAttributes) If intRet Then MsgBox "DSN Created" Else MsgBox "Create Failed" End If End Sub
D'autre part, pour une base ACCESS, çà me crée un user DSN et je préfèrerait un DSN système (et au passage comment on fait un file DSN ?)

Voici une autre solution (qui marche), qui me crée une connexion, mais pas un DSN :
Code:

1 2 3 4 5 6 7 8 9 Sub essai() Dim db As Database Dim WrkSpaceODBC As DAO.Workspace, ConnectODBC As DAO.Connection, r2 As DAO.Recordset Dim strODBC As String, ErrDesc As String Dim qdf As DAO.QueryDef Set WrkSpaceODBC = DBEngine.CreateWorkspace("ODBC", "Admin", "", dbUseODBC) Set ConnectODBC = WrkSpaceODBC.OpenConnection("", dbDriverNoPrompt, True, "ODBC;DRIVER={Client Access ODBC Driver (32-bit)};UID=BAUDP;System=LV011BK;DATABASE=FGE50C3;SIGNON=1;DBQ=FGE50C3;DFTPKGLIB=FGE50C3;PKG=FGE50C3/DEFAULT(IBM),2,0,1,0,512;") ConnectODBC.QueryTimeout = 900 End Sub
Comment utiliser cette connexion pour paramétrer un objet QueryDef ? L'argument Name doit rester chaîne vide sinon çà plante.
Cette dernière solution est la plus proche de ce que je voudrait faire : la connexion est initialisée, et quand je mets lun paramètre errroné, çà m'ouvre la fenêtre ODBC. Mais comment l'enregistrer en tant que DSN ?

Je sens que çà chauffe, mais j'ai besoin de votre aide pour finaliser complètement ce code.

Merci d'avance

Bonsoir,

Ton premier code (faq) s'appuie sur l'API ODBC.
La faq ne mentionne que les constantes relatives à un DSN utilisateur.
Code:

1 2 3 Private Const ODBC_ADD_DSN = 1 Private Const ODBC_CONFIG_DSN = 2 Private Const ODBC_REMOVE_DSN = 3
Pour un DSN Système :
Code:

1 2 3 Private Const ODBC_ADD_SYS_DSN As Long = 4 ' Ajoute DSN système Private Const ODBC_CONFIG_SYS_DSN As Long = 5 ' Configure DSN système Private Const ODBC_REMOVE_SYS_DSN As Long = 6 ' Supprime DSN système
Code:

intRet = SQLConfigDataSource(vbNull, ODBC_ADD_SYS_DSN, strDriver, strAttributes)

Avec SQLConfigDataSource de l'API ODBC, les attributs sont séparés par le caractère nul (vbNullChar ou Chr(0)).
De plus, la chaîne d'attributs doit se terminer par deux caractères nul.
Code:

1 2 3 4 5 6 7 8 9 10 strAttributes = "DSN=ODBCKPI" & Chr$(0) strAttributes = strAttributes & "DESCRIPTION=Test DSN par VB6" & Chr$(0) strAttributes = strAttributes & "UID=BAUDP" & Chr$(0) strAttributes = strAttributes & "System=LV011BK" & Chr$(0) strAttributes = strAttributes & "DATABASE=FGE50C3" & Chr$(0) strAttributes = strAttributes & "SIGNON=1" & Chr$(0) strAttributes = strAttributes & "DBQ=FGE50C3" & Chr$(0) strAttributes = strAttributes & "DFTPKGLIB=FGE50C3" & Chr$(0) strAttributes = strAttributes & "PKG=FGE50C3/DEFAULT(IBM),2,0,1,0,512" & Chr$(0) strAttributes = strAttributes & Chr$(0)
Citation:

au passage comment on fait un file DSN

Pour créer un fichier DSN il faut utiliser la fonction SQLWriteFileDSN de l'API ODBC.

Voici un exemple de création d'objet requête SQL-Direct en VBA+DAO
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 Dim db As DAO.Database, qd As DAO.QueryDef Dim strNomRequete As String Dim strODBCconn As String, strSQL As String ' Nom de la requête SQL-Direct à créer ou modifier strNomRequete = "mysqlFournisseurs" ' Chaîne de connexion ODBC avec DSN, pour requête SQL-Direct strODBCconn = "ODBC;DSN=Mon DSN MySQL;UID=su;PWD=123456" ' code SQL de la requête strSQL = "select * from fournisseurs" Set db = CurrentDb ' Référence ou crée une requête On Error Resume Next Set qd = db.QueryDefs(strNomRequete) On Error GoTo 0 If qd Is Nothing Then Set qd = db.CreateQueryDef(strNomRequete) ' SQL de la requête qd.SQL = strSQL ' Chaîne de connexion ODBC pour requête SQL-Direct qd.Connect = strODBCconn ' libère les objets Set qd = Nothing Set db = Nothing
Si on ne souhaite pas utiliser de DSN, la chaîne de connexion doit inclure le nom du pilote ODBC, plus d'autres attributs (dépend du pilote).
Code:

1 2 ' Chaîne de connexion ODBC sans DSN, pour requête SQL-Direct strODBCconn = "ODBC;Driver=MySQL ODBC 3.51 Driver;SERVER=MDM;DATABASE=test;UID=su;PWD=123456"
Concernant l'argument Name de OpenConnection.
Si l'argument Connect ne contient pas l'attribut DSN=xxxx, Name doit être le nom d'un DSN valide ou rien.
Si l'argument Connect contient l'attribut DSN=xxxx,je crois que Name peut être n'importe quoi.

A+

Merci pour cette réponse claire, nette et précise.

cependant, j'ai exécuté ce code :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
Private Sub OBCD()
    '---- Créer un DSN ---------
    Dim strDriver As String
    Dim strAttributes As String
    Dim intRet As Long
 
    strDriver = "Client Access ODBC Driver (32-bit)" & Chr$(0)
    strAttributes = "DSN=ODBCKPI" & Chr$(0)
    strAttributes = strAttributes & "DESCRIPTION=Test DSN" & Chr$(0)
    strAttributes = strAttributes & "UID=BAUDP" & Chr$(0)
    strAttributes = strAttributes & "System=LV011BK" & Chr$(0)
    strAttributes = strAttributes & "DATABASE=FGE50C3" & Chr$(0)
    strAttributes = strAttributes & "SIGNON=1" & Chr$(0)
    strAttributes = strAttributes & "DBQ=FGE50C3" & Chr$(0)
    strAttributes = strAttributes & "DFTPKGLIB=FGE50C3" & Chr$(0)
    strAttributes = strAttributes & "PKG=FGE50C3/DEFAULT(IBM),2,0,1,0,512" & Chr$(0)
    strAttributes = strAttributes & Chr$(0)
    intRet = SQLConfigDataSource(vbNull, 4, strDriver, strAttributes)
    If intRet Then
        MsgBox "DSN Created"
    Else
        MsgBox "Create Failed"
    End If
 
End Sub

sans succès, alors que ce code fonctionne pour le paramétrage d'un objet ODBC :

Code:

1
2
3
4
5
6
7
8
9
10
 
Sub essai()
    Dim db As Database
    Dim WrkSpaceODBC As DAO.Workspace, ConnectODBC As DAO.Connection, r2 As DAO.Recordset
    Dim strODBC As String, ErrDesc As String
    Dim qdf As DAO.QueryDef
   Set WrkSpaceODBC = DBEngine.CreateWorkspace("ODBC", "Admin", "", dbUseODBC)
   Set ConnectODBC = WrkSpaceODBC.OpenConnection("tagadatsointsoin", dbDriverPrompt, False, "ODBC;DRIVER={Client Access ODBC Driver (32-bit)};UID=BAUDP;System=LV011BK;DATABASE=FGE50C3;SIGNON=1;DBQ=FGE50C3;DFTPKGLIB=FGE50C3;PKG=FGE50C3/DEFAULT(IBM),2,0,1,0,512;")
    ConnectODBC.QueryTimeout = 900
End

J'aimerais bien réussir à en créer un : si je mets mon appli sur un serveur sur lequel je n'ai pas la main, je suis perdu !

11/07/2009, 12h12
LedZeppII

Bonjour,

Je réalise que ( ) , sont des caractère interdits.
Essaie en replaçant cette ligne

Code:

strAttributes = strAttributes & "PKG=FGE50C3/DEFAULT(IBM),2,0,1,0,512" & Chr$(0)

par

Code:

strAttributes = strAttributes & "PKG={FGE50C3/DEFAULT(IBM),2,0,1,0,512}" & Chr$(0)

c'est à dire en utilisant la syntaxe mot-clé={valeur} au lieu de mot-clé=valeur.

A+

Salut

J'ai essayé avec les accolades {}, mais j'ai toujours le message "dsn failed !"

code :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
Private Sub OBCD()
    '---- Créer un DSN ---------
    Dim strDriver As String
    Dim strAttributes As String
    Dim intRet As Long
 
 
    strDriver = "{Client Access ODBC Driver (32-bit)}" & Chr$(0)
    strAttributes = "DSN=ODBCKPI" & Chr$(0)
DATABASE=FGE50C3 SIGNON=1 DBQ=FGE50C3 DFTPKGLIB=FGE50C3 PKG=FGE50C3/DEFAULT(IBM),2,0,1,0,512"
 
    strAttributes = strAttributes & "DESCRIPTION=Test DSN" & Chr$(0)
    strAttributes = strAttributes & "UID=BAUDP" & Chr$(0)
    strAttributes = strAttributes & "System=LV011BK" & Chr$(0)
    strAttributes = strAttributes & "DATABASE=FGE50C3" & Chr$(0)
    strAttributes = strAttributes & "SIGNON=1" & Chr$(0)
    strAttributes = strAttributes & "DBQ=FGE50C3" & Chr$(0)
    strAttributes = strAttributes & "DFTPKGLIB=FGE50C3" & Chr$(0)
    strAttributes = strAttributes & "PKG={FGE50C3/DEFAULT(IBM),2,0,1,0,512}" & Chr$(0)
    strAttributes = strAttributes & Chr$(0)
    intRet = SQLConfigDataSource(vbNull, 4, strDriver, strAttributes)
    If intRet Then
        MsgBox "DSN Created"
    Else
        MsgBox "Create Failed"
    End If
 
End Sub

rageant !

J'ai essayé d'utiliser la méthode SQLWriteFileDSN, mais çà ne fonctionne pas : quelle est la syntaxe exacte ?

Hmmm ... rageant, oui.

Les accolades ne concernent que strAttributes, pas strDriver.

Je note une différence entre mon code et celui de la faq, au niveau du premier argument de la fonction SQLConfigDataSource.
Cet argument est censé être un handler de fenêtre Windows.
vbNull vaut 1, et je ne sais pas si c'est un handler valide.
Il est plus sûr de mettre 0, qui sera interprété par la fonction comme "aucune fenêtre".

Tu peux aussi essayer avec le code que j'ai mis ici.
C'est le même que celui de la faq, mais avec (j'espère) récupération du message d'erreur.

Autre choses à essayer.
Essayer de créer un DSN minimaliste : Un nom de DSN et un nom de pilote.
Cela permet au moins de valider l'usage de la fonction SQLConfigDataSource.
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 Private Sub OBCD() '---- Créer un DSN --------- Dim strDriver As String Dim strAttributes As String Dim intRet As Long strDriver = "Client Access ODBC Driver (32-bit)" & Chr$(0) strAttributes = "DSN=ODBCKPI" & Chr$(0) ' strAttributes = strAttributes & "DESCRIPTION=Test DSN" & Chr$(0) ' strAttributes = strAttributes & "UID=BAUDP" & Chr$(0) ' strAttributes = strAttributes & "System=LV011BK" & Chr$(0) ' strAttributes = strAttributes & "DATABASE=FGE50C3" & Chr$(0) ' strAttributes = strAttributes & "SIGNON=1" & Chr$(0) ' strAttributes = strAttributes & "DBQ=FGE50C3" & Chr$(0) ' strAttributes = strAttributes & "DFTPKGLIB=FGE50C3" & Chr$(0) ' strAttributes = strAttributes & "PKG={FGE50C3/DEFAULT(IBM),2,0,1,0,512}" & Chr$(0) strAttributes = strAttributes & Chr$(0) intRet = SQLConfigDataSource(0, 4, strDriver, strAttributes) If intRet Then MsgBox "DSN Created" Else MsgBox "Create Failed" End If End Sub
Essaie ensuite d'ajouter des attributs, en "décommentant" des lignes, et en réexécutant la Sub.

A+

Salut !!

çà fonctionne !!
J'ai mis 0 au lieu de 1 dans la fonction SQLConfigDataSource :

Code:

intRet = SQLConfigDataSource(0, 4, strDriver, strAttributes)

D'autre part, j'ai regardé la définition de cette fonction dans la déclaration :
Code:

1 2 3 Private Declare Function SQLConfigDataSource Lib "ODBCCP32.DLL" (ByVal hwndParent As Long, _ ByVal fRequest As Long, ByVal lpszDriver As String, _ ByVal lpszAttributes As String) As Long
Elle est définie d'après la dll "ODBCCP32.DLL". Or j'ai fait une recherche sur mon poste, et je ne l'avais pas trouvé (ce qui peut paraître étonnant !). Je l'ai donc téléchargé et installé. Etait-ce possible que cette déclaration ne soit qu'une "boîte vide" ?

En tout cas, merci beaucoup cher LedZeppII, pour ton expertise, ton implication dans mon problème, sans toi, c'était foutu.
Et sache que je suis moi aussi fan de LedZep !

Cependant

Je reviens sur le caractère "résolu" de ce post, car un petit problème vient "gripper" toute cette mécanique : Lors de la création dynamique de mon objet ODBC, tout se paramètre correctement, à l'appel de cette fonction :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 Public Function ODBCKPI(name As String, system As String, lib As String, login As String, password As String, action As Integer) As Integer '---- Créer un DSN --------- Dim strDriver As String Dim strAttributes As String Dim intRet As Long strDriver = "Client Access ODBC Driver (32-bit)" & Chr$(0) strAttributes = "DSN=" & name & Chr$(0) strAttributes = strAttributes & "DESCRIPTION=DSN pour l'appli KPIWH" & Chr$(0) strAttributes = strAttributes & "UID=" & login & Chr$(0) strAttributes = strAttributes & "PWD=" & password & Chr$(0) strAttributes = strAttributes & "LANGUAGEID=ENU" & Chr$(0) strAttributes = strAttributes & "System=" & system & Chr$(0) strAttributes = strAttributes & "DATABASE=" & lib & Chr$(0) strAttributes = strAttributes & "SIGNON=1" & Chr$(0) strAttributes = strAttributes & "DBQ=" & lib & Chr$(0) strAttributes = strAttributes & "DFTPKGLIB=" & lib & Chr$(0) strAttributes = strAttributes & "PKG=" & lib & "/DEFAULT(IBM),2,0,1,0,512" & Chr$(0) strAttributes = strAttributes & Chr$(0) Debug.Print strAttributes intRet = SQLConfigDataSource(0, action, strDriver, strAttributes) If intRet Then Debug.Print "Je suis content pour ODBC" ODBCKPI = 1 Else Debug.Print "DSN failed" ODBCKPI = 0 End If End Function
Mon DSN est soit créé, soit configuré, selon que je passe le paramètre action à 4 ou 5 en fonction de ce code :
Code:

1 2 3 4 5 6 7 8 9 10 cpt = cpt + 1 If cpt = 1 Then action = 4 suppr_odbc "ODBCKPI" Else action = 5 End If If ODBCKPI("ODBCKPI", RSEntr!serveur, RSEntr!lib, RSEntr!login, RSEntr!password, action) = 1 Then
et tous les paramètres sont correctement renseignés. J'ai pu le vérifier en ouvrant l'interface "source de données".

Je crée ensuite dynamiquement une requête SQL directe avec cette procédure :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 Private Sub createQuery(dsn As String, name As String, login As String, password As String, strg As String) Dim db As DAO.Database Dim qdf As DAO.QueryDef Set db = CurrentDb suppreq name Set qdf = db.CreateQueryDef(name) With qdf .Connect = "ODBC;DSN=" & dsn & ";UID=" & login & ";PWD=" & password .sql = strg .ReturnsRecords = True .ODBCTimeout = 1500 End With Debug.Print qdf.sql End Sub
Et grâce à l'appel suivant :

Code:

createQuery RSEntr!dsn, "RqRWMSQteStk", RSEntr!login, RSEntr!password, CStr(str)

En ouvrant la requête manuellement, et en regardant dans l'onglet "Propriétés", j'ai pu vérifier que toutes les informations étaient correctement renseignées. Tout fonctionne bien à la création de mon objet ODBC (paramètre "action" à 4).

Cependant, lors des passages suivant la création (avec le paramètre "action" à 5), à l'exécution de ma requête, tout se passe comme si les données n'avaient pas réellement été rafraîchie lors de l'appel de la fonction SQLConfigDataSource de la procédure ODBCKPI : on reste apparemment sur les données du premier ODBC, alors que dans les interfaces graphiques, les paramètres ont l'air corrects (nom de l'ODBC, login et password, base de données...), et celà m'affiche un message d'erreur disant que c'est la mauvaise base, ou que le user est mauvais...
Peut-être y a-t-il une commande que j'ai omis, ou un paramètre qu'il faut passer, à moins que ce soit un "bug", et là, adieu le DSN dynamique.

Merci

17/07/2009, 20h34
LedZeppII

Bon, j'ai fait pas mal d'essais et j'observe la même chose que toi.

Access ne prend pas en compte les changements du DSN en cours de session.
En revanche si je ferme Access et le réouvre les modifications sont perçues.

On peut émettre l'hypothèse que le DSN n'est "lu" qu'une fois au cours d'une même session Access, lors de la première utilisation.
Ça me rappelle cette discussion.
Ça n'est pas exactement ton cas de figure, mais on se heurte de manière semblable à la façon dont Access gère ses connexions ODBC.

J'ai réussi à contourné ton problème, en mettant tout dans la chaîne de connexion de la requête SQL-Direct (sans DSN donc).
Comme tu dis, adieu le DSN dynamique. En revanche le côté dynamique semble pouvoir se déplacer au niveau de la chaîne de connexion.

J'ai même testé une version avec un DSN peu fourni.
-> Nom du pilote ODBC
-> des options propres à MySQL
-> Nom du serveur.
Je déporte tous les autres paramètres dans la chaîne de connexion de la requête SQL-Direct.

En résumé, je mets dans le DSN tout ce qui est commun à mes différentes connexions.
Et tout le reste (partie variable) dans la chaîne de connexion de la requête SQL-Direct.

A+
20/07/2009, 16h59
patbeautifulday

Salut LedZepII

Merci d'être toujours présent sur ce post, en étant toujours aussi éclairant.
Je suis content que tu tombes sur le même diagnostic que moi, çà me rassure.
Quant à la chaîne de connexion dans la requête SQL directe, j'ai déjà essayé, mais çà plante je crois. Je vais refaire un essai et je te tiens au courant.
22/07/2009, 09h05
patbeautifulday

Salut LedZepII

J'ai fait des essais avec la chaîne de connexion dans la requête, et çà ne fonctionne pas. Celà semble avoir absolument besoin d'un DSN.
J'ai essayé en appelant un file DSN, et en essayant de changer le serveur, sans succès.
Conclusion : impossible de faire un DSN dynamique.

Merci pour ton implication dans ce problème LedZepII
22/07/2009, 21h13
LedZeppII

Bonjour PatBeautifukday,

Citation:

J'ai fait des essais avec la chaîne de connexion dans la requête, et çà ne fonctionne pas. Celà semble avoir absolument besoin d'un DSN.

J'ai déjà créé des requêtes SQL-Direct sans DSN. Ça devrait fonctionner.
Je suis presque sur qu'un DSN n'est pas obligatoire.

J'ai vérifié la trace ODBC d'une requête avec DSN et une autre sans DSN.
Dans les deux cas ACCESS appelle SQLDriverConnect.
Cette fonction accepte une chaîne de connexion avec DSN=NomDSN, ou avec DRIVER=NomDriver (si on ne met pas DSN=NomDSN).

Pour moi un DSN est juste un moyen pratique de stocker des paramètres de connexion, exploités par le gestionnaire ODBC ou le pilote ODBC pour établir une connexion.
Les paramètres qui ne sont pas fournis dans la chaîne de connexion sont récupérés dans le DSN.

Tu as peut-être un problème d'accolades {}. En trop ou manquantes.
Si l'anglais ne te rebutes pas regardes la section 'Comments' du lien que je t'ai mis plus haut.

A+
03/08/2009, 15h41
patbeautifulday

Salut LedZepII

J'ai fait des essais avec la chaîne de connexion complète dans la propriété .connect de la requête SQL direct, et çà ne fonctionne pas. Peut-être est-ce dû au pilote AS400 qui n'accepte pas ces propriétés. C'est mon point de vue, étant donné que tu me dis que sur SQL Server, çà fonctionne.
C'est vrai que je pourrais envisager de "déléguer" certaines choses à la chaîne de connexion, et certaines choses qui ne changent pas à l'objet ODBC (comme par exemple le pilote), mais je n'ai pas le temps de coder, et re-tester tout celà.
Je vais rester sur des objets ODBC statiques, mais avec, une procédure de création de ces objets s'ils n'existent pas dans mon application (dans le cas où on lance la procédure de MAJ des données sur un autre poste que le mien).

Merci encore pour ton expertise et ton implication sur ce post, qui profiteront sûrement à d'autres que moi.