Discussion :

[pijaku]

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 :

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

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 :

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 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

[Pierre Fauconnier]

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.





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)

[pijaku]

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+

[pijaku]

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

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
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 :

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

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?

[Pierre Fauconnier]

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 :

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

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 :

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

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.

[pijaku]

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"?

[Pierre Fauconnier]

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 (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 :

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
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

[pijaku]

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

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

Merci encore, Pierre et, à bientôt.

[Pierre Fauconnier]

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 :

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

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 :

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
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 :

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

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 :

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

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

[pijaku]

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".