VBA Tableaux structurés & SQL

Bonjour,

Pour des raisons pratiques, je voudrais créer une fonction de recherche SQL valide pour x tableaux structurés.
Je tiens vraiment à cette solution SQL, pas à une boucle ou autre filtres, ça je sais faire.

Type de fonction attendue :

Code:

---

Function SQLFind(Table As ListObject, ParamArray Champs() As Variant) As String()

---

Sous-entendu, quelque soit mon tableau structuré, le souhaite retourner tout le contenu d'une ligne en fonction d'un ou plusieurs champs.

J'ai tenté plusieurs approches, et, systématiquement, le Recordset me renvoie une erreur d'exécution '3011' : Le moteur de base de données Microsoft Access n'a pas pu trouver l'objet PETITSDEJEUNERS. Vérifiez...
Mon dernier code de tentative infructueuse :

Code:

---

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

Sub sqlSelect() ' Nécessite la référence Microsoft Office xx.x Access Database Engine Object Library Dim Table As DAO.Database, RecSet As DAO.Recordset, i As Long, LO As ListObject Set LO = Worksheets("RESTAURATION").ListObjects("PETITSDEJEUNERS") Debug.Print LO.Name Set Table = OpenDatabase(Workbooks("DEPLACEMENTS DPTs").FullName, False, False, "Excel 8.0") Set RecSet = Table.OpenRecordset("SELECT * FROM " & LO.Name) If RecSet.RecordCount > 0 Then RecSet.MoveFirst For i = 1 To RecSet.RecordCount If RecSet!Prenom <> "" Then Debug.Print RecSet!Prenom & vbTab & RecSet!Nom RecSet.MoveNext Next i End If Set LO = Nothing RecSet.Close Set RecSet = Nothing Table.Close Set Table = Nothing End Sub

---

Salut Franck

Le moteur Jet ne reconnait pas les listobject, mais bien les plages nommées. Sélectionne ton ListObject et crée une plage nommée qui s'appuie dessus. Tu pourras normalement utiliser cette plage nommée comme nom de table. Note que le nom, lorsque validé, n'apparaît pas à la sélection du tableau.


Pièce jointe 577509


Rappel: Un listobject n'est pas présent dans la collection des Names du classeur (c'est pourquoi il n'est pas reconnu par le moteur Jet)

Bonjour Pierre,

Et merci de ta réponse.
En effet, en ajoutant des noms au classeur, ça répond bien au besoin.

Entre temps, j'avais réussi (enfin!) à trouver un autre moyen, consistant à passer par ADODB et les adresses : du classeur et du Range du ListObject.
Je la posterais ici lorsqu'elle sera aboutie.

Merci encore.
a+

Voici, donc, la fonction que j'ai réalisé.

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 Function SQLFind(WbkFullName As String, ListObj As ListObject, strRequete As String) As Variant ' Nécessite la référence Microsoft ActiveX Data Objects x.x Library ' ------------------------------------------------------------------------------------------------------------------------------------' ' Arguments : ' WbkFullName : Adresse complète du fichier Excel. ' Exemple : ThisWorkbook.FullName ' ListObj : Le ListObject concerné. ' Exemple : Worksheets("Feuil1").ListObjects("NOMDULISTOBJECT") ' strRequete : La requête SQL. ' Exemple : "SELECT * FROM produit WHERE NOM = 'informatique' AND DATES = #19/08/2020#" ' ------------------------------------------------------------------------------------------------------------------------------------' Dim adoConnect As ADODB.Connection Dim adoRecord As ADODB.Recordset Dim strConn As String, strListObjAddress As String   'Affectation des variables strListObjAddress = ListObj.Range.Address(0, 0) strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & WbkFullName & ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"";" Set adoConnect = CreateObject("ADODB.Connection") Set adoRecord = CreateObject("ADODB.Recordset") 'Connection adoConnect.Open strConn adoRecord.Open strRequete, adoConnect 'Retour SQLFind = adoRecord.GetString 'Suppression des objets adoRecord.Close Set adoRecord = Nothing adoConnect.Close Set adoConnect = Nothing End Function

---

Exemple d'appel :

Code:

---

1 2 3

Dim Retour As Variant Retour = SQLFind(ThisWorkbook.FullName, Worksheets("Feuil1").ListObjects("NOMDULISTOBJECT"), "SELECT * FROM produit WHERE NOM = 'informatique' AND DATES = #19/08/2020#") Debug.Print Split(Retour, vbTab)(0) & " " & Split(Retour, vbTab)(1) & " " & Split(Retour, vbTab)(2) & " " & Split(Retour, vbTab)(3)

---

Cela semble fonctionner correctement, du moins comme je l'espérais.
Ne m'y connaissant pas plus que cela en SQL, ET sachant que le provider ne posera pas de souci, y a t'il des dangers à éviter? Un risque d'erreur à traiter?

Perso, pour rendre la fonction générique, je travaillerais avec des paramètres au niveau de la requête, pour éviter de devoir gérer correctement les parties variables du texte SQL. En effet, certains caractères du texte doivent être échappés (les ', %, _ et autres), les dates doivent être passées au format EN et encadrées par #, les valeurs décimales doivent utiliser le bon séparateur décimal, etc.

Par exemple, le code suivant va poser problème à cause de l'apostrophe présente dans la variable

Code:

---

1 2 3

Nom = "Camping de l'échalotte"   sql = "select * from matable where Nom='" & Nom & "'"

---

En utilisant des paramètres, tu évites ce problème (Code non testé, donné à titre d'exemple):

Code:

---

1 2 3 4 5 6 7 8 9 10

Nom = "Camping de l'échalotte" sql = "select * from matable where monchamp = ?" Set cn = New ADODB.Connection cn.Open "machainedeconnexion" With cm .ActiveConnection = cn .CommandText = sql .Parameters.Append cm.CreateParameter("p1", adVarChar, adParamInput, 255, Nom) End With Set rs = cm.Execute()

---

J'ai modélisé cela pour Access dans ce billet, mais on peut adapter pour Excel.

Ok. MErci.
Je vais me renseigner sur GetParameters et lire ton billet.

Pour info, je l'ai déjà lu dans plusieurs de tes productions, qu'appelles-tu "Dal"?

Data Access Layer (Couche d'accès aux données). En programmation 3-tiers, c'est la couche qui gère les échanges entre l'application et le stockage des données. Dans mon billet, DAL représente le module générique qui sert d'extension à ADODB, et dans lequel je capitalise le code d'accès aux données. A priori, sauf ajout de fonctionnalités, on n'y modifie rien de projet en projet, puisqu'il a vocation d'être générique. Ca permet de systématiser l'approche et de ne pas réinventer la roue à chaque nouveau projet lié à une gestion de données.

Dans mon billet, j'illustre que cette Dal générique est utilisée par des dal spécifiques qui, elles, sont liées à "l'application" développée. L'utilisation des paramètres va permettre d'utiliser aisément cette dal générique. J'en explique la création et le fonctionnement dans ce billet, le premier des quatre liés à cette approche.






Dans le premier billet, j'utilise la fonction suivante pour récupérer la chaine de connexion, en précisant que normalement, on ne met pas les valeurs métier (ici le nom de la base Access) en dur.

Code:

---

1 2 3

Private Function getConnectionString() As String getConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=D:\Documents\Database19.accdb" End Function

---

Dans mes vrais projets, j'ai en fait:

• La DAL générique (sans cette fonction qui récupère la chaine de connexion);
• Une DAL "projet". Si je travailles pour Tartempion, je l'appelle dalTartempion et ce module contient la fonction getConnectionString. Dans un Excel, les valeurs variables de la connexion sont dans un tableau structuré de paramétrage;
• Autant de dal spécifiques que je gère de "notions": dalContact, dalProduit, dalVehicule, etc...

Ces dal spécifiques utilisent la dal générique en lui passant la chaine de connexion récupérée par la dalProjet... De cette façon, la dal générique est vraiment générique.

Dal Générique, où on voit bien qu'il n'y a aucune notion de métier ni même de type de base de données ou de connexion. Pour limiter les notions ADODB à cette dal générique, j'y ai aussi placer la fonction qui permet de créer les paramètres à ajouter à la commande:

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 42 43 44

Function getTransposedRows(connectionString As String, CommandType As CommandTypeEnum, sql As String, Optional Parameters) Dim Rows   Rows = getRows(connectionString, CommandType, sql, Parameters) If Not IsEmpty(Rows) Then getTransposedRows = Transpose(Rows) End Function   Function getRows(connectionString As String, CommandType As CommandTypeEnum, sql As String, Optional Parameters) Dim cn As New ADODB.Connection Dim cm As New ADODB.Command Dim pm As ADODB.Parameter Dim Counter As Long Dim rs As ADODB.Recordset   cn.Open connectionString cm.ActiveConnection = cn cm.CommandText = sql cm.CommandType = CommandType If Not IsMissing(Parameters) Then For Counter = 0 To UBound(Parameters) cm.Parameters.Append Parameters(Counter) Next End If Set rs = cm.Execute() If Not rs.EOF Then getRows = rs.getRows() rs.Close cn.Close End Function   Function getParameter(Name As String, _ ParamType As ADODB.DataTypeEnum, _ Direction As ADODB.ParameterDirectionEnum, _ size As Long, _ ByVal Value) As ADODB.Parameter   Set getParameter = New ADODB.Parameter With getParameter .Name = Name .Type = ParamType .Direction = Direction .size = size .Value = Value End With End Function

---

Dal projet, qui permet entre autres choses de récupérer la chaine de connexion stockée dans un tableau structuré (et récupérée par une fonction du module AppTools qui contient des outils liés à l'application gérée):

Code:

---

1 2 3 4 5 6 7 8 9

Function getConnectionString() As String If Environ("username") = "Pierre Fauconnier" Then getConnectionString = appTools.getParameter("Connexion Pierre") getConnectionString = Tools.ReplaceStrings(getConnectionString, Array("{pwd}", "****")) Else getConnectionString = appTools.getParameter("Connexion") getConnectionString = Tools.ReplaceStrings(getConnectionString, Array("{uid}", Environ("username"), "{catalog}", "*****")) End If End Function

---

dal spécifique (ici pour la gestion de contrats) qui récupère entre autres les données des contrats pour un fournisseur particulier (ici au travers d'une procédure stockée):

Code:

---

1 2 3 4 5 6 7 8 9

Function getRow(SupplierID As Long) Dim sql As String Dim Parameters   sql = "[xl_BI].[dbo].[Contract_getFrameContract]" Parameters = Array(DAL.getParameter("P1", adInteger, adParamInput, 4, SupplierID))   getRow = DAL.getRows(dalEloy.getBiConnectionString, adCmdStoredProc, sql, Parameters) End Function

---

Merci pour tout Pierre.
Je vais donc lever le pied sur le projet pour étudier tout ceci.

Je reviens en cas de besoin ou pour placer cette discussion comme "résolue".

Citation:

---

Envoyé par pijaku

Ok. MErci.
Je vais me renseigner sur GetParameters[...]

---

Note que GetParameter est une fonction perso de ma dal. En fait, pour ajouter un paramètre à la commande, on doit passer par la méthode Append de ADODB.Command qui reçoit un objet de type ADODB.Parameter créé par... La fonction CreateParameter de... l'objet ADODB.Command :aie: (Ils sont fous ces adodbiens, mais c'est ainsi).

Donc, pour créer et ajouter un paramètre à une ADODB.Command, on utilise ceci:

Code:

---

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

Sub TestAdodb() Dim cn As ADODB.Connection Dim cm As ADODB.Command Dim rs As ADODB.Recordset Dim sql As String Dim ContactName As String   Set cn = New ADODB.Connection Set cm = New ADODB.Command   cn.Open "..." sql = "Select * from Contact where longname = ?" ContactName = "Tartemption"   With cm .ActiveConnection = cn .CommandText = sql .Parameters.Append cm.CreateParameter("p1", adVarChar, adParamInput, 255, ContactName) Set rs = cm.Execute End With End Sub

---

C'est une des raisons qui m'ont poussé à créer une surcouche d'abstraction de ADODB via le module que j'appelle dal et qui permet une manipulation que j'estime plus simple des objets ADODB, lorsque l'on doit y revenir souvent en tout cas ;)

Citation:

---

Note que GetParameter est une fonction perso de ma dal.

---

Oui oui, bien sur.
Erreur dans ma réponse, je pensais CreateParameters, et c'est bien ce que j'ai consulté (ici (entre autre) : https://docs.microsoft.com/fr-fr/sql...l-server-ver15)

Le modèle d'objet ADODB, pour quelqu'un qui n'a jamais mis les pieds dedans est très complexe.
Il me faudra du temps pour m'y familiariser...
Mais bon, c'est le jeu ma pauvre Lucette :D

Je considère ce sujet résolu puisque la réponse à la question originelle a été apportée.

Merci encore, Pierre et, à bientôt.