Récupérer le resultat d'une requête SQL dans un variant (VBA)

Bonjour à tous,

Je souhaiterais rentrer le résultat d'une requête SQL dans un variant VBA.
Je sais copier le résultat directement dans une feuille excel en indiquant la cellule de base (celle qui sera en haut à gauche) via cells.copyfromrecordset.
mais aucune idée pour le mettre directement dans un variant vba (sans passer par l'étape feuille excel).

Voici le code, les XXX sont rentrés avec les bons paramètres.
c'est la partie en gras qui ne fonctionne pas, rs n'étant pas un tableau il n'a pas de "taille".
Ce que je voudrais c'est rentrer le résultat de la requête dans V. Ensuite je vais faire des calculs et sortir le résultat de ma fonction.

Merci!

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

Function OSSql(a As Variant) As Variant Dim dbConnection As ADODB.Connection Dim rs As ADODB.Recordset Dim dbParameters As String Dim query As String Dim v As Variant dbParameters = "Provider=XXX;Data Source=XXX;" & _ "Initial Catalog=XXX;" & _ "Integrated Security=SSPI;" Set dbConnection = New ADODB.Connection Set rs = New ADODB.Recordset dbConnection.Open dbParameters query = "SELECT XXX FROM XXX" Set rs = dbConnection.Execute(query) Redim(C(ubound(rs)) V.CopyFromRecordset rs End Function

---

Bonjour,

je pense que tu cherches la méthode "getrows" ?

extrait d'une fonction perso, requeteSQL est de type Variant, Requet est de type Object

Code:

---

1 2 3 4 5 6 7 8 9 10 11

Set Requete = Source.Execute(xSQL)   With Requete If .EOF Then Exit Function Else requeteSQL = Application.Transpose(.getrows) End If   .Close End With

---

ensuite, la partie "procédure appelante" : C'est Tabl (type Variant) qui reçoit le résultat
Bon, ici je l'injecte ensuite sur une feuille excel, mais ce n'est qu'un détail

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

Sub DesTestsSQL() Dim LaRequete As String Dim Tabl As Variant   OuvertureConnexion   ActiveSheet.Cells.Delete LaRequete = "SELECT FORMAT(DATE, 'yyyy-mm-dd') AS DATE_ACTIVITE,AGENT,SUM(QUANTITE) AS PRODUCTIVITE " & _ "FROM [" & LaFeuille & "$] " & _ "GROUP BY DATE,AGENT " & _ "ORDER BY DATE,AGENT" Tabl = requeteSQL(LeFichier, LaRequete)   If Not IsArray(Tabl) Then MsgBox "aucun résultat" Else Cells(2, 1).Resize(UBound(Tabl, 1), UBound(Tabl, 2)).Value = Tabl End If   FermetureConnexion   End Sub

---

Bonjour Joe,

Merci pour ton aide, le début fonctionne bien! j'arrive ainsi à récupérer la taille ainsi que le premier champ de l'équivalent de ton requeteSQl chez moi.
En revanche je ne peux pas passer par une feuille excel pour "populer" mon tableau car je suis dans une fonction et non une procédure.

Il faut donc que j'arrive à remplir l'équivalent de ton "tabl" avec les résultats de la requête.
Et là je ne sais pas trop comment faire.

Voici mon code actuel, avec l'endroit où (j'imagine) ça coince en gras.
Pour le moment c'est un tableau à une colonne, l'idée ensuite sera de pvr en remplir à n col.


Merci d'avance pour votre aide :)

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 35 36 37 38 39 40 41

Function OSSql(a As Variant) As Variant Dim dbConnection As ADODB.Connection Dim rs As ADODB.Recordset Dim dbParameters As String Dim query As String Dim v As Variant Dim tabl() As Variant Dim i As Integer dbParameters = "Provider=XX;Data Source=XX;" & _ "Initial Catalog=XX;" & _ "Integrated Security=SSPI;" Set dbConnection = New ADODB.Connection Set rs = New ADODB.Recordset dbConnection.Open dbParameters query = "SELECT XX FROM XX" Set rs = dbConnection.Execute(query) With rs If .EOF Then Exit Function Else v = Application.Transpose(.GetRows) End If .Close End With ReDim tabl(UBound(v)) For i = LBound(v) To UBound(v) tabl(i) = v(i).Value Next i OSSql = tabl(3) End Function

---

E/ NVM, en fait je peux travailler directement sur V :) j'avais simplement oublié qu'il fallait préciser v(X,1) pour avoir v(x)...
Encore merci Joe pour ton aide précieuse! :lol:

Bonjour,

mais ......... ta variable V est :

- déjà un Array : pourquoi vouloir remplir un autre tableau avec ton tableau ? V n'est pas un array UNIQUEMENT si tu n'avais aucun résultat (d'où le test .EOF pour vérifier si le retour de la requête est vide). V est dans ce cas un variant sans aucun affectation de valeur .. et peut donc être n'importe quoi.

- un array à deux dimensions : tu dois impérativement indiquer la dimension dans tes boucles et manipulations, contrairement à un array unidimensionnel.

Code:

---

1 2	Debug.Print V(1,1) ' Première ligne, première colonne de ton resultat ' en partant du principe que tu es en Base 1

---

Je suis assez surpris de la façon dont tu as construit ta fonction : elle contient en dur ta requête, et elle ne renvoit qu'un seul élément (qui semble être un string) via un retour de type variant (dangereux du coup).

Et de plus, ta fonction gérant elle-même l'identification d'un élément pour le retourner à la procédure appelante ... si tu n'as aucun résultat ta fonction ne retourne rien, il est donc primordiale de laisser ta fonction faire la basse besogne (récupération) et de laisser ta procédure appelante analyser et gérer ce qu'on lui retourne.

Je te conseille vraiment :

- de laisser cette fonction uniquement réaliser la requête : tu lui envoie les paramètres nécessaires
- la fonction te renvoi un variant représentant le .getrows (s'il y a des résultat)
- la procédure appelante stocke ça dans un tableau qu'ensuite tu manipules

Si vraiment ton besoin primaire est de ne retourner qu'un seul résultat, alors :

- tu peux ajouter un paramètre à ta fonction qui te renverra le bon élément
- tu affines ta requête SQL pour filtrer en amont


Clairement, la logique de l'exemple que je t'ai montré semble correspondre à ton besoin, mais je te laisse décrire plus précisément ce que tu fais, et pourquoi tu voulais juste la troisième ligne de résultat

Hello! Merci pour ta réponse :)

Oui j'avais oublié de préciser les 2 dim... du coup je pensais qu'il fallait passer par une étape supplémentaire.

Sinon globalement pour l'utilité de la fonction, celle là n'en a aucune évidement :) je voulais juste comprendre le mécanisme pour pouvoir ensuite l'adapter à mes besoins. L'idée c'est que les paramètres de la fonctions changent la requête et que je puisse faire des calculs sur mon variant pour ensuite sortir un résultat.

je ne voulais pas spécialement sortir la v(3), c'était un simple test pour vérifier si ça fonctionnait

j'ai l'impression que j'ai tout ce qu'il me faut maintenant pour avancer.
encore merci :)

Bon j'ai réussit à faire tout ce que je voulais sur des requêtes qui me renvoient qu'une seule colonne.
Quid des requêtes qui en renvoient plusieurs?
on peut rentrer le résultat de la colonne 1 dans un array, de la colonne 2 dans un autre etc?

Bonjour,
Tu peux sélectionner que ce dont tu as besoin!

Code:

---

query = "SELECT cham1,champ3 FROM XX"

---

Si 1 requête renvoie plusieurs champs (donc plusieurs colonnes) alors ton Array qui reçoit le .getrows contient tous ces champs dans des colonnes séparées

Tel que présenté dans mon post #6 : http://www.developpez.net/forums/d16...a/#post8767866

la seconde procédure manipulait un tableau (Tabl) contenant le résultat d'une requête qui rappatrie plusieurs colonnes
et on y voit, quand j'injecte le résultat dans une feuille excel, que je manipule le tableau en deux dimensions directement, sans artifice ou étape intermédiaire (multi-requête, multi remplissage etc...) :

Code:

---

1 2 3 4 5

If Not IsArray(Tabl) Then MsgBox "aucun résultat" Else Cells(2, 1).Resize(UBound(Tabl, 1), UBound(Tabl, 2)).Value = Tabl End If

---

Du coup, je ne comprend pas le pourquoi de ta dernière question, tout semblait ok au niveau de la mécanique.

Et j'appuie dysorthographie, mon dernier message, par sa longueur, a probablement noyé ce que je disais à ce propos

Citation:

---

- tu affines ta requête SQL pour filtrer en amont

---

Je suis totalement novice avec le getrows & globalement avec les liens SQL/VBA. J'utilisais jusque là le "copyfromrecordset" qui n'est pas adapté à mon besoin ici.

Je comprends maintenant que la tableau reçoit tous les résultats de la requête via le getrows ce qui est très bien.
J'avais bien compris et je sais bien qu'idéalement je devrais faire tous les calculs "dans" la requête... mais ayant déjà toute une bibliothèque de fonctions VBA je me dis que c'est aussi simple de les utiliser...

Le soucis qu'il me reste (c'est surement assez basique mais quand on ne l'a jamais fait ça coule pas forcément de source ;) ), c'est comment sélectionner la colonne X du tableau sortant de la requête et l'entrer comme paramètre d'une fonction?

Par exemple, j'ai une fonction qui va me calculer un ecart type et à qui je dois rentrer comme paramètre une série qui sera retournée par ma requête, disons la colonne 2.

Comment je peux sélectionner uniquement cette colonne 2?

Par ex avec V= tableau recevant la requête dont la deuxième colonne est celle qui m'interesse, et maFonction une fonction renvoyant l'ecartype d'une serie:
J'ai essayé:
Resultat = mafonction(v(2))
mais ce n'est pas ça...

merci :)

Soit un Tableau V à 2 dimensions et contenant n colonnes
si tu veux utiliser la colonne 4 en tant que paramètre de ta fonction :

Code:

---

Resultat = mafonction(Application.Transpose(Application.Index(V, , 4)))

---

Peut-être que tu devras au préalable injecter cette colonne dans un tableau intermédiaire (aucune idée, tu testeras), auquel cas :

Code:

---

1 2	MonTableauTemp = Application.Transpose(Application.Index(V, , 4)) Resultat = mafonction(MonTableauTemp)

---

fait à la va-vite, mais normalement ça devrait déjà te donner des pistes
attention à vérifier le passage ByVal de ton paramètre dans la fonction, car je crois qu'il ne va pas apprécier de le recevoir ByRef ?

ok merci, il fallait bien passer par une étape intermédiaire sinon ça ne fonctionne pas.
j'ai tjr du mal à comprendre le pourquoi du comment du transpose ~ je ne visualise pas bien du tout, il faudrait que je me fasse des exemples simples.
c'est comme les byref / byval ça reste encore très mystérieux pour moi^^

En tout cas ça fonctionne, c'est déjà pas mal :)
merci!

Tu vas voir, c'est très simple à comprendre :

1) Transposer un tableau (ou une plage), c'est inverser les colonnes et les lignes
2) Un tableau unidimensionnel est présenté sous la forme d'une ligne
3) Si tu injectes ta colonnes telles quelle dans un tableau unidimensionnel ... tu vas avoir un problème. tu veux insérer une barre verticale dans une barre horizontale

==> donc tu transpose ta "colonne 4" afin qu'elle devienne une "barre horizontale" pour la ranger dans ton tableau de dimension 1

J'ai fais simple ... parfois on peut être amené à transposer deux fois de suite (gommer la seconde dimension et remettre la "barre" dans le sens qui te convient)


ByRef = On travaille directement sur l'adresse mémoire de la variable ==> la fonction peut modifier la variable réelle
ByVal = On travaille sur une copie conforme de la variable ==> même si la fonction modifie la variable, vu que c'est une copie, la variable initiale n'en est pas affectée

ah en effet expliqué comme ça ce n'est pas si compliqué.
j'ai tjr vu les résultats des requêtes affichés en format "colonnes" sur SQL server management... mais si en "réalité" ce sont des lignes, je comprends mieux pourquoi on transpose...
merci =)

Bonjour,
je ne comprend toujours pas pourquoi passe par un tableau et pas par un copyrecordset!

puisque si j'ai bien compris ce que tu fais après revient au même! apprends à manipuler le langage Sql ce serra plus efficace!

Je suis entièrement d'accord avec toi dans le principe.

Dans la pratique, la réunion de deux conditions me fera peut-être dire l'inverse :

- il a besoin d'utiliser plusieurs colonnes dans plusieurs appels de fonction (et ne souhaite pas faire X requêtes SQL identiques pour rappatrier les champs les uns après les autres)
ET
- il est impossible avec un RecordSet contenant plusieurs champs d'isoler le champs unique voulu pour l'ensemble du RecordSet (sauf à sous-requêter ... mais du coup on enfreint la première condition :aie:)



vu que je ne sais pas si la deuxième condition est possible (j'utilise très peu cet objet) ... je m'en tiens à ça, jusqu'à lire tes lumières, et éventuelles méthodes pour récupérer un seul champs du RecordSet si c'es effectivement possible.

Citation:

---

Envoyé par dysorthographie

Bonjour,
je ne comprend toujours pas pourquoi passe par un tableau et pas par un copyrecordset!

puisque si j'ai bien compris ce que tu fais après revient au même! apprends à manipuler le langage Sql ce serra plus efficace!

---

c'est surtout parce que c'est une fonction et non une procédure, je ne veux afficher qu'un résultat (ou plusieurs dans le cas de fonctions matricielles) et non pas tt le tableau.
et oui je sais bien, mais comme dit plus haut j'ai déjà toute une bibliothèque de fonctions vba qui ont comme arguments des ranges/variants. et ça m'évite de tt refaire en sql (que je ne maîtrise pas très bien, ça me prend un temps fou de faire des opérations simples... je ne sais même pas si faire des régressions linéaires par ex c'est possible en sql?)

d'autre part comme le dit joe, ça m'évite aussi d'avoir à faire une requête pour chaque fonction, ici je rapatrie tout le tableau et mes fonctions vont piocher dedans ce dont elles ont besoin.

Voilà ce que j'utilise depuis 20 ans, et ce sur n'importe quel type de base de données.

http://www.developpez.net/forums/d14...a/#post7767068

Dès l'instant ou tu t'es connecté sur une base de donnée tu peux faire peint de requête.

Tu peux manipuler des recordset les réactualiser.

Je regarde pour énumérer toutes les possibilités.

Ça ne sera pas fait dans la minutes mais je m'y engage.

Tu peux faire une fonction qui te permet de choisir tes champs, ta table ton filtre et les valeurs à retourner!

Ah non, perd pas ton temps à présenter tout ça !

Tu me dis que c'est possible, donc c'est à moi de me "culturer" comme un grand

je commencerai par le lien de ton message ... et je "m'amuserai" à modifier certains de mes programmes qui font des copies de Base de Données hebergées sous feuille Excel (pour réaliser divers traitements et tableaux de bord) .... car voir que 80% de mes temps de traitements les plus récalcitrants sont liés à ... l'ouverture des fichiers .... je n'en puis plus !


Merci pour la réponse, pour moi elle me suffit amplement :plusser:

L'avantage d'un module de classe, c'est l'auberge espagnol, chacun y apporte ce qu'il a et en plus c'est sous forme d'une variable objet!

Comme ça ce sera ma première vraie classe peut-être .... j'ai jamais réussi à maîtriser et à conceptualiser les classes, quand il est utile d'en créer une ou pas, toussa toussa ... :aie:

c'est pas faute d'avoir tenté les tuto du forum, ou les exemples qui parsèment le forum :aie:

Si un jour ça t'intéresse on pourra aborder le sujet!

Ici il faut le voir comme un programme complet encapsulées dans une variable objet.

Mais un module de classe permet des chose très puissant avec de collection!

Notes qu'un module de classe implique une autre façon de raisonner.

Mais pour l'instant j'estime avoir pollué suffisamment le poste de notre ami!

petit exemple a partir d'excel!

F1	F2	F3	F4	F5
A1	B1	C1	D1	E1
A2	B2	C2	D2	E2
A3	B3	C3	D3	E3
A4	B4	C4	D4	E4
A5	B5	C5	D5	E5
A6	B6	C6	D6	E6
A7	B7	C7	D7	E7
A8	B8	C8	D8	E8
A9	B9	C9	D9	E9
A10	B10	C10	D10	E10
A11	B11	C11	D11	E11
A12	B12	C12	D12	E12
A13	B13	C13	D13	E13
A14	B14	C14	D14	E14
A15	B15	C15	D15	E15
A16	B16	C16	D16	E16
A17	B17	C17	D17	E17
A18	B18	C18	D18	E18

Code:

---

1 2 3 4 5 6 7 8 9 10

Sub test() With CreateObject("Adodb.connection") .Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=""Excel 12.0;HDR=YES;""" Set Rs1 = .Execute("Select * from [Toto$] where [F1]='A10'") Set Rs2 = .Execute("Select [F2] from (" & Rs1.Source & ")") ThisWorkbook.Sheets(2).Range("A1").CopyFromRecordset Rs1 ThisWorkbook.Sheets(3).Range("A1").CopyFromRecordset Rs2 ThisWorkbook.Sheets(4).Range("A1").CopyFromRecordset .Execute("Select [F3], [F5] from (" & Rs1.Source & ")") End With End Sub

---