Discussion :

[debdev]

Bonjour,

Je fais suite à l'un de mes posts sur la copie d'un recordset depuis Access vers Excel. Grâce à l'aide apportée sur ce post, j'utilisais une connexion DAO et des requêtes SQL direct vers une base Oracle: tout fonctionnait très bien et rapidement. Depuis peu, j'ai dû augmenter le nombre d'appels à ma procédure qui copie mes recordsets et j'ai des plantages de temps à autre (même pas en systématique !). J'ai cherché sur internet, et j'ai vu d'autres cas plus ou moins proches du mien. Au final, leur solution était de passer par une connexion ADO et non DAO. Donc je me suis lancée mais j'ai un souci technique d'où mon appel à l'aide.

Voici ma tentative: (ce code est placé dans ma base Access qui contient les tables liées à une base Oracle et les requêtes SQL direct)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
Sub LaunchQuery(xlWkb As excel.Workbook, MyQuery As String, MyWorksheet As String, MyDynamicRange As String, MyRangeStart As String)
 
    Dim cnn As ADODB.Connection
    Set cnn = CurrentProject.Connection
    Debug.Print cnn.Provider 'ça m'indique Microsoft.ACE.OLEDB.12.0
 
    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset
 
    Dim strSQL As String
    strSQL = "SELECT * from " & MyQuery 'MyQuery est une requete SQL direct
 
    rst.Open strSQL, cnn, adOpenForwardOnly, adLockReadOnly, adCmdText
 
    xlWkb.Worksheets(MyWorksheet).Range(MyRangeStart).CopyFromRecordset rst 'ça m'indique ODBC l'appel a échoué erreur -2147467259
 
    Set rst = Nothing
    Set cnn = Nothing
 
End Sub

What's the problem ?
Merci par avance pour votre aide !

PS: à terme, j'ai l'intention de sortir les paramètres de connexion de ma procédure et a les passer en arguments, car je ne veux pas ouvrir/fermer ma connexion des centaines de fois, mais là, c'est pour tester déjà la copie du recordset.

[debdev]

Bonjour tout le monde,

J'ai réalisé plusieurs tests pour essayer de comprendre ce qui se passait:

1. si je définis une chaine de connexion en passant par un DSN créé dans mes sources de données ODBC (dont le driver est Microsoft ODBC for Oracle), alors

• si je fais une requête select sur une table liée à la base oracle, ça marche
• si je fais une requête select sur une requête SQL direct liée à la base oracle, ça ne marche pas, ça plante sur rst.open (ODBC Driver for oracle: table ou vue inexistante)

2. si je définis une chaine de connexion en utilisant celle de la base Access sur laquelle je suis (dont le driver est Microsoft.ACE.OLEDB.12.0), alors

• si je fais une requête select sur une table liée à la base oracle, ça marche
• si je fais une requête select sur une requête SQL direct liée à la base oracle, ça ne marche pas, ça plante sur copyfromrecordset (ODBC l'appel a échoué)

Donc comment faire pour pouvoir mettre dans un recordset mes données issues d'une requête SQL direct afin ensuite de les copier dans un fichier excel ?

[EDIT]
en relisant le post dont j'ai mis le lien plus haut, LedZeppII, tu me disais qu'avec DAO, on pouvait faire du SQL direct mais pas avec ADO. est-ce ce qu'il se passe ici ? donc quel que soit le driver utilisé, ça ne change rien ?

j'ai fait d'autres tests:
- une requête Access qui fait un select sur une requête SQL direct (dont la connexion à Oracle se fait via un driver "Oracle dans OraHome92") => la requête fonctionne
- une requête Access qui fait un select sur une requête SQL direct (dont la connexion à Oracle se fait via un driver "Microsoft ODBC for Oracle") => la requête ne fonctionne pas et retourne "ODBC l'appel a échoué"

bref, je suis perdue entre ADO/DAO, drivers and co.

pour que vous compreniez bien ce que je cherche à faire avec ma base access liée à ma base oracle:
1. j'ai un formulaire où je coche des cases qui me permettent de construire ma syntaxe SQL
2. j'injecte cette syntaxe dans une requête SQL direct (liée à oracle) déjà enregsitrée dans ma base Access (je réutilise toujours la même, j'écrase seulement la syntaxe à chaque fois)
3. la requête est exécutée sur le serveur où se trouve la base oracle
4. je veux copier les résultats de la requête dans un fichier excel



Merci pour votre aide

[LedZeppII]

Bonjour,

Je vais essayer de résumer les possibilités de connexion à un SGDB (Oracle, SQL Server, MySql, ...).

Table liée ODBC ou requête SQL-Direct:
Access <----> Moteur Bdd Access <----> ODBC <----> SGDB
DAO <----> Moteur Bdd Access <----> ODBC <----> SGDB
ADO <----> Moteur Bdd Access <----> ODBC <----> SGDB
(Le Provider pour ADO est Microsoft.ACE.OLEDB.12.0 si on est en Office 2007)

La différence entre table liée et requête SQL-Direct:
Table liée : C'est le moteur de bdd d'Access qui exécute la requête
Requête SQL-Direct : C'est le moteur SGDB Oracle qui exécute la requête

Ce que l'on peut faire avec ADO, mais qu'on ne peut plus faire avec le moteur d'Access depuis la version 2007:
ADO <----> ODBC <----> SGDB ORACLE
Là on ne peut utiliser ni table liée, ni une requête SQL-Direct.
Il faut établir une connexion ODBC au travers d'ADO (Provider MSDASQL).
Ensuite, soit on ouvre une table ou une requête Oracle (une Vue), soit on fournit une instruction SQL SELECT complète (le SQL qui est stockée dans une requête SQL-Direct par exemple).

Si je reprends tes différents essais (1.a, 1.b, 2.a, 2.b):

1

si je définis une chaine de connexion en passant par un DSN créé dans mes sources de données ODBC (dont le driver est Microsoft ODBC for Oracle), alors 1.a si je fais une requête select sur une table liée à la base oracle, ça marche 1.b si je fais une requête select sur une requête SQL direct liée à la base oracle, ça ne marche pas, ça plante sur rst.open (ODBC Driver for oracle: table ou vue inexistante)

2

si je définis une chaine de connexion en utilisant celle de la base Access sur laquelle je suis (dont le driver est Microsoft.ACE.OLEDB.12.0), alors 2.a si je fais une requête select sur une table liée à la base oracle, ça marche 2.b si je fais une requête select sur une requête SQL direct liée à la base oracle, ça ne marche pas, ça plante sur copyfromrecordset (ODBC l'appel a échoué)

Dans le 1) je n'ai pas compris ce que tu as fait.

Dans le 2) tu es en ADO et tu passes par le moteur de bdd Access.
Ce que je ne comprends pas c'est pourquoi le 2.b ne fonctionne pas.
J'ai testé le principe de ton code sur un serveur mySql et ça fonctionne pour moi.

J'ai peur de ne pas pouvoir t'aider beaucoup, donc je vais te proposer d'essayer ADO avec le provider ODBC.
Mon code exemple s'appuie sur une requête SQL-Direct, dont il va récupérer la chaîne de connexion ODBC et le code SQL.
On va tomber dans le cas de figure suivant:
ADO <----> ODBC <----> SGDB (ORACLE pour toi, MySql chez moi).

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
Sub TestSQLDirect_ADO_2()
Dim oCn As ADODB.Connection
Dim oRS As ADODB.Recordset, sSql As String
Dim sSqlDirectQryName As String, sOdbcConnStr As String
Dim oXLApp As Object, oWbk As Object, oRg As Object
Dim lIdx As Long, lRow As Long
 
' Nom de la requête SQL-Direct (objet Access)
sSqlDirectQryName = "qryMySQLFournisseurs2"
' Récupérer la chaîne de connexion ODBC en retirant le préfixe "ODBC;"
sOdbcConnStr = Mid(CurrentDb.QueryDefs(sSqlDirectQryName).Connect, 6)
' Récupérer le SQL
sSql = CurrentDb.QueryDefs(sSqlDirectQryName).SQL
 
' Ouvrir une connexion ADO de type ODBC (provider par défaut)
Set oCn = New ADODB.Connection
oCn.ConnectionString = sOdbcConnStr
oCn.Open
 
' Ouvrir un recordset sur l'instruction SQL SELECT
Set oRS = New ADODB.Recordset
Set oRS.ActiveConnection = oCn
oRS.Open sSql, , adOpenForwardOnly, adLockReadOnly, adCmdText
 
If Not oRS.EOF Then
    Set oXLApp = CreateObject("Excel.Application")
    oXLApp.Visible = True
    oXLApp.UserControl = True
    Set oWbk = oXLApp.Workbooks.Add()
 
    oWbk.ActiveSheet.Range("A2").CopyFromRecordset oRS
 
    ' Alternative (non optimisée=>lente) à .CopyFromRecordset
    'lRow = -1
    'Set oRg = oWbk.ActiveSheet.Range("A2")
    'Do
    '    lRow = lRow + 1
    '    For lIdx = 0 To oRS.Fields.Count - 1
    '        oRg.Offset(lRow, lIdx).Value = oRS.Fields(lIdx).Value
    '    Next
    '    oRS.MoveNext
    'Loop Until oRS.EOF
End If
 
' Fermer et libérer le recordset
oRS.Close
Set oRS = Nothing
 
' Fermer et libérer la connexion
oCn.Close
Set oCn = Nothing
 
End Sub

A+

[LedZeppII]

Bonjour,

Je viens de tester le cas 2.b sur une bdd Oracle, et ça fonctionne.
Mon code test:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
32
33
34
Sub TestSQLDirect_ADO_1()
Dim oCn As ADODB.Connection
Dim oRS As ADODB.Recordset, sSql As String
Dim sSqlDirectQryName As String
Dim oXLApp As Object, oWbk As Object
 
' Nom de la requête SQL-Direct (objet Access)
sSqlDirectQryName = "qryORA_MVT504027"
 
' Réutiliser connexion Access
Set oCn = Application.CurrentProject.Connection
 
' Ouvrir un recordset sur une instruction SQL SELECT
sSql = "SELECT * FROM [" & sSqlDirectQryName & "]"
Set oRS = New ADODB.Recordset
Set oRS.ActiveConnection = oCn
oRS.Open sSql, , adOpenForwardOnly, adLockReadOnly, adCmdText
 
If Not oRS.EOF Then
    Set oXLApp = CreateObject("Excel.Application")
    oXLApp.Visible = True
    oXLApp.UserControl = True
    Set oWbk = oXLApp.Workbooks.Add()
    oWbk.ActiveSheet.Range("A2").CopyFromRecordset oRS 
End If
 
' Fermer et libérer le recordset
oRS.Close
Set oRS = Nothing
 
' Libérer la connexion
Set oCn = Nothing
 
End Sub

Il y a peut-être des cas où ça ne fonctionne pas, mais ça valide le principe 2.b : On peut utiliser ADO pour ouvrir un recordset ADO sur une requête Access SQL-Direct.

A+

[debdev]

Salut tout le monde,

Merci LedZeppII pour tes réponses. Dsl de ne répondre que maintenant mais je n'avais pas l'accès à ma base depuis jeudi dernier donc je préfèrais refaire les tests avant de te répondre. J'en ai fait plusieurs et le problème reste le driver ODBC utilisé pour lier la requête SQL à Oracle.

Si la requête SQL direct est liée à Oracle par le driver "Oracle dans OraHome92" => ton code (et le mien) fonctionne
Si la requête SQL direct est liée à Oracle par le driver "Microsoft ODBC for Oracle" => ton code (et le mien) ne fonctionne pas et retourne "ODBC l'appel a échoué" sur le copyfromrecordset

Alors, je me suis dit ok, utilisons le driver d'Oracle. Sauf que si j'ai changé de driver il y a 2 ans, c'est parce qu'il y avait un bug de décimales du genre 50.12 devient 5012: j'avais d'ailleurs fait un post ici.

En utilisant le driver de Microsoft, pas de bug de décimales mais échec du copyfromrecordset.
En utilisant le driver d'Oracle, bug de décimales mais le copyfromrecordset fonctionne.

Voilà donc selon le driver que je choisis, soit je n'obtiens rien soit j'obtiens des résultats ... faux
Je suis en train de lire des posts pour bidouiller les settings dans le registre pour que ça fonctionne avec le driver Oracle, mais ça me fait un peu peur de toucher le registre...

LedZeppII, dans toncode, ta requête SQL direct est liée à ta base via quel driver ?

Merci pour l'aide !

[debdev]

Re,

J'ai lu pas mal de docs sur les settings dans Oracle, sur mon PC, mais je ne comprends pas tout donc je laisse cela de côté.
J'ai donc repris les tests en utilisant une requête SQL direct liée à Oracle grâce au driver "Microsoft ODBC for Oracle".

Je fais mes tests sans passer par le VBA, directement en créant une requête Access qui dit "select * from MyQuerySQLDirect" et là, j'ai un message d'erreur à l'exécution:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
ODBC -- l'appel a échoué.
[Microsoft][Gestionnaire de pilotes ODBC] Erreur de séquence de la fonction (#0)

Je suppose que c'est ce message là qui apparait en VBA (où je n'ai que la partie "l'appel a échoué").
Donc si ça ne marche déjà pas avec une requête toute simple, ça ne marchera pas non plus en vba...

Pas encore eu le temps de lire assez de doc sur cette erreur. Vous, ça vous parle un peu ?

Merci

[debdev]

Bonjour à tous,

Petit message pour vous dire que mon problème est résolu, un grand merci à LedZeppII pour son aide même s'il m'a fallu du temps pour comprendre . Le problème venait de plusieurs choses dont mon incompréhension des requêtes/tables liées (je lançais des requêtes SQL direct sur des tables qui n'existaient pas dans ma base Oracle mais qui existaient dans ma base Access ...). Et je passais par une requête select pour lancer une requête SQL direct alors qu'il fallait utiliser le QueryDef pour récupérer la syntaxe.

Bref, tout fonctionne maintenant

Bon week end !

[LedZeppII]

Bonjour,

Désolé, je n'avais pas vu tes messages du 25/03.

Merci pour ton retour sur la résolution de ton soucis.
Ça me rassure au sujet des requêtes SQL-Direct, qui pour ma part, ne m'ont jamais causé de problèmes.

Donc, si on reprend dans l'ordre (c'est surtout à l'attention des personnes qui liraient cette discussion).

• DAO n'était pas la source du problème. On peut utiliser DAO ou ADO pour ouvrir un recordset sur une requête SQL-Direct.
  (Je préfère DAO ... moins de code à écrire; plus orienté MS-Access que ADO qui est plus généraliste)
• ADO n'offre pas d'avantage par rapport à DAO pour ouvrir un recordset sur une requête SQL-Direct.
• ADO offre un avantage par rapport à DAO pour ouvrir un recordset sur SGDB distant à condition de ne pas passer par une requête SQL-Direct.
  (On 'attaque' directement le SGDB via un provider dédié au SGDB ou via ODBC)

...je passais par une requête select pour lancer une requête SQL direct alors qu'il fallait utiliser le QueryDef pour récupérer la syntaxe

La je ne suis pas sur de comprendre.
Si tu fais référence à mon exemple de code pour le cas 2.b, il sert juste à montrer comment récupérer le Sql de la requête SQL-Direct, dans le but de s'en servir dans un contexte particulier, à savoir ADO avec le provider ODBC.
Ça n'est pas 'la méthode' pour ouvrir une requête SQL-Direct. C'est la méthode dans le contexte ADO avec le provider ODBC.
La méthode DAO reste, à mon avis, plus directe (moins de lignes de code VBA).

A+

[debdev]

Hello,

Oui, ADO ou DAO, les 2 fonctionnent. Mais de façon aléatoire avec DAO, j'avais un souci sur le copyfromrecordset (d'où mon topic). En regardant sur internet, d'autres avaient le même problème que moi, sans trouver la cause précise, mais leur solution était de passer par ADO. Une hypothèse selon moi pour ce problème initial est que certaines parties du code se lançaient avant que d'autres n'aient terminé. Comme je ne voyais pas comment faire autrement, je m'étais moi aussi lancée dans ADO. Sauf que là, il y a eu confusion totale dans mon esprit, j'ai tout mélangé (tables liées, requêtes liées, requêtes qui ouvrent des requêtes liées, ...) . J'ai repris tout pas à pas, en analysant ton code et j'ai compris ma confusion et tout fonctionne nickel

Merci encore pour ton aide.
Bonne journée !