Requete SQL VBA Excel

Version imprimable

Bonjour,

j'ai un problème que nos ITs sont incapables de résoudre, j'espère trouver plus de réponses ici ;)

On aimerait faire des reportings via Excel et Vba. Ce ne sont pas des requetes compliqués mais nous ne savons pas comment connecter Oracle à VBA/Excel.

Voici le code que j'ai réussi a construire grâce aux informations trouvées sur le net :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 Sub Connection_Oracle() 'Déclaration de la variable de connexion Dim cNx As ADODB.Connection Set cNx = New ADODB.Connection 'Définition de la chaîne de connexion cNx.ConnectionString = "DSN=XxX" & NomDuDSN & ";UID=XxX" & NomUtilisateur & ";PWD=XxX" & MotDePasse & ";" Dim varSQL As String varSQL = "SELECT tr.trade_id FROM trade tr WHERE tr.trade_id = 123" With ActiveSheet.QueryTables.Add(Connection:=cNx, Destination:=ActiveSheet.Range("A1"), Sql:=varSQL) .Refresh End With End Sub
A priori, il n'y a pas de souci au niveau de la connection avec le serveur. En tout cas dans microsoft Query, la connexion s'effectue correctement (il retrouve les tables de mon serveur). Je dis a priori, car je n'ai aucun moyen de tester la connexion dans VBA étant donné que je n'arrive meme pas a obtenir une réponse via un msgbox...

Voilà,

est ce que quelqu'un en saurait un peu plus sur le requetage sur VBA/Excel ?
Comment savoir si la connexion est correct ?
Comment faire une requete ?
Et comment formater le résultat obtenu ?

Je sais qu'il y a beaucoup de quesitons, mais si vous avez une réponse à l'unes de ces questions : n'hésiter pas !

Merci beaucoup.

20/06/2011, 16h32
pgz

Bonjour.

Pour les premiers pas :
tu ouvres une connexion ADO
tu ouvres un Recordset ADO avec une requête simple
Si oRst est ton recordse tu fais

Code:

Thisworkbook.Worksheets("Feuil1").Range("A1").Copyfromrecordset oRst

Cela devrait t'éclairer. SI tu as ensuite d'autres pb, tu reviens.

Cordialement,

PGZ

Je ne suis pas sur de bien comprendre, quelle valeur devrait apparaitre dans ma cellule A1 ?

A quoi correspond le recordset ?

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
Sub Connection_Oracle()
 
'Déclaration de la variable de connexion
Dim cNx As ADODB.Connection, oRS As ADODB.Recordset
Set cNx = New ADODB.Connection
 
 
'Définition de la chaîne de connexion
cNx.ConnectionString = "DSN=XxX" & NomDuDSN & ";UID=XxX" & NomUtilisateur & ";PWD=XxX" & MotDePasse & ";"
 
cNx.Open
 
Dim varSQL As String
 
varSQL = "SELECT tr.trade_id FROM trade tr WHERE tr.trade_id = 123"
 
ThisWorkbook.Worksheets("Feuil1").Range("A1").CopyFromRecordset oRS
 
'With ActiveSheet.QueryTables.Add(Connection:=cNx, Destination:=ActiveSheet.Range("A1"), Sql:=varSQL)
'    .Refresh
'
'End With
 
'With ActiveSheet.QueryTables.Add(Connection:=cNx, Destination:=Range("A1"))
'.CommandText = SQLSTRING
'.AdjustColumnWidth = False
'.Refresh (False)
 
'End With
End Sub

Re,

CEla devrait ressembler à

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
Sub Connection_Oracle() 
'Déclaration de la variable de connexion
Dim cNx As ADODB.Connection, oRS As ADODB.Recordset, sSQL As String
Dim NomDuDSN As String, NomUtilisateur As String, MotDePasse As String
 
Set cNx = New ADODB.Connection
 
'Définition de la chaîne de connexion
cNx.ConnectionString = "DSN=XxX" & NomDuDSN & ";UID=XxX" & NomUtilisateur & ";PWD=XxX" & MotDePasse & ";"
cNx.Open
 
sSQL = "SELECT tr.trade_id FROM trade tr WHERE tr.trade_id = 123"
Set oRS = New ADODB.Recordset
 
oRS.Open sSQL, cNx, adOpenStatic
 
With ThisWorkbook.Worksheets("Feuil1")
    .UsedRange.ClearContents
    .Range("A1").CopyFromRecordset oRS
End With
 
oRS.Close
cNx.Close
Set oRS = Nothing
Set cNx = Nothing
End Sub

Cordialement,

PGZ

21/06/2011, 15h45
F.Salenko

La connection se fait bien. Si je mets les mauvais identifiants, le programme me renvoie un message d'erreur.
En revanche toujours aucune écriture au niveau de la cellule A1.

A quoi sert la commande recordset ?

Aurais tu une alternative pour faire apparaitre le resultat de la requete ? (qui devrait etre "123")

Si j'arrivais a faire apparaitre un résultat, j'aurais une base pour démarrer les recherches.

Merci d'avance pour ton aide

Bonjour.

Si la requête renvoie au moins 1 enregistrement, il devrait être copié dans la feuille. Tu peux vérifier avec

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
Sub Connection_Oracle()
'Déclaration de la variable de connexion
Dim cNx As ADODB.Connection, oRS As ADODB.Recordset, sSQL As String
Dim NomDuDSN As String, NomUtilisateur As String, MotDePasse As String
 
Set cNx = New ADODB.Connection
 
'Définition de la chaîne de connexion
cNx.ConnectionString = "DSN=XxX" & NomDuDSN & ";UID=XxX" & NomUtilisateur & ";PWD=XxX" & MotDePasse & ";"
cNx.Open
 
sSQL = "SELECT tr.trade_id FROM trade as tr WHERE tr.trade_id = 123;"
Set oRS = New ADODB.Recordset
 
oRS.Open sSQL, cNx, adOpenStatic
 
MsgBox IIf(oRS.EOF, "Il n'y a aucun enregistrement", "Il y a au moins un enregistrement")
 
With ThisWorkbook.Worksheets("Feuil1")
    .UsedRange.ClearContents
    .Range("A1").CopyFromRecordset oRS
End With
 
oRS.Close
cNx.Close
Set oRS = Nothing
Set cNx = Nothing
End Sub

Si le jeu est vide et qu'il n'y a pas d'erreur levée, essaie à nouveau en enlevant la clause WHERE.

Cordialement,

PGZ

En effet, j'ai eu un retour. Super début.

Voici le code pour ceux que cela intéresse :

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
32
33
34
 
Sub Connection_Oracle2()
 
'Déclaration de la variable de connexion
Dim cNx As ADODB.Connection, oRS As ADODB.Recordset
Dim NomDuDSN As String, NomUtilisateur As String, MotDePasse As String, sSQL As String
 
NomDuDSN = "XxX"
NomUtilisateur = "XxX"
MotDePasse = "XxX"
 
Set cNx = New ADODB.Connection
 
'Définition de la chaîne de connexion
cNx.ConnectionString = "DSN=" & NomDuDSN & ";UID=" & NomUtilisateur & ";PWD=" & MotDePasse & ";"
cNx.Open
 
sSQL = "SELECT trade_id FROM trade ;"
Set oRS = New ADODB.Recordset
 
oRS.Open sSQL, cNx, adOpenStatic
 
MsgBox IIf(oRS.EOF, "Il n'y a aucun enregistrement", "Il y a au moins un enregistrement")
 
With ThisWorkbook.Worksheets("Feuil1")
    .UsedRange.ClearContents
    .Range("A1").CopyFromRecordset oRS
End With
 
oRS.Close
cNx.Close
Set oRS = Nothing
Set cNx = Nothing
End Sub

Je vais étudier les commandes pour adapter la macro. j'essaierai de faire un tuto par la suite.

Je te tiens au courant de l'avancement du projet. J'aurais probablement d'autres questions demain.

Encore Merci !

23/06/2011, 10h24
F.Salenko
Rebonjour pgz,

Bon, je ne sais pas pourquoi mais le programme semble mieux lire la requete quand celle ci est en ligne :
Code:

1 2 3 sSQL = " SELECT * " & _ " FROM trade tr " & _ " WHERE tr.trade_id = '34101235' "
Les strings étant limités à 255 caractères, si ma requete est trop longue est ce que je peux l'enregistrer sur deux variables comme cela :

Requete1 = SQL1
Requete2 = suite de SQL1

et lancer la requete :

oRS.Open Requete1 & requete2, cNx, adOpenStatic

?

Merci pour ton aide, elle a été précieuse.

Je ne cloture pas le sujet tout de suite si j'ai des problèmes d'éditions.

FS
23/06/2011, 10h39
pgz

Bonjour.

Une chaîne de longueur variable peut contenir 2^31 caractères, soit plus de 2 10^9. Cela devrait être assez, non?

C'est dans les tables access que ce type est limité à 255.

Donc tu peux avoir sSQL aussi longue que de besoin.

Cordialement,

PGZ
20/08/2014, 18h09
norelien

précision
Bonour à tous,

Je déterre le sujet histoire de compléter la réponse maintenant que j'ai une solution.

La requete sql envoyée depuis excel en vba vers la base de donnée est une chaine de caractères soit un string.

Dans VB/Excel, il n'est pas possible de couper les bloc fonction mais on peut toujours couper une ligne de commande pour un retour à la ligne sous certaines règles. C'est souvent ce que fait l'éditeur de macro sans logique apparente.
Il faut couper la ligne avant un bloc fontion. On termine alors la jonction par un espace+ underscore ( _). On revient à la ligne et on la commance alors avec la commande suivante (& par exemple)
Pratique pour une requete sql souvent illisible car trop longue.
Je corrige donc le bloc du post précédent dont le & est mal placé :
Code:

1 2 3 sSQL = " SELECT * " & _ " FROM trade tr " & _ " WHERE tr.trade_id = '34101235' "
devient...
Code:

1 2 3 sSQL = "SELECT * " _ & "FROM trade tr " _ & "WHERE tr.trade_id = '34101235' "
Attention, l'utilisation de " _" ne supporte plus rien après. Pas question de rajouter des commentaires en fin de ligne.
CQFE. :)

J'ai mis 2 jours à trouver la réponse, j'espère que cela pourra en aider d'autres.

See You all