Fonction VBA qui accède à Access - Fenêtre intempestive "Sélectionner la source de.."

[Davidc57]

Bonjour le forum,

La petite question existentielle du vendredi après-midi

J'ai un programme excel qui va chercher des informations dans Access par el biais de fonction VBA. Concrètement j'ai crée des fonction VBA qui prennent en entrée des paramètres grâce auxquels je construits une requête pour aller interroger Access et récupérer l'information.

Dans excel, j'appelle cette fonction VBA comme on utilise n'importe quelle formule excel - Recherchev(), Somme.si(), ... -

Tout fonctionne très bien, mes feuilles excel sont alimentées, les résultats sont corrects.

Mon seul problème c'est que VBA m'ouvre la fenêtre "Sélectionner la source de données" pour je ne sais pas quelle raison !

Je susi obligé de cliquer sur le bouton "Annuler" de cette fenêtre pour que le programme continue. Notez que je ne fait que cliquer sur "Annuler" dans cette fenêtre, je ne choisit aucune source de données.

Je vous passe certains détails, mais en gros je fais une boucle (28 passages) et à chaque passage dans la boucle la fenêter intempestive s'ouvre une fois. Je dois donc cliquer 28 fois sur le bouton "Annuler" et ca devient insupportable.

Avez-vous des idées ?

Je précise que si j'écrit la formule dans une case pour appeler la fonction VBA, la fenêtre n'apparaît pas. C'est uniquement quand je lance mon programme qui va boucler que celle ci apapraît .... là il y a des mises à jour de certaines cases et donc vba appel légitimement les focntions pour mettre à jour les cases contenant des formules d'accès à ces fonctions.

Merci d'avance de votre soutien,
David

 

[Jam]

Re : Fonction VBA qui accède à Access - Fenêtre intempestive "Sélectionner la source

Bonjour Davidc57,

Tu utilises, si j'ai bien compris, une connexion ADO pour faire fonctionner ton fichier. Le problème provient certainement du fait que la connexion est perdue entre les différents appels, de la requête, d'où les erreurs.
L'exécution de ton programme via un pas à pas devrait te permettre de trouver où se situe le problème.
Sinon, si tu mets à dispo ton code, nous serions très certainement plus à même de t'aider à trouver le problème.

Bon courage.

 

[Davidc57]

Re : Fonction VBA qui accède à Access - Fenêtre intempestive "Sélectionner la source

Bonjour Jam,

Merci de ta réponse que je lit qu'aujourd'hui.

J'ai toujours ce problème. En passant excel en mode 'calculation manuel' en début de code puis en automatique à la fin, je suis parvenu à faire qu'il ne me m'ouvre qu'une seule fois la fenêtre intempestive. Cette solution me convient pour le moment mais j'aimerai bien comprendre d'où cela peut bien venir.

Je vais voir la semaine prochaine si je publier des éléments de mon fichier excel pour illustre mon problème. Ca ne sera pas facile (gros fichiers, beaucoup de feuilles, données confidentielles ... bref y'a du boulot avnt de pouvoir mettre un exemple. Je vais voirce que je peux faire.


Merci encore,
David

 

[Jam]

Re : Fonction VBA qui accède à Access - Fenêtre intempestive "Sélectionner la source

Salut Davidc57,

Ne peux-tu pas au moins nous mettre à dispo le bout de code ou la procédure qui génère cette erreur ?
Car vu ce que tu indiques je ne vois comme ça qu'une source d'erreur possible: la variable de connexion à la BDD est "perdue" en route...d'autre part vu le mode de fonctionnement (intégration des requêtes dans des fonctions de feuilles), il faut que tu lances ta connexion à l'ouverture du fichier et que tu la maintiennes ouverte tant que le fichier Excel est ouvert. A moins de l'ouvrir systématiquement à chaque appel de fonction, mais je pense que ce choix n'est pas le bon. Donc si tu as choisis l'option 1, je pense que tu fermes la connexion à un mauvais moment avant de relancer l'appel à la fonction. Correct ?

Bon courage,

 

[Davidc57]

Re : Fonction VBA qui accède à Access - Fenêtre intempestive "Sélectionner la source

Bonjour Jam,

J'ouvre et je ferme la connexion à la BDD dans la fonction excel.

Voici mon code :

Function PTF_par_Expertise(strExpertise As String, i As String)
' i = indice du PTF dans l'expertise - Voir Table de paramétrage dans Access

Dim db As DAO.Database
Dim rst As Recordset
Dim Sql As String

Set db = OpenDatabase(Sheets("PILOTAGE").Range("strCheminCompletBDDRMM"))

Sql = "SELECT T_Param_Expertise_Fonds_Phares.ID_Fonds_Phares"
Sql = Sql & " FROM T_Param_Expertise_Fonds_Phares"
Sql = Sql & " WHERE (((T_Param_Expertise_Fonds_Phares.Expertise)='" & strExpertise & "') AND ((T_Param_Expertise_Fonds_Phares.Indice_PTF_Expertise)='" & i & "'))"

Set rst = db.OpenRecordset(Sql)

If rst.RecordCount <> 0 Then
    PTF_par_Expertise = rst!ID_Fonds_Phares
Else
    PTF_par_Expertise = ""
End If

rst.Close
db.Close

End Function

Ce code est celui d'une fonction. J'en ai plusieurs mais c'est toujours selon le même principe.

Merci,
David

 

[Jam]

Re : Fonction VBA qui accède à Access - Fenêtre intempestive "Sélectionner la source

Salut David, le forum,

Aïe, ça fait un bout de temps que j'ai pas utilisé DAO depuis XL2K. M'enfin ce n'est pas trop grave.
Juste quelques remarques rapides:
- Comme je l'insinuais dans un post précédent, je pense qu'il faudrait mieux ouvrir la connexion à l'ouverture du fichier et de ne la refermer qu'à la fermeture du fichier vu le mode de fonctionnement. Il suffit de placer le tout dans des variables globales comme ceci:

Public db As DAO.Database

et de l'ouvrir dans la procédure Private Sub Workbook_Open() du module ThisWorkbook
- A défaut d'utiliser le point précédent, il faut prendre soin de vider la mémoire lorsque les variables ne sont plus utilisées après les .close comme ceci:

set rst=nothing

. Faire de même avec db. Si la mémoire n'est pas correctement vidée, cela peut entraîner des problèmes de plantages qui sont peut-être ceux que tu rencontres.
- Si tu te contentes d'envoyer des paramètres à des requêtes SELECT je pense qu'il faudrait que tu te penches vers l'utilisation de requêtes paramétrées. En fait tu crées tes requêtes dans Access, tu leur transferts le(s) paramètre(s), le moteur d'Access effectue le traitement, et tu récupères le résultat. Les fonctions seront peut-être un chtouille plus longue, mais tu devrais gagner en rapidité et en qualité de code (plus de string SQL à écrire )
- Dernier conseil qui me passe par la tête et qui n'a pas de lien direct avec le problème ici: bien penser aux Index/Clé primaires qui peuvent considérablement accélérer les requêtes !

Bon courage,