Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

  1. #1
    Futur Membre du Club
    connexions multiples à une base de donnée grâce au modèle ADO
    Bonjour a tous,

    J'ai ce code qui permet d'interroger une base de donnée(ici des fichiers excels) avec le modèle ADO ( un grand merci à SilkyRoad). Le code fonctionne mais je compte le mettre dans une macro complémentaire. Elle est destiné à 6 utilisateurs, étant donné je n'ai jamais cela avant, est ce que vous sauriez comment ça va se comporter en cas de connexion multiple à un même fichier par exemple ? ou si le modèle ADO gère bien ce cas de figure

    Si vous avez d'autres remarques, je suis preneuse

    Mercii

    Lucy

    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
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    Public Sub extractionValeurCelluleClasseurFerme(ticker As String, champs As String, dateD As String, dateF As String, rng As Range)
        Dim Source As Object 'As ADODB.Connection
        Dim Rst As Object 'As ADODB.Recordset
        Dim ADOCommand As Object 'As ADODB.Command
        Dim Fichier As String, Feuille As String
        Dim listChamps() As String, nbChamps As Integer
        Dim champs0, champs1, champs2, champs3, champs4, champs5 As String
     
        Feuille = "Feuil1$"
        Fichier = "C:\Users\" & ticker & ".xlsx"
        listChamps() = Split(champs, ";")
        nbChamps = UBound(listChamps())
     
        Set Source = CreateObject("ADODB.Connection")
        Source.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Fichier + ";Extended Properties=Excel 12.0;"
     
        Set ADOCommand = CreateObject("ADODB.Command")
        With ADOCommand
            .ActiveConnection = Source
            Select Case nbChamps
            Case 0
                champs0 = listChamps(0)
            .CommandText = "SELECT DateValue,[" & champs0 & "] FROM [" & Feuille & "] WHERE DateValue>= #" & dateD & "# And DateValue <= #" & dateF & "# GROUP BY DateValue,[" & champs0 & "]"
            Case 1
                champs0 = listChamps(0)
                champs1 = listChamps(1)
             .CommandText = "SELECT DateValue,[" & champs0 & "],[" & champs1 & "] FROM [" & Feuille & "] WHERE DateValue>= #" & dateD & "# And DateValue <= #" & dateF & "# GROUP BY DateValue,[" & champs0 & "],[" & champs1 & "]"
            Case 2
                champs0 = listChamps(0)
                champs1 = listChamps(1)
                champs2 = listChamps(2)
            .CommandText = "SELECT DateValue,[" & champs0 & "],[" & champs1 & "],[" & champs2 & "] FROM [" & Feuille & "] WHERE DateValue>= #" & dateD & "# And DateValue <= #" & dateF & "# GROUP BY DateValue,[" & champs0 & "],[" & champs1 & "],[" & champs2 & "]"
            Case 3
                champs0 = listChamps(0)
                champs1 = listChamps(1)
                champs2 = listChamps(2)
                champs3 = listChamps(3)
            .CommandText = "SELECT DateValue,[" & champs0 & "],[" & champs1 & "],[" & champs2 & "],[" & champs3 & "] FROM [" & Feuille & "] WHERE DateValue>= #" & dateD & "# And DateValue <= #" & dateF & "# GROUP BY DateValue,[" & champs0 & "],[" & champs1 & "],[" & champs2 & "],[" & champs3 & "]"
            Case 4
                champs0 = listChamps(0)
                champs1 = listChamps(1)
                champs2 = listChamps(2)
                champs3 = listChamps(3)
                champs4 = listChamps(4)
            .CommandText = "SELECT DateValue,[" & champs0 & "],[" & champs1 & "],[" & champs2 & "],[" & champs3 & "],[" & champs4 & "] FROM [" & Feuille & "] WHERE DateValue>= #" & dateD & "# And DateValue <= #" & dateF & "# GROUP BY DateValue,[" & champs0 & "],[" & champs1 & "],[" & champs2 & "],[" & champs3 & "],[" & champs4 & "]"
            Case 5
            .CommandText = "SELECT * FROM [" & Feuille & "] WHERE DateValue>= #" & dateD & "# And DateValue <= #" & dateF & "#"
            End Select
        End With
     
        Set Rst = CreateObject("ADODB.Recordset")
     
        'Rst.Open ADOCommand, , adOpenForwardOnly, adLockReadOnly
        Rst.Open ADOCommand, , adOpenStatic, adLockReadOnly
        Set Rst = Source.Execute(ADOCommand.CommandText)
     
        rng.Offset(1, 0).CopyFromRecordset Rst
        Rst.Close
        Source.Close
        Set Source = Nothing
        Set Rst = Nothing
        Set ADOCommand = Nothing
    End Sub

  2. #2
    Membre habitué
    Bonjour,

    Théoriquement cela ne devrait pas fonctionner, car si je ne me trompe pas un fichier Excel ne peut admettre qu'une seule connexion externe, c'est écrit dans le tuto.

    ONTAYG

  3. #3
    Futur Membre du Club
    Bonjour,

    Merci du retour!

    Oui effectivement j'ai revue le tutoriel mais cela veut dire que tant que l'utilisation n'est pas simultanée cela devrait fonctionner

    Connaissez vous peut être un autre modèle qui permettrait les connexions multiples ?

    Lucy

  4. #4
    Membre habitué
    Bonjour,

    C'est le rôle des bases de données, Excel est un tableau.

    ONTAYG

  5. #5
    Futur Membre du Club
    D'accord c'est noté !!

    Merci pour tes retours

    Lucy

  6. #6
    Nouveau membre du Club
    Bonjour deTouteBoT,

    Je ne connais pas ADO mais de se que j'ai compris, je peux te proposer 2 pistes de réflexion qui seront à confirmer :

    - Détection de l'ouverture en écriture/lecture seule :
    Excel permet de détecter si un fichier excel est ouvert en lecture seule.A vérifier si on peut l'appliquer à ADO. Il te suffirait alors d'annuler ta commande, la renouveler ultérieurement (ex : timer)
    (Workbooks(Thisworkbook.Name).Readonly = True quand lecture seule)

    - Fichier Excel partagé :
    Alors là par contre, il faut prendre de grosse pincette parce que ca devient le bordel et tu as 2 types de fichiers partagé :
    Local (à "l'ancienne") : qui permet la connexion multiple mais je ne peux pas te dire ce qui marchera ou pas.
    Sharepoint/Teams : qui permet aussi la connexion multiple mais la gestion/modification reste limité.

    Voilà 2 pistes de recherche pour toi, je suis désolé mais sans connaître ADO, je ne peux pas en conseiller une en particulier.
    Peut-être les autres pourront te renseigner plus.


    Et je suis d'accords avec ONTAYG : Excel est un tableur.
    On peut se rapprocher d'une BDD ultra simplifié avec l'aide des macros, mais ce n'est pas un outil de gestion de base de données.
    Voir ACCESS.
    Faites pas comme les anglais avec Excel qui n'a pas assez de ligne.

    Bon courage,
    Zeabon

  7. #7
    Responsable
    Office & Excel

    Salut.

    Tu ne donnes pas ta version d'Excel mais à partir d'Excel 2010, tu devrais utiliser Power Query pour rapatrier tes données dans chaque classeur de travail. Ca demande 0 lignes de codes en VBA et ça permet les accès multiples. Ca permet d'éviter l'adodb sur de l'excel, dont je suis loin d'être fan.




    Cela étant, je ne comprends pas trop bien ton problème. La connexion n'est ouverte que le temps du transfert, donc il y a peu de chance que les 6 utilisateurs lancent la requête en même temps. Le conflit éventuel se gère avec On Error Goto qui permet d'informer proprement l'utilisateur, mais il faudrait que le temps de transfert soit super long que le risque de collision soit réel, non?


    Cela étant, Excel n'est normalement pas fait pour cela, car comme le dit ONTAYG, Excel est un tableur et pas un SGBD (système de gestion de bases de données). Power Query permet toutefois de nuancer un peu ce propos à l'heure actuelle et il est possible, pour de petites structures, de monter un système avec des fichiers de données
    "Plus les hommes seront éclairés, plus ils seront libres" (Voltaire)
    ---------------
    Mes remarques et critiques sont purement techniques. Ne les prenez jamais pour des attaques personnelles...
    Le VBA ne palliera jamais une mauvaise conception de classeur ou un manque de connaissances des outils natifs d'Excel...
    Ce ne sont pas des bonnes pratiques parce que ce sont les miennes, ce sont les miennes parce que ce sont des bonnes pratiques
    VBA pour Excel? Pensez D'ABORD en EXCEL avant de penser en VBA...
    Vous avez apprécié la réponse? =>
    ---------------

  8. #8
    Membre éclairé
    bonsoir,
    si le fichier Excel est en mode partagé le 6 utilisateurs pourront ce connecté au même instant sans problème!

  9. #9
    Responsable
    Office & Excel

    Salut Thumb

    Si on est en 365 et que le fichier est sur un onedrivre, il n'y a pas de problèmes à le partager, car techniquement, cela fonctionne. Dans la mesure où, selon le code donné dans le message initial, il s'agit uniquement de lire les données, c'est une configuration qui pourrait fonctionner.

    Je suis plus dubitatif sur un partage à 6 en lecture-écriture même si, là aussi, c'est techniquement possible. As-tu de ton côté des retours d'expérience à ce sujet? Ton avis m'intéresse grandement.
    "Plus les hommes seront éclairés, plus ils seront libres" (Voltaire)
    ---------------
    Mes remarques et critiques sont purement techniques. Ne les prenez jamais pour des attaques personnelles...
    Le VBA ne palliera jamais une mauvaise conception de classeur ou un manque de connaissances des outils natifs d'Excel...
    Ce ne sont pas des bonnes pratiques parce que ce sont les miennes, ce sont les miennes parce que ce sont des bonnes pratiques
    VBA pour Excel? Pensez D'ABORD en EXCEL avant de penser en VBA...
    Vous avez apprécié la réponse? =>
    ---------------

  10. #10
    Membre éclairé
    Bonjour Pierre,
    En fait le problème en en écriture c'est de savoir verouller un enregistrement car les 6 personnes voient les modifications en même temps !

    Autres problèmes est qu'Excel demande si on veut enregistrer les modifications à la fermeture du fichier car il a peut-être été modifié par un autre.

    Si on dit non les modifications sont perdus et mon voisin qui vient de saisir pendant une heure se tire les cheveux !

    C'est bien ce qui a été dit plus haut sur les Sgbd

  11. #11
    Futur Membre du Club
    Bonjour à tous!

    Merci pour toutes vos réponses

    Zeabon, j'ai regardé il existe bien un code permettant de vérifier si il y a une connexion au classeur donc cela devrait être faisable, je n'ai pas encore testé les fichiers partagés mais je vais me renseigner davantage
    Oui je conçois totalement que c'est pas sa fonction première mais ne pas utiliser une bd est plus une contrainte qu'un choix! mercii

    Pierre Fauconnier, oui on en m'a parlé à plusieurs reprises je devrais donc m'y mettre, enfaite cette procédure va dans une fonction Excel donc je ne sais si je peux allier les deux
    Oui c'est ce que je pense aussi mais vu que c'est la première fois que je vais tester un code comme ça, je voulais un petit retour d'expérience mais une simple procédure d'erreur devrait le faire

    Thumb down, je vois bien le concept de fichier partagé mais une autre problématique se pose pour moi, les fichiers que j'interroge sont aussi crée par une autre macro, peut on créer des fichiers excels partagés par vba ?
    Après, mon but reste la lecture uniquement mais je garde en tete tous ça

    Lucy
    (XL16)

  12. #12
    Membre éclairé
    Le seul problème dans les connections simultanée à un même fichier c'est qu'un message demande la confirmation de lecture seule puis affiche le classeur !

    Si ton but est uniquement de lire les données de ton fichier, il te faut tout simplement définir ta connexion en read only et tout deviendra transparent !

    "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & SourceFile & ";Mode=Read;Extended Properties=""Excel 12.0;HDR=Yes;"";"

  13. #13
    Nouveau membre du Club
    Citation Envoyé par Thumb down Voir le message

    Si ton but est uniquement de lire les données de ton fichier, il te faut tout simplement définir ta connexion en read only et tout deviendra transparent !
    Je suis D'accords avec Thumb là-dessus.

    J'ai déjà créé des pseudos BDD avec Excels en utilisant une fichier interface séparé.
    9/10ième de mes connexions à la BDD étaient en lecture seule pour éviter les ouvertures en modification simultanée.

    La solution de détecter si le fichier est déjà ouvert est utile seulement si tu souhaites y accéder en modifications pour écrire/modifier/supprimer quelque chose.

    Zeabon

  14. #14
    Membre éclairé
    Bonjour,
    Juste un petit détail le delete, comme le create table,drop table etc... N'existe pas en ado Excel !

  15. #15
    Futur Membre du Club
    Bonjour,

    Thumb down, je pense que c'est exactement ce qu'il me fallait!! Je prends vos remarques en note ça me servira surement merci

    J'aurais encore besoin de vos lumières à propos de mon code je viens de remarquer un petit bug

    quelque chose au niveau des dates qui n'allaient pas, quand je rentre des dates dont le nombre de jour est inférieur à 12, elle considère la date au format anglais

    (les fichiers sources sont au format français)

    Code :Sélectionner tout -Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
     
     
    Sub test()
    Dim rng As Range
    ticker = "flute"
    champs = "Arg1"
    dateD = "05/03/2013"
    dateF = "25/03/2014"
    Set rng = ThisWorkbook.Sheets("Feuil2").Range("A1")
    Call extractionValeurCelluleClasseurFerme(CStr(ticker), CStr(champs), CStr(dateD), CStr(dateF), rng)
     
    End Sub


    La date de début qu'elle me sort est le "03/05/2014", par contre elle me sort bien la bonne date de fin, auriez vous une idée ?

    Je pense que le souci vient de l'utilisation du format date de sql
    Code :Sélectionner tout -Visualiser dans une fenêtre à part
    "SELECT DateValue,[" & champs0 & "] FROM [" & Feuille & "] WHERE DateValue>= #" & dateD & "# And DateValue <= #" & dateF & "# GROUP BY DateValue,[" & champs0 & "]"


    j'ai réfléchis à une solution en inversant les jours et mois dès lors que le nombre de jour est inférieur à 12 mais il y a un moyen plus propre de le faire je suis preneuse!

    Merci à vous

    Lucy

  16. #16
    Membre éclairé
    Bonjour,
    Essais comme ça
    Code :Sélectionner tout -Visualiser dans une fenêtre à part
    DateValue>= #" & format(dateD,"yyyy-mm-dd") & "#

    Le mieux pour éviter ce genre de désagrément, c'est de faire des requêtes paramétrable {Adodb.Command}.

  17. #17
    Futur Membre du Club
    ça marche nickel !!! merci

    Qu'entends tu par des requêtes paramétrables ?

    J'aurais une autre question, le fait de créer des objets tels que

    Code :Sélectionner tout -Visualiser dans une fenêtre à part
    Set Source = CreateObject("ADODB.Connection")


    évite bien de devoir cocher manuellement les références à ADO ?

    Lucy

  18. #18
    Membre éclairé
    Code :Sélectionner tout -Visualiser dans une fenêtre à part
    dim nc as New  ADODB.Connection

    Cette forme décrire implique ADODB.Connection soit connu dans les références du projet {menu=>outils=>références=>ActiveX data Object}

    Alors qu'avec creatobject ("ADODB.Connection") tu utilises la version installée sur ton ordinateur et tu t'affrenchi des références. Mais tu perds l'accès à la saisie intuitive et les constantes Ado.

  19. #19
    Responsable
    Office & Excel

    Salut.

    Perso, utiliser la liaison tardive (donc sans référencer la bibliothèque ADO) n'est pertinent que si ton code est susceptible d'être démarré de différentes machines, et encore. Je n'ai pas rencontré ces dernières années de problèmes à utiliser la référence 2.7 de ADO. Cette façon de procéder te pénalise lors de la saisie du code, surtout pour des requêtes paramétrées, car tu ne disposes effectivement pas de la saisie semi-automatique et des constantes ADO comme le dit Thumb, mais tu te prives également des tests de compilation car les erreurs de code ne surviendront qu'à l'exécution.

    Une requête paramétrée est une requête dans laquelle on remplace certaines valeurs (notamment celles de la clause WHERE) par des paramètres, c'est-à-dire des variables que l'on spécifie hors de la requête sql. Un exemple vaut mieux qu'un long discours.

    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
    Sub Test()
      Dim cn As ADODB.Connection
      Dim cm As ADODB.Command
      Dim pm As ADODB.Parameter
      Dim rs As ADODB.Recordset
      Dim Filename As String
      Dim Date1 As Date, Date2 As Date
      Dim sql As String
     
      Date1 = DateSerial(2020, 1, 1)
      Date2 = DateSerial(2020, 1, 31)
     
      Filename = "c:\data\temp\source-odbc.xlsx"
      sql = "select * from [feuil1$] where date >=? and date <=?"
      Set cn = New ADODB.Connection
      cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Filename + ";Extended Properties=Excel 12.0;"
      Set cm = New ADODB.Command
      cm.ActiveConnection = cn
      cm.CommandText = sql
      cm.Parameters.Append cm.CreateParameter("p1", adDate, adParamInput, 4, Date1)
      cm.Parameters.Append cm.CreateParameter("p2", adDate, adParamInput, 4, Date2)
      Set rs = cm.Execute()
    End Sub


    Dans la requête sql de l'exemple, j'ai remplacé les dates par des ?. La commande sql reçoit les deux dates en paramètres. C'est le moteur ADO qui remplacera les ? par les valeurs des paramètres en fonction du type de paramètre renseigné. ADO pour excel n'acceptant les paramètres nommés, il est indispensable de créer les paramètres dans l'ordre de leur apparition dans la requête SQL!


    L'avantage est que tu n'as pas à te préoccuper de la façon dont tu dois passer les valeurs (comme dans ton exemple, passer les dates au format mm-dd-yyyy et les encadrer par des #). L'utilisation des paramètres permet également de passer facilement du texte sans devoir toi-même échapper les quelques caractères qui posent problème (l'apostrophe, les jokers % et _, ...). Ca permet également d'avoir un texte de requête plus simple à gérer car tu ne dois pas concaténer toutes tes variables...

    Que du bonheur

    J'ai écrit 4 billets sur le sujet (c'est pour connecter Excel et Access, mais dans les grandes lignes, c'est ok pour Excel). Voici le lien vers le premier des quatre.


    En plus des restrictions "CRUD" dont parle Thumb, le problème de ADO pour Excel est que les jointures sont limitées et que les champs sont typés par Excel en fonction des 8 premières lignes récupérées du recordset. Perso, je n'aime pas du tout travailler avec ADO pour Excel, et je lui préfère de loin le rapatriement des données en VBA suivi d'un traitement local ou, surtout sur les versions actuelles de l'outil, Power Query qui devient pour moi incontournable et à prendre en mains rapidement pour booster ta productivité.
    "Plus les hommes seront éclairés, plus ils seront libres" (Voltaire)
    ---------------
    Mes remarques et critiques sont purement techniques. Ne les prenez jamais pour des attaques personnelles...
    Le VBA ne palliera jamais une mauvaise conception de classeur ou un manque de connaissances des outils natifs d'Excel...
    Ce ne sont pas des bonnes pratiques parce que ce sont les miennes, ce sont les miennes parce que ce sont des bonnes pratiques
    VBA pour Excel? Pensez D'ABORD en EXCEL avant de penser en VBA...
    Vous avez apprécié la réponse? =>
    ---------------

  20. #20
    Membre éclairé
    Bonjour Pierre,
    Je rajouterai encore un bémol à l'utilisation d'Ado pour Excel Ado n'a pas été implémenté pour des versions supérieur à 2004 il ne dépasse pas la taille d'un xls!

###raw>template_hook.ano_emploi###