Discussion :

[annedeblois]

Bonjour,

Nous avons une base de données Access 97 qui extrait des données à partir d'une table SQL Server -- nommée Resultat_dAnalyse -- plutôt volumineuse.

Pour éviter d'interroger directement la table SQL Server, en premier lieu le code envoie du code SQL qui envoie dans une table temporaire située dans un autre fichier Access (défini par une constante nommée COQUILLE, mais là n'est pas le problème).

Or, après m'être fait rapporter que la requête renvoie beaucoup de messages d'erreur ODBC -- l'appel a échoué. En mode débuggage, j'ai remarqué que c'est l'exécution de la seconde ligne DoCmd.RunSQL qui vire dans le beurre, voici le bout de code:

Code VBA :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
    ' Mettre les données de la tables des résultats dans une tables temporaire
    ' afin d'améliorer les performance des requêtes l'utilisant.
    DoCmd.SetWarnings False
    DoCmd.RunSQL "DELETE * FROM BIO_TB_TREA_RESULTAT_ANALYSE_tempo IN '" & COQUILLE & "'"
 
    ' La commande qui génère les erreurs lorsqu'il y a beaucoup de données pour un paramètre donné
    DoCmd.RunSQL "INSERT INTO BIO_TB_TREA_RESULTAT_ANALYSE_tempo ( Username, NoParam, NoTest, NoTestString, DateHrModif, Noechantillon, ModifiePar, Valeur ) in '" & COQUILLE & "' " & _
                    "SELECT CurrentUser() AS Username, Resultat_dAnalyse.NoParam, Resultat_dAnalyse.NoTest, CStr([Resultat_dAnalyse].[notest]) AS NoTestString, Resultat_dAnalyse.DateHrModif, Resultat_dAnalyse.Noechantillon, Resultat_dAnalyse.ModifiePar, Resultat_dAnalyse.Valeur " & _
                    "FROM Resultat_dAnalyse " & _
                    "WHERE (((Resultat_dAnalyse.NoParam)=[Formulaires]![BIO_TREA_CONTROLE_DUPLICATA]![parametre]) AND ((CStr([Resultat_dAnalyse].[notest])) Like [Forms]![BIO_TREA_CONTROLE_DUPLICATA]![test]) AND ((Resultat_dAnalyse.DateHrModif)>=[Forms]![BIO_TREA_CONTROLE_DUPLICATA]![date1] And (Resultat_dAnalyse.DateHrModif)<=[Forms]![BIO_TREA_CONTROLE_DUPLICATA]![date2])); "
    DoCmd.SetWarnings True
 
    DoCmd.OpenQuery "BIO_TREA_CONTROLE_DUPLICATA_RESULTATS_NEW", , acReadOnly
    'DoCmd.OpenQuery "BIO_TREA_CONTROLE_DUPLICATA_RESULTATS", , acReadOnly

Une fois la table temporaire remplie, les deux requêtes qui ferment la marche sont exécutées.

Le code SQL ne renvoie pas d'erreurs lorsqu'il y a peu de données correspondant aux critères. Me compliquerais-je la vie en procédant de cette façon? Ou encore, est-ce que j'éviterais mieux les problèmes en passant par une succession de deux ou trois tables temporaires?

Merci à l'avance,

AD

EDIT - Désolée, j'ai verrouillé le post accidentellement, c'est réglé...

[Maxence HUBICHE]

Salut Anne !

Je ne comprends pas bien ton problème

SQL Serveur =>> COQUILLE (mdb) =>> BDD (mdb)
C'est bien le schéma n'est-ce pas ?
Si c'est le cas, lorsque les données sont dans COQUILLE, il n'y a plus de 'relation' avec SQLServer (vous n'avez pas poussé jusqu'à faire une table liée à SQLServer j'espère !!!!)
Donc, s'il n'y a plus de relation avec SQLServer, il n'y a pas de problème ODBC avec SQLServer !!!


Est-ce que, jusque là, j'ai bon ?

[annedeblois]

Salut Anne !

Je ne comprends pas bien ton problème

SQL Serveur =>> COQUILLE (mdb) =>> BDD (mdb)
C'est bien le schéma n'est-ce pas ?

Désolée, d'habitude j'expose le tout plus clairement.

En fait, le code SQL extrait des données de la table SQL (qui est volumineuse) et les insère dans la table temporaire située dans le fichier MDB dont l'information est dans la chaîne COQUILLE. Ce fichier MDB appelé COQUILLE est la base de données Access même de laquelle je lance le code SQL inscrit dans le bout de code VBA que j'ai envoyé.

Est-ce que c'est plus clair comme ça?

[chrifo]

Bonjour,

La table "Resultat_dAnalyse" est donc une table sqlserver liée dans access ?
Avez-vous les droits pour creer sur sqlserver une vue correspondant à la requête ? Ensuite vous importez la vue dans access.
Cela déporte clairement la requête sur sqlserver et peut régler vos problèmes sur de gros volumes.

A+

[annedeblois]

Bonjour,

La table "Resultat_dAnalyse" est donc une table sqlserver liée dans access ?

Exactement.

Avez-vous les droits pour creer sur sqlserver une vue correspondant à la requête ? Ensuite vous importez la vue dans access.
Cela déporte clairement la requête sur sqlserver et peut régler vos problèmes sur de gros volumes.

A+

Oui, j'ai les droits. Par contre, je ne suis pas une experte en SQL Server

Donc, si j'ai bien compris, je peux créer la vue et la lier à ma base de données Access et je n'ai qu'à ouvrir la vue plutôt que de faire un SELECT à mettre dans la table temporaire Access? Et ensuite lancer mes requêtes sur la vue?

Merci de confirmer. Ça me semble très intéressant.

[Maxence HUBICHE]

En fait, Access 97 est très moyen en ODBC avec SQLServer, générant souvent des locks en pagaille.
Si tu avais au minimum la version 2000, tu pourrais faire un adp qui te connecterait en direct avec le moteur SQLServer. Mais bon, tu as 97 :s alors, n'en parlons plus.

Je te conseillerais d'utiliser les DTS plutôt que les fichiers liés par ODBC.
Surtout si tu as de gros volumes de données.
Après tout, ils pourront facilement exporter/importer des données depuis/vers ton système SQLServer, à partir d'un fichier plat (par exemple), fichier plat tout aussi lisibles par Access

[chrifo]

Donc, si j'ai bien compris, je peux créer la vue et la lier à ma base de données Access et je n'ai qu'à ouvrir la vue plutôt que de faire un SELECT à mettre dans la table temporaire Access? Et ensuite lancer mes requêtes sur la vue?

Non, je pensais plutôt à importer la vue dans Access (les données de la vue sqlserver seront importées comme s'il s'agissait d'une table) et à effectuer les requêtes sur ce résultat.
L'objectif est d'effectuer les requêtes soit en totalité sur sqlserver (en exportant les données access dans sqlserver et en créant les index qui vont bien après l'export - ceci vous permettra de maitriser le plan d'exécution de la requête), soit en totalité dans access (en important les données sqlserver dans access), mais de ne jamais croiser les 2 afin d'éviter la tambouille de l'ODBC.

La solution proposée par Maxence, bien que différente, a le même objectif et est peut-être tout aussi performante car elle passe par des fichiers à plat mais évite complètement le pilote ODBC.

[annedeblois]

Non, je pensais plutôt à importer la vue dans Access (les données de la vue sqlserver seront importées comme s'il s'agissait d'une table) et à effectuer les requêtes sur ce résultat.
L'objectif est d'effectuer les requêtes soit en totalité sur sqlserver (en exportant les données access dans sqlserver et en créant les index qui vont bien après l'export - ceci vous permettra de maitriser le plan d'exécution de la requête), soit en totalité dans access (en important les données sqlserver dans access), mais de ne jamais croiser les 2 afin d'éviter la tambouille de l'ODBC.

La solution proposée par Maxence, bien que différente, a le même objectif et est peut-être tout aussi performante car elle passe par des fichiers à plat mais évite complètement le pilote ODBC.

Merci à vous deux, puisque je dois garder le pilote ODBC, je vais devoir choisir l'importation de la vue dans Access.

Par contre, je ne suis pas trop sûre de ce que tu veux dire par importer la vue en Access. Est-ce que je crée la vue dans SQL Server et ensuite dans mon code VBA je fais mes requêtes sur la vue et non sur la table? (ou si c'est imossible, c'est que je n'ai pas tout compris, désolée... )

[Maxence HUBICHE]

puisque je dois garder le pilote ODBC

Tu DOIS garder le pilote ODBC ?
Pourquoi ce mot DEVOIR ici ?


Je pensais que tu DEVAIS faire que ça marche

[vodiem]

désolé de m'engager sur un terrain que je ne maîtrise pas et dont je ne peux affirmer de certitude (n'ayant pas en plus d'odbc installé).

une première bétise:
si tu as une erreur "ODBC -- l'appel a échoué" dû à un volume plus important d'enregistrements, je me demandais si cela ne provenait pas d'un délai limite de transaction et donc d'un parametre de l'odbc?

une certitude:
ta requete est basic: tu ne gagnerais pas en découpant en plusieurs tables temporaires.
seulement en envoyant par "petit bout" effectivement tu ne devrais plus avoir d'erreur du au volume, mais c'est pas terrible de faire comme ca.

une interrogation:
tu vide est tu rempli la table temporaire. tu ne pourrais pas faire simplement une requete selection étant donner que la table [Resultat_dAnalyse] est liée? tu gagnerais en temps puisque tu ne recopierais plus les données et peut être l'odbc réagirait mieux?

une autre bétise:
il n'est pas possible d'attacher une vue SQL dans access?

pour l'importation dans access:
il est préférable que tu crée la vue sur SQL puis importe dans access, tu auras moins (au pire le même nb) de donner à importer.

sinon je dirais pour garder l'idée de Maxence, si tu dois garder l'ODBC: garde-le et mais ne l'utilise pas.

[chrifo]

Par contre, je ne suis pas trop sûre de ce que tu veux dire par importer la vue en Access. Est-ce que je crée la vue dans SQL Server et ensuite dans mon code VBA je fais mes requêtes sur la vue et non sur la table? (ou si c'est imossible, c'est que je n'ai pas tout compris, désolée... )

Moi je pensais vraiment à importer la vue, ce qui va te créer une table access avec les données de la vue. L'exécution de la requête est ainsi déportée dans sqlserver (ta vue), et tu importe le résultat dans access.
--> De la même manière que tu attachais la table sqlserver, importe la vue. (né crée pas un lien comme lors d'une attache, importe la pour la matérialiser dans ton mdb)

[Maxence HUBICHE]

En fait, les solutions que nous te proposons sont systématiquement de COUPER la connexion avec SQLServer.
Lorsque tu utilises une connexion ODBC depuis Access vers SQLServer, il y a un nombre monstrueux de locks qui se mettent en route. Tu peux les voir si tu fais une analyse de ton serveur SQL. Ces locks saturent complètement ton server qui deviens plus lent qui finit par dépasser le timeout qui te renvoie une erreur.
Cela se produit donc forcément là où tu as un maximum de données.

Il te faut également savoir que j'ai déjà vu des connexions fantômes sur de telles connexionx, côté SQLServer. Une connexion (sans l'activité) c'est déjà 1Mo. Je te laisse imaginer ce qui peut se produire si tu conserve des connexions fantômes, en plus.

Donc, la meilleure solution, c'est de couper cette connexion.

La solution de chrifo est intéressante, car elle ne génère pas d'intermédiaire entre SQLServer et Access. Elle implique cependant une procédure d'importation depuis Access.
Ma solution passe par un fichier externe, mais elle peut être complètement automatisée. En effet, tu peux lier le fichier généré par le DTS dans Access, et la procédure DTS peut être plannifée côté serveur.

Maintenant, c'est à toi de nous dire ce que tu préfères faire, ou si ces propositions ne correspondent pas à ce que tu souhaites faire.

++

[annedeblois]

Salut, excusez-moi de n'avoir pas pu donner suite à vos réponses...!!

Je vais étudier toutes les pistes que vous m'avez apportées, et vais voir laquelle me convient le mieux.



Je ne sais malheureusement pas quand je pourrai vous donner des nouvelles, par contre.

Dommage que nous ne puissions pas mettre un tag du style [En attente] comme on met ou [Délestage]... Cela permettrait de voir tout de suite que celui qui a ouvert le fil est en train de tester la solution et qu'il ne sait quand il pourra donner une réponse...

[Maxence HUBICHE]

Dommage que nous ne puissions pas mettre un tag du style [En attente]

Suffit de demander