Connexions à BdD .XLS

[TooFatBoy]

Bonjour,

Pouriez-vous m'aider à comprendre le principe de fonctionnement de la connexion à un fichier .XLS en tant que base de données ?


J'ai une macro de connexion à la base de données :

Sub ConnexionBdD(Fichier)
' Connexion au fichier servant de base de données

    Set Cn = New ADODB.Connection

    With Cn
        .Provider = "Microsoft.Ace.OLEDB.12.0"      ' "Microsoft.Jet.OLEDB.4.0"
        .ConnectionString = "Data Source=" & Fichier & ";Extended Properties=Excel 8.0;"
        .Open
    End With

End Sub

J'ai une macro de déconnexion :

Sub DeconnexionBdD()
' Fermeture de la connexion avec le fichier servant de base de données

    Cn.Close
    Set Cn = Nothing

End Sub

Et au milieu j'ai une macro de lecture des données :

Sub LectureMois()

    Call ConnexionBdD("F:/2014.xls")

    texte_SQL = "SELECT [ListePersonnel$].Fonction, [ListePersonnel$].Nom, [ListePersonnel$].Prenom, " _
    & "FROM [ListePersonnel$] " _
    & "WHERE [ListePersonnel$].Fonction = " & Chr(34) & "Secrétaire" & Chr(34) _
    & " AND [ListePersonnel$].Mois = " & Chr(34) & "Janvier" & Chr(34) _

    Set Rst = New ADODB.Recordset
    Set Rst = Cn.Execute(texte_SQL)

    'Ecrit le résultat de la requête dans la cellule A2
    ThisWorkbook.Sheets("ListeJanvier").Range("A2").CopyFromRecordset Rst

    Call DeconnexionBdD

End Sub

1- Le "Set Cn = Nothing" est-il indispensable pour pouvoir effctuer une nouvelle connexion par la suite ?

2- Faut-il également faire un "Set Rst = Nothing" pour pouvoir effectuer plusieurs fois de suites la macro de lecture des données, ou bien on peut refaire un "Set Rst = New ADODB.Recordset" sans passer par cette ligne de commande ?

 

[camarchepas]

Re : Connexions à BdD .XLS

Bonjour ,

Les set xxx = nothing sont utilisés pour supprimer et libérer les objets créés permettant par la même occasion de libérer un peu de mémoire, jamais inutile donc.

 

[TooFatBoy]

Re : Connexions à BdD .XLS

Merci pour la réponse.
C'est effectivement ce que j'avais compris.

Je crois comprendre dans ta réponse que cette instruction n'est pas obligatoire mais plutôt conseillée.
Ce n'est donc pas le fait qu'il n'y ait aucune ligne "Set Rst = Nothing" dans le programme qui provoque une erreur de compilation sur certains PC sous Excel 2010.

 

[chris]

Re : Connexions à BdD .XLS

Bonjour

Non rien à voir : il peut manquer une référence sinon Excel indique la ligne qui coince...

Pour les connexions on peut, pendant qu'une connexion est active, l'utiliser plusieurs fois dans une même procédure mais sinon, en fin de procédure, si tu ne la libères pas, tu risques de te retrouver avec plusieurs connexions vers un même fichier et, outre l'aspect mémoire, un conflit d'accès...

 

[TooFatBoy]

Re : Connexions à BdD .XLS

Merci pour cette réponse claire.
La ligne qui coince est systématiquement la connexion : Cn.Execute(texte_SQL)

Ayant lu que la lecture ou l'écriture dans un fichier Excel fermé par plusieurs personnes simultanément ne fonctionnait pas, j'essaye d'ouvrir la connexion au dernier moment possible, c'est-à-dire juste avant l'envoie de la requête.
Et je referme aussitôt la connexion au fichier.

Le problème semble se produire quand le fichier est en réseau et donc que plusieurs personnes essayent d'accéder simultanément au fichier .XLS servant de base de données.
sur certains PC ça provoque un plantage d'Excel (obligé d'aller tuer la tâche Excel...), alors que sur d'autres PC du même réseau ça provoque un message d'erreur "erreur de compilation" avec surlignage de la ligne Cn.Execute(texte_SQL).

 

[chris]

Re : Connexions à BdD .XLS

Re

As-tu testé en enregistrant le fichier en mode partagé ? Cela peut régler les problèmes d'accès simultanés mais limite aussi l'usage du fichier par ceux qui l'utilisent...

 

[TooFatBoy]

Re : Connexions à BdD .XLS

Non, je n'ai pas essayé car j'étais focalisé sur ceci :
Excel ne peut pas gérer les connections multiples et simultanées à un même classeur.
que j'ai lu ici (5ème paragraphe du chapitre II) : Lire et écrire dans les classeurs Excel fermés

Le fichier est dans un dossier lui-même partagé, donc je suppose que le fichier hérite des droits du dossier.
Mais je vais tenter ce que tu me proposes, au besoin en voyant ça avec les administrateur du réseau.

 

[camarchepas]

Re : Connexions à BdD .XLS

Re ,Chriss , Marcel,

J'ai eu le même genre de problème , le partage n'a rien résolu.
Et oui , le sql Excel est un peu limité pour cela.
L'éxécution d'une requête sql est souvent trés courte sur Excel , la solution peut être de mettre un fichier témoin d'utilisation , et avant de lancer la requête l'on vérifie que ce fichier n'est pas présent , s'il l'est ,alors l'on fait patienter l'utilisateur.

l'autre solution si la base n'est pas géante est de faire une copie à chaque connexion d'un utilisateur, l'on peut anticiper la copie dés l'ouverture du fichier, et de la supprimer lorsque celui ci à terminé de l'interroger .

 

[TooFatBoy]

Re : Connexions à BdD .XLS

Déjà, merci à vous deux de vous intéresser à mon problème car sinon je me sens bien seul face à ce problème, et mon chef attend des résultats...

J'ai eu le même genre de problème , le partage n'a rien résolu.
Et oui , le sql Excel est un peu limité pour cela.

Ce n'est pas de très bon augure pour l'avenir...

L'éxécution d'une requête sql est souvent trés courte sur Excel , la solution peut être de mettre un fichier témoin d'utilisation , et avant de lancer la requête l'on vérifie que ce fichier n'est pas présent , s'il l'est ,alors l'on fait patienter l'utilisateur.

Veux-tu dire créer un fichier genre "Attendre.txt" (vide ou contenant par exemple le login de session de l'utilisateur qui est déjà en train d'accéder à la base de données) juste avant la connexion à la BdD, et le supprimer juste après la déconnexion ?
Si le fichier existe, j'attends un dixième de seconde, puis je reteste. Ainsi de suite jusqu'à ce que le fichier n'existe plus, et je peux alors le créer, me connecter, envoyer la requète, fermer la connexion et enfin supprimer le fichier.
C'est bien ça, le principe que tu proposes ?

l'autre solution si la base n'est pas géante est de faire une copie à chaque connexion d'un utilisateur, l'on peut anticiper la copie dés l'ouverture du fichier, et de la supprimer lorsque celui ci à terminé de l'interroger .

Je ne suis pas certain d'avoir compris.
Au lieu d'accéder directement à la BdD, je fais une copie du fichier en question, j'accède aux données, puis je supprime le fichier ?
Mais comment faire quand je veux effectuer une requête de modification de la BdD ?

 

[camarchepas]

Re : Connexions à BdD .XLS

Le 2 eme point était pour de la consultation uniquement ,

effectivement si tu veux modifier , j'en reviens à la 1er proposition, que tu as trés bien compris , un tout petit fichier log avec les info que tu désires, en plus tu sauras toujours qui utilise la base.

un petit peu plus lourd à gérer que du sql classique, mais bon

 

[TooFatBoy]

Re : Connexions à BdD .XLS

OK. Je vais essayer ça alors. Merci.


Le problème pourrait-il venir de l'installation d'Excel ?
En effet, j'ai constaté deux réactions différentes selon les PC utilisés : le problème survient toujours au même endroit du programme, mais sur certains PC ça plante carrément Excel, alors que sur les autres PC ça m'envoit un message d'erreur ("erreur de compilation", alors que le programme torune parfaitement bien chez moi [j'ai Excel 2013 sous Windows 7 Pro, mais au boulot on a Excel 2010 sous windows 7 Entreprise]).

 

[camarchepas]

Re : Connexions à BdD .XLS

A oui ,
un truc , dans la boucle d'attente , pour rendre la main au systéme ,
un doevents est le bienvenue

si ton test est en amont de cette ligne , tu ne devrais plus avoir le souci, la base est en Excel ou en csv ?

 

[TooFatBoy]

Re : Connexions à BdD .XLS

OK pour le DoEvents, je vais essayer de l'utiliser.
Ma première tentative pour tester si l'imprimante était prête n'a pas été très concluant, mais bon, on verra bien.


En fait, la BdD est juste un tableau (en fait un tableau par onglet) avec des colonnes de texte.
Pour être plus précis, c'est un fichier .xls qui contient 13 onglets :
- 1 qui contient la liste du personnel,
- 12 correspondant chacun à un mois de l'année contenant la position de chaque personne pour le mois en question.