Discussion :

[francis60]

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

[Invité]

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

[francis60]

Cher membre émérite⁽¹⁾

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

[francis60]

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

[francis60]

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.

[francis60]

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

[Invité]

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!

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

[francis60]

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

[francis60]

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

[francis60]

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

[Invité]

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!

[francis60]

Comment actualiser la requête ?

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

[francis60]

tu n'auras jamais une actualisation implicite!

Je veux bien d'une actualisation explicite alors, si tu pouvais l'expliciter.

[francis60]

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.

[Invité]

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!

[francis60]

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.

[Invité]

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

[francis60]

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 ?

[Invité]

Bonsoir,
Poste un classeur avec de données bidonnées!

[francis60]

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