1. #1
    Membre habitué Avatar de francis60
    Homme Profil pro
    Aide à la décision, modélisation coût/process
    Inscrit en
    août 2011
    Messages
    129
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Oise (Picardie)

    Informations professionnelles :
    Activité : Aide à la décision, modélisation coût/process
    Secteur : Conseil

    Informations forums :
    Inscription : août 2011
    Messages : 129
    Points : 138
    Points
    138

    Par défaut Actualisation sql connection interne au classeur obligé de fermer le classeur à chaque mise à jour

    Bonjour à tous,

    J'ai un classeur .xlsm qui contient une requête SQL sur les données internes de ce même classeur.
    Lorsque je modifie les données des tables sur lesquelles je fait une requête de jointure, le résultat de la requête ne tient pas compte des données modifiées.
    Je suis obligé de sauvegarder et de fermer le classeur et de le rouvrir à chaque mise à jour.
    Si je sauvegarde simplement sans fermer, la mise à jour n'est pas prise en compte et la requête sql retourne les anciens résultats.
    Une remarque: la première requête dure quelque secondes et les mises à jour (ratées) 2/10ème de secondes.

    Auriez-vous une explication et une solution au mystère de la connexion rémanente?

    Merci,

    Francis


    La procédure qui prend en paramètre la requête et la plage de destination:
    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
    Sub RunSQL_Range(ByVal rSQL As String, ByVal rDest As Range)
     
    Dim Conn As ADODB.Connection
    Dim Rst As ADODB.Recordset
    Dim Fichier As String, Direction As String
     
        Set Conn = New ADODB.Connection
        With Conn
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .ConnectionString = "Data Source=" & ThisWorkbook.Path & "\" & ThisWorkbook.Name & _
        ";Extended Properties=""Excel 12.0;HDR=YES"";"
        .Open
        End With
     
     
        Set Rst = New ADODB.Recordset
        With Rst
        .ActiveConnection = Conn
        .Open rSQL, , adOpenKeyset, adLockOptimistic, adCmdTableDirect
        End With
     
        rDest.CopyFromRecordset Rst
     
        Rst.Close
        Conn.Close
     
        Set Conn = Nothing
        Set Rst = Nothing
     
    End Sub
    rSQL = SELECT `Raw material producer`, `Material type`, `Commercial name`, Region, `Quater` FROM [Project$A1:G37], [Quaters$A1:A7] WHERE `Commercial name` IS NOT NULL AND `Quater` IS NOT NULL GROUP BY Region, `Raw material producer`, `Material type`, `Commercial name`, Region, `Quater` ORDER BY `Quater`, `Raw material producer`, `Material type`, `Commercial name`, Region

  2. #2
    Membre émérite Avatar de dysorthographie
    Homme Profil pro
    Ingénieur développement logiciels
    Inscrit en
    septembre 2016
    Messages
    1 687
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Charente Maritime (Poitou Charente)

    Informations professionnelles :
    Activité : Ingénieur développement logiciels
    Secteur : Industrie

    Informations forums :
    Inscription : septembre 2016
    Messages : 1 687
    Points : 2 868
    Points
    2 868

    Par défaut

    Bonsoir,
    Je ne vois n'y connexion interne, n'y requête interne dans ce code!

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    rSQL = "SELECT DISTINCT [Raw material producer] [Material type], [Commercial name], [Region], [Quater] FROM [Project$A1:G37], [Quaters$A1:A7] WHERE [Commercial name] IS NOT NULL AND [Quater] IS NOT NULL ORDER BY [Quater[, [Raw material producer], [Material type], [Commercial name], [Region]"
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    .ConnectionString = "Data Source=" & ThisWorkbook.FullName  & _
    Il dit non avec la tête
    mais il dit oui avec le coeur
    il dit oui à ce qu’il aime
    il dit non au professeur {Jacques PRÉVERT}

  3. #3
    Membre habitué Avatar de francis60
    Homme Profil pro
    Aide à la décision, modélisation coût/process
    Inscrit en
    août 2011
    Messages
    129
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Oise (Picardie)

    Informations professionnelles :
    Activité : Aide à la décision, modélisation coût/process
    Secteur : Conseil

    Informations forums :
    Inscription : août 2011
    Messages : 129
    Points : 138
    Points
    138

    Par défaut

    Cher membre émérite(1)

    Tu me conseilles d'utiliser plutôt des brackets [ ] au lieu des ` ` pour encadrer les noms de champs et "ThisWorkbook.FullName" plutôt que "ThisWorkbook.Path & "\" & ThisWorkbook.Name".

    Merci, je vais tester ça.

    Je ne vois n'y connexion interne, n'y requête interne dans ce code!
    Pour moi connexion interne et requête interne signifiaient simplement que le code qui lance la requête SQL est placé dans la même classeur qui contient les données (ThisWorkbook).

    J'ai tout de même l'impression qu'un classeur fantôme est resté en mémoire lors de la première connection et que, malgré les modifications de données dans le classeur actif, la requête continue d’interroger ce classeur obsolète.

    Francis (membre habitué)

    émérite (adj.)
    1.qui a une longue pratique, une expérience lui conférant une grande habileté.
    2.(vieux)honoré d'un titre dont on n'exerce pas ou plus la fonction (ex: professeur honoraire).

  4. #4
    Membre habitué Avatar de francis60
    Homme Profil pro
    Aide à la décision, modélisation coût/process
    Inscrit en
    août 2011
    Messages
    129
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Oise (Picardie)

    Informations professionnelles :
    Activité : Aide à la décision, modélisation coût/process
    Secteur : Conseil

    Informations forums :
    Inscription : août 2011
    Messages : 129
    Points : 138
    Points
    138

    Par défaut

    dysorthographie, j'ai vu aussi que tu n'avais pas repris le GROUP BY dans le SQL, je vais voir ce que ça change.

  5. #5
    Membre habitué Avatar de francis60
    Homme Profil pro
    Aide à la décision, modélisation coût/process
    Inscrit en
    août 2011
    Messages
    129
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Oise (Picardie)

    Informations professionnelles :
    Activité : Aide à la décision, modélisation coût/process
    Secteur : Conseil

    Informations forums :
    Inscription : août 2011
    Messages : 129
    Points : 138
    Points
    138

    Par défaut

    Citation Envoyé par dysorthographie Voir le message
    Bonsoir,
    Je ne vois n'y connexion interne, n'y requête interne dans ce code!
    J'ai testé toutes tes propositions mais hélas le problème persiste.
    De plus lorsque j'ouvre à nouveau le classeur, XL propose ReadOnly ou Notify, comme si il était encore ouvert par un autre processus.

    Si je sauvegarde des données modifiées avec Save et que j'ouvre le classeur sauvegardé dans un autre cession XL, les données du classeur sont bel et bien actualisées; pourtant, dans la première cession, la requête donne toujours les résultats obsolètes de sa première activation bien qu'elle ait été close.

  6. #6
    Membre habitué Avatar de francis60
    Homme Profil pro
    Aide à la décision, modélisation coût/process
    Inscrit en
    août 2011
    Messages
    129
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Oise (Picardie)

    Informations professionnelles :
    Activité : Aide à la décision, modélisation coût/process
    Secteur : Conseil

    Informations forums :
    Inscription : août 2011
    Messages : 129
    Points : 138
    Points
    138

    Par défaut

    Dois-je renoncer au SQL et réécrire tout en pur VBA, si je souhaite une exécution dynamique des requêtes ?

  7. #7
    Membre émérite Avatar de dysorthographie
    Homme Profil pro
    Ingénieur développement logiciels
    Inscrit en
    septembre 2016
    Messages
    1 687
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Charente Maritime (Poitou Charente)

    Informations professionnelles :
    Activité : Ingénieur développement logiciels
    Secteur : Industrie

    Informations forums :
    Inscription : septembre 2016
    Messages : 1 687
    Points : 2 868
    Points
    2 868

    Par défaut

    bonjour,
    Pour moi connexion interne et requête interne signifiaient simplement que le code qui lance la requête SQL est placé dans la même classeur qui contient les données (ThisWorkbook).
    un code est exécuté quans on lui donne l'ordre de s'exécuté! donc un fois si un valeur change dans le classeur le code ne s'éxécutera pas une autre fois!
    Citation Envoyé par francis60 Voir le message
    dysorthographie, j'ai vu aussi que tu n'avais pas repris le GROUP BY dans le SQL, je vais voir ce que ça change.
    le group by permet de faire des requête de statisique Count,sum etc... pas pour dédoublonner! ça prend beaucoup de ressource pour rien un select Distinct fait l'affaire!

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    Sub RunSQL_Range(ByVal rSQL As String, ByVal rDest As Range)
    With ADODB.Connection
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .ConnectionString = "Data Source=" & ThisWorkbook.FullName & ";Extended Properties=""Excel 12.0;HDR=YES"";"
        .Open
        rDest.CopyFromRecordset .Execute(rSQL)
        .Close
    End With
    End Sub
    Il dit non avec la tête
    mais il dit oui avec le coeur
    il dit oui à ce qu’il aime
    il dit non au professeur {Jacques PRÉVERT}

  8. #8
    Membre habitué Avatar de francis60
    Homme Profil pro
    Aide à la décision, modélisation coût/process
    Inscrit en
    août 2011
    Messages
    129
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Oise (Picardie)

    Informations professionnelles :
    Activité : Aide à la décision, modélisation coût/process
    Secteur : Conseil

    Informations forums :
    Inscription : août 2011
    Messages : 129
    Points : 138
    Points
    138

    Par défaut

    Citation Envoyé par dysorthographie Voir le message
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    With New ADODB.Connection
    J'ai utilisé ton code en ajoutant New, mais le problème reste le même > pas d'actualisation la deuxième fois

    Cette fois c'est encore plus étrange, même après avoir fermé excel, la requête donne les anciennes valeurs qui ont été effacées ...

  9. #9
    Membre habitué Avatar de francis60
    Homme Profil pro
    Aide à la décision, modélisation coût/process
    Inscrit en
    août 2011
    Messages
    129
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Oise (Picardie)

    Informations professionnelles :
    Activité : Aide à la décision, modélisation coût/process
    Secteur : Conseil

    Informations forums :
    Inscription : août 2011
    Messages : 129
    Points : 138
    Points
    138

    Par défaut

    Nom : classeur fantome read only.jpg
Affichages : 12
Taille : 48,9 Ko

    Le classeur fantôme qui s'ouvre en read only dans une cession inutilisable...

  10. #10
    Membre habitué Avatar de francis60
    Homme Profil pro
    Aide à la décision, modélisation coût/process
    Inscrit en
    août 2011
    Messages
    129
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Oise (Picardie)

    Informations professionnelles :
    Activité : Aide à la décision, modélisation coût/process
    Secteur : Conseil

    Informations forums :
    Inscription : août 2011
    Messages : 129
    Points : 138
    Points
    138

    Par défaut

    A tout hasard les références que j'utilise:

    Nom : myReferences.jpg
Affichages : 11
Taille : 63,7 Ko

  11. #11
    Membre émérite Avatar de dysorthographie
    Homme Profil pro
    Ingénieur développement logiciels
    Inscrit en
    septembre 2016
    Messages
    1 687
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Charente Maritime (Poitou Charente)

    Informations professionnelles :
    Activité : Ingénieur développement logiciels
    Secteur : Industrie

    Informations forums :
    Inscription : septembre 2016
    Messages : 1 687
    Points : 2 868
    Points
    2 868

    Par défaut

    J'ai utilisé ton code en ajoutant New, mais le problème reste le même > pas d'actualisation la deuxième fois
    tu n'auras jamais une actualisation implicite!
    Il dit non avec la tête
    mais il dit oui avec le coeur
    il dit oui à ce qu’il aime
    il dit non au professeur {Jacques PRÉVERT}

  12. #12
    Membre habitué Avatar de francis60
    Homme Profil pro
    Aide à la décision, modélisation coût/process
    Inscrit en
    août 2011
    Messages
    129
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Oise (Picardie)

    Informations professionnelles :
    Activité : Aide à la décision, modélisation coût/process
    Secteur : Conseil

    Informations forums :
    Inscription : août 2011
    Messages : 129
    Points : 138
    Points
    138

    Par défaut

    Comment actualiser la requête ?

    Je ne souhaite pas une actualisation dynamique mais comment faire la mise à jour.

  13. #13
    Membre habitué Avatar de francis60
    Homme Profil pro
    Aide à la décision, modélisation coût/process
    Inscrit en
    août 2011
    Messages
    129
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Oise (Picardie)

    Informations professionnelles :
    Activité : Aide à la décision, modélisation coût/process
    Secteur : Conseil

    Informations forums :
    Inscription : août 2011
    Messages : 129
    Points : 138
    Points
    138

    Par défaut

    Citation Envoyé par dysorthographie Voir le message
    tu n'auras jamais une actualisation implicite!
    Je veux bien d'une actualisation explicite alors, si tu pouvais l'expliciter.

  14. #14
    Membre habitué Avatar de francis60
    Homme Profil pro
    Aide à la décision, modélisation coût/process
    Inscrit en
    août 2011
    Messages
    129
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Oise (Picardie)

    Informations professionnelles :
    Activité : Aide à la décision, modélisation coût/process
    Secteur : Conseil

    Informations forums :
    Inscription : août 2011
    Messages : 129
    Points : 138
    Points
    138

    Par défaut

    Une précision: les données sont modifiées directement dans Excel par une saisie dans les cellules et ensuite je souhaite relancer le query pour mettre à jour le résultat en fonction de ces nouvelles valeurs d'entrée.

  15. #15
    Membre émérite Avatar de dysorthographie
    Homme Profil pro
    Ingénieur développement logiciels
    Inscrit en
    septembre 2016
    Messages
    1 687
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Charente Maritime (Poitou Charente)

    Informations professionnelles :
    Activité : Ingénieur développement logiciels
    Secteur : Industrie

    Informations forums :
    Inscription : septembre 2016
    Messages : 1 687
    Points : 2 868
    Points
    2 868

    Par défaut

    Petite pression, adodb ne voit que les données effectivement sauvegardées! Si tu modifiées ton fichier excel les modifications ne seront vues que si tu sauvegardes ton fichier!

    Tu peux rappeler ton code sur l'événement change de ThisWorkbook!
    Il dit non avec la tête
    mais il dit oui avec le coeur
    il dit oui à ce qu’il aime
    il dit non au professeur {Jacques PRÉVERT}

  16. #16
    Membre habitué Avatar de francis60
    Homme Profil pro
    Aide à la décision, modélisation coût/process
    Inscrit en
    août 2011
    Messages
    129
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Oise (Picardie)

    Informations professionnelles :
    Activité : Aide à la décision, modélisation coût/process
    Secteur : Conseil

    Informations forums :
    Inscription : août 2011
    Messages : 129
    Points : 138
    Points
    138

    Par défaut

    Citation Envoyé par dysorthographie Voir le message
    Petite pression, adodb ne voit que les données effectivement sauvegardées! Si tu modifiées ton fichier excel les modifications ne seront vues que si tu sauvegardes ton fichier!

    Tu peux rappeler ton code sur l'événement change de ThisWorkbook!
    Moi aussi je prendrais bien une petite pression bien fraîche...

    Sinon, lorsque je sauvegarde le fichier .xlsm avec les données modifiées et que je relance mon code, il retourne les anciennes données bien que ces données soient effectivement mises à jour (j'ai vérifié en ouvrant le fichier dans une seconde cession).

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
        .Open
        rDest.CopyFromRecordset .Execute(rSQL)
        .Close
    Ton code laisse des paramètres par défaut que je ne maîtrise pas.

    Je vais lire le tuto "Comprendre les Recordset ADO"
    IV. Les Curseurs
    IV-A. Positionnement (CursorLocation)
    IV-A-1. Curseur côté serveur (adUseServer)
    IV-A-2. Curseur côté client (adUseClient)
    IV-B. Fonctionnalités (bibliothèque de curseur)
    IV-B-1. Verrouillage (LockType)
    Quel verrou choisir ?
    Le verrouillage pessimiste (adLockPessimistic
    Le verrouillage optimiste (adLockOptimistic)
    D'autres modes de verrouillage
    IV-B-2. Type de curseur (CursorType)
    En avant seulement (adOpenForwardOnly)
    Statique (adOpenStatic)
    Jeu de clé (adOpenKeyset)
    Dynamique (adOpenDynamic)
    Je pense que les paramètres mystérieux de
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    .Open rSQL, , adOpenKeyset, adLockOptimistic, adCmdTableDirect
    doivent être mieux choisis. Il faudrait une connection asynchrone qui se libère dès l’instruction close.

  17. #17
    Membre émérite Avatar de dysorthographie
    Homme Profil pro
    Ingénieur développement logiciels
    Inscrit en
    septembre 2016
    Messages
    1 687
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Charente Maritime (Poitou Charente)

    Informations professionnelles :
    Activité : Ingénieur développement logiciels
    Secteur : Industrie

    Informations forums :
    Inscription : septembre 2016
    Messages : 1 687
    Points : 2 868
    Points
    2 868

    Par défaut

    Le code que je t'ai donné fait les choses correctement!
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    Sub RunSQL_Range(ByVal rSQL As String, ByVal rDest As Range)
    With ADODB.Connection
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .ConnectionString = "Data Source=" & ThisWorkbook.FullName & ";Extended Properties=""Excel 12.0;HDR=YES"";"
        .Open
        rDest.CopyFromRecordset .Execute(rSQL)
        .Close
    End With
    End Sub
    Il dit non avec la tête
    mais il dit oui avec le coeur
    il dit oui à ce qu’il aime
    il dit non au professeur {Jacques PRÉVERT}

  18. #18
    Membre habitué Avatar de francis60
    Homme Profil pro
    Aide à la décision, modélisation coût/process
    Inscrit en
    août 2011
    Messages
    129
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Oise (Picardie)

    Informations professionnelles :
    Activité : Aide à la décision, modélisation coût/process
    Secteur : Conseil

    Informations forums :
    Inscription : août 2011
    Messages : 129
    Points : 138
    Points
    138

    Par défaut

    Citation Envoyé par dysorthographie Voir le message
    Le code que je t'ai donné fait les choses correctement!
    L'as-tu testé avec une connection sur des données internes au classeur ?

    1. code lancé 1 première fois,
    2. résultat 1,
    3. données modifiées,
    4. classeur sauvegardé sans le fermer,
    5. code lancé une seconde fois,
    6. résultat 2.


    résultat 2 = résultat 1 ?

  19. #19
    Membre émérite Avatar de dysorthographie
    Homme Profil pro
    Ingénieur développement logiciels
    Inscrit en
    septembre 2016
    Messages
    1 687
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Charente Maritime (Poitou Charente)

    Informations professionnelles :
    Activité : Ingénieur développement logiciels
    Secteur : Industrie

    Informations forums :
    Inscription : septembre 2016
    Messages : 1 687
    Points : 2 868
    Points
    2 868

    Par défaut

    Bonsoir,
    Poste un classeur avec de données bidonnées!
    Il dit non avec la tête
    mais il dit oui avec le coeur
    il dit oui à ce qu’il aime
    il dit non au professeur {Jacques PRÉVERT}

  20. #20
    Membre habitué Avatar de francis60
    Homme Profil pro
    Aide à la décision, modélisation coût/process
    Inscrit en
    août 2011
    Messages
    129
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Oise (Picardie)

    Informations professionnelles :
    Activité : Aide à la décision, modélisation coût/process
    Secteur : Conseil

    Informations forums :
    Inscription : août 2011
    Messages : 129
    Points : 138
    Points
    138

    Par défaut

    Bonjour dysorthographie,

    J'ai suivi ton conseil et j'ai refait un classeur anonymisé à partir de zéro pour être sûr de ne pas embarquer de données pro.
    Et là quoi tu te doutes déjà de ce qu'il arriva...
    Et oui ça marche et même mieux : il n'y a pas besoin de sauvegarder le fichier entre les saisies.

    En prime de la solution, j'ai une intuition de l'explication > j'ouvrais une fenêtre non modale en // du code (ce dont je n'avais pas parlé) avec un message que j'actualisais avec userform.Show et userform.Update, du coup cet objet parasite figeait en quelque sorte une version rémanente du classeur dans la mémoire que ADO continuait d'interroger en dépit des valeurs actualisées dans le classeur courant visible à l'écran.

    J'ai supprimé cette fenêtre du coup ça fonctionne à nouveau correctement.

    En fait, je souhaitais informer l'utilisateur avec une sorte d'aide actualisée à la fois comme mode opératoire et info sur les processus en cours (La status bar personne ne la lit jamais sauf le développeur).

    Je vais me contenter de petits msgbox et remettre à plus tard une étude plus sérieuse d'ADO.

    Merci bien donc

    Francis

+ Répondre à la discussion
Cette discussion est résolue.
Page 1 sur 2 12 DernièreDernière

Discussions similaires

  1. [C#][Linq to Sql] Connection a une base de données
    Par Greg84 dans le forum Accès aux données
    Réponses: 1
    Dernier message: 10/09/2008, 16h15
  2. [PL/SQL] connect en SQL dynamique
    Par mbgh1302 dans le forum SQL
    Réponses: 9
    Dernier message: 22/01/2008, 23h54
  3. redefinir java.sql.Connection, c'est possible?
    Par lety247 dans le forum Servlets/JSP
    Réponses: 5
    Dernier message: 07/01/2008, 13h46
  4. Réponses: 1
    Dernier message: 08/02/2007, 10h11
  5. [SQL CONNECT] Cacher son mot de passe ?
    Par Lideln dans le forum PHP & SGBD
    Réponses: 5
    Dernier message: 04/07/2006, 10h49

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo