connexions multiples à une base de donnée grâce au modèle ADO

**deTouteBoT** · 19/10/2020, 10h07

Bonjour a tous,

J'ai ce code qui permet d'interroger une base de donnée(ici des fichiers excels) avec le modèle ADO ( un grand merci à SilkyRoad). Le code fonctionne mais je compte le mettre dans une macro complémentaire. Elle est destiné à 6 utilisateurs, étant donné je n'ai jamais cela avant, est ce que vous sauriez comment ça va se comporter en cas de connexion multiple à un même fichier par exemple ? ou si le modèle ADO gère bien ce cas de figure

Si vous avez d'autres remarques, je suis preneuse

Mercii

Lucy

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
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
Public Sub extractionValeurCelluleClasseurFerme(ticker As String, champs As String, dateD As String, dateF As String, rng As Range)
    Dim Source As Object 'As ADODB.Connection
    Dim Rst As Object 'As ADODB.Recordset
    Dim ADOCommand As Object 'As ADODB.Command
    Dim Fichier As String, Feuille As String
    Dim listChamps() As String, nbChamps As Integer
    Dim champs0, champs1, champs2, champs3, champs4, champs5 As String
 
    Feuille = "Feuil1$"
    Fichier = "C:\Users\" & ticker & ".xlsx"
    listChamps() = Split(champs, ";")
    nbChamps = UBound(listChamps())
 
    Set Source = CreateObject("ADODB.Connection")
    Source.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Fichier + ";Extended Properties=Excel 12.0;"
 
    Set ADOCommand = CreateObject("ADODB.Command")
    With ADOCommand
        .ActiveConnection = Source
        Select Case nbChamps
        Case 0
            champs0 = listChamps(0)
        .CommandText = "SELECT DateValue,[" & champs0 & "] FROM [" & Feuille & "] WHERE DateValue>= #" & dateD & "# And DateValue <= #" & dateF & "# GROUP BY DateValue,[" & champs0 & "]"
        Case 1
            champs0 = listChamps(0)
            champs1 = listChamps(1)
         .CommandText = "SELECT DateValue,[" & champs0 & "],[" & champs1 & "] FROM [" & Feuille & "] WHERE DateValue>= #" & dateD & "# And DateValue <= #" & dateF & "# GROUP BY DateValue,[" & champs0 & "],[" & champs1 & "]"
        Case 2
            champs0 = listChamps(0)
            champs1 = listChamps(1)
            champs2 = listChamps(2)
        .CommandText = "SELECT DateValue,[" & champs0 & "],[" & champs1 & "],[" & champs2 & "] FROM [" & Feuille & "] WHERE DateValue>= #" & dateD & "# And DateValue <= #" & dateF & "# GROUP BY DateValue,[" & champs0 & "],[" & champs1 & "],[" & champs2 & "]"
        Case 3
            champs0 = listChamps(0)
            champs1 = listChamps(1)
            champs2 = listChamps(2)
            champs3 = listChamps(3)
        .CommandText = "SELECT DateValue,[" & champs0 & "],[" & champs1 & "],[" & champs2 & "],[" & champs3 & "] FROM [" & Feuille & "] WHERE DateValue>= #" & dateD & "# And DateValue <= #" & dateF & "# GROUP BY DateValue,[" & champs0 & "],[" & champs1 & "],[" & champs2 & "],[" & champs3 & "]"
        Case 4
            champs0 = listChamps(0)
            champs1 = listChamps(1)
            champs2 = listChamps(2)
            champs3 = listChamps(3)
            champs4 = listChamps(4)
        .CommandText = "SELECT DateValue,[" & champs0 & "],[" & champs1 & "],[" & champs2 & "],[" & champs3 & "],[" & champs4 & "] FROM [" & Feuille & "] WHERE DateValue>= #" & dateD & "# And DateValue <= #" & dateF & "# GROUP BY DateValue,[" & champs0 & "],[" & champs1 & "],[" & champs2 & "],[" & champs3 & "],[" & champs4 & "]"
        Case 5
        .CommandText = "SELECT * FROM [" & Feuille & "] WHERE DateValue>= #" & dateD & "# And DateValue <= #" & dateF & "#"
        End Select
    End With
 
    Set Rst = CreateObject("ADODB.Recordset")
 
    'Rst.Open ADOCommand, , adOpenForwardOnly, adLockReadOnly
    Rst.Open ADOCommand, , adOpenStatic, adLockReadOnly
    Set Rst = Source.Execute(ADOCommand.CommandText)
 
    rng.Offset(1, 0).CopyFromRecordset Rst
    Rst.Close
    Source.Close
    Set Source = Nothing
    Set Rst = Nothing
    Set ADOCommand = Nothing
End Sub

**ONTAYG** · 19/10/2020, 10h08

Bonjour,

Théoriquement cela ne devrait pas fonctionner, car si je ne me trompe pas un fichier Excel ne peut admettre qu'une seule connexion externe, c'est écrit dans le tuto.

ONTAYG

**deTouteBoT** · 19/10/2020, 10h38

Bonjour,

Merci du retour!

Oui effectivement j'ai revue le tutoriel mais cela veut dire que tant que l'utilisation n'est pas simultanée cela devrait fonctionner

Connaissez vous peut être un autre modèle qui permettrait les connexions multiples ?

Lucy

**ONTAYG** · 19/10/2020, 10h49

Bonjour,

C'est le rôle des bases de données, Excel est un tableau.

ONTAYG

**deTouteBoT** · 19/10/2020, 10h58

D'accord c'est noté !!

Merci pour tes retours

Lucy

**Zeabon** · 19/10/2020, 11h34

Bonjour deTouteBoT,

Je ne connais pas ADO mais de se que j'ai compris, je peux te proposer 2 pistes de réflexion qui seront à confirmer :

- Détection de l'ouverture en écriture/lecture seule :
Excel permet de détecter si un fichier excel est ouvert en lecture seule.A vérifier si on peut l'appliquer à ADO. Il te suffirait alors d'annuler ta commande, la renouveler ultérieurement (ex : timer)
(Workbooks(Thisworkbook.Name).Readonly = True quand lecture seule)

- Fichier Excel partagé :
Alors là par contre, il faut prendre de grosse pincette parce que ca devient le bordel et tu as 2 types de fichiers partagé :
Local (à "l'ancienne") : qui permet la connexion multiple mais je ne peux pas te dire ce qui marchera ou pas.
Sharepoint/Teams : qui permet aussi la connexion multiple mais la gestion/modification reste limité.

Voilà 2 pistes de recherche pour toi, je suis désolé mais sans connaître ADO, je ne peux pas en conseiller une en particulier.
Peut-être les autres pourront te renseigner plus.

Et je suis d'accords avec ONTAYG : Excel est un tableur.
On peut se rapprocher d'une BDD ultra simplifié avec l'aide des macros, mais ce n'est pas un outil de gestion de base de données.
Voir ACCESS.
Faites pas comme les anglais avec Excel qui n'a pas assez de ligne.

Bon courage,
Zeabon

**Pierre Fauconnier** · 19/10/2020, 15h58

Salut.

Tu ne donnes pas ta version d'Excel mais à partir d'Excel 2010, tu devrais utiliser Power Query pour rapatrier tes données dans chaque classeur de travail. Ca demande 0 lignes de codes en VBA et ça permet les accès multiples. Ca permet d'éviter l'adodb sur de l'excel, dont je suis loin d'être fan.

Nom : 2020-10-19_155810.png
Affichages : 516
Taille : 37,6 Ko

Nom : 2020-10-19_155810.png
Affichages : 516
Taille : 37,6 Ko

Cela étant, je ne comprends pas trop bien ton problème. La connexion n'est ouverte que le temps du transfert, donc il y a peu de chance que les 6 utilisateurs lancent la requête en même temps. Le conflit éventuel se gère avec On Error Goto qui permet d'informer proprement l'utilisateur, mais il faudrait que le temps de transfert soit super long que le risque de collision soit réel, non?

Cela étant, Excel n'est normalement pas fait pour cela, car comme le dit ONTAYG, Excel est un tableur et pas un SGBD (système de gestion de bases de données). Power Query permet toutefois de nuancer un peu ce propos à l'heure actuelle et il est possible, pour de petites structures, de monter un système avec des fichiers de données

**Thumb down** · 19/10/2020, 23h51

bonsoir,
si le fichier Excel est en mode partagé le 6 utilisateurs pourront ce connecté au même instant sans problème!

**Pierre Fauconnier** · 20/10/2020, 09h06

Salut Thumb

Si on est en 365 et que le fichier est sur un onedrivre, il n'y a pas de problèmes à le partager, car techniquement, cela fonctionne. Dans la mesure où, selon le code donné dans le message initial, il s'agit uniquement de lire les données, c'est une configuration qui pourrait fonctionner.

Je suis plus dubitatif sur un partage à 6 en lecture-écriture même si, là aussi, c'est techniquement possible. As-tu de ton côté des retours d'expérience à ce sujet? Ton avis m'intéresse grandement.

**Thumb down** · 20/10/2020, 18h31

Bonjour Pierre,
En fait le problème en en écriture c'est de savoir verouller un enregistrement car les 6 personnes voient les modifications en même temps !

Autres problèmes est qu'Excel demande si on veut enregistrer les modifications à la fermeture du fichier car il a peut-être été modifié par un autre.

Si on dit non les modifications sont perdus et mon voisin qui vient de saisir pendant une heure se tire les cheveux !

C'est bien ce qui a été dit plus haut sur les Sgbd

**deTouteBoT** · 20/10/2020, 19h09

Bonjour à tous!

Merci pour toutes vos réponses

Zeabon, j'ai regardé il existe bien un code permettant de vérifier si il y a une connexion au classeur donc cela devrait être faisable, je n'ai pas encore testé les fichiers partagés mais je vais me renseigner davantage
Oui je conçois totalement que c'est pas sa fonction première mais ne pas utiliser une bd est plus une contrainte qu'un choix! mercii

Pierre Fauconnier, oui on en m'a parlé à plusieurs reprises je devrais donc m'y mettre, enfaite cette procédure va dans une fonction Excel donc je ne sais si je peux allier les deux
Oui c'est ce que je pense aussi mais vu que c'est la première fois que je vais tester un code comme ça, je voulais un petit retour d'expérience mais une simple procédure d'erreur devrait le faire

Thumb down, je vois bien le concept de fichier partagé mais une autre problématique se pose pour moi, les fichiers que j'interroge sont aussi crée par une autre macro, peut on créer des fichiers excels partagés par vba ?
Après, mon but reste la lecture uniquement mais je garde en tete tous ça

Lucy
(XL16)

**Thumb down** · 20/10/2020, 19h23

Le seul problème dans les connections simultanée à un même fichier c'est qu'un message demande la confirmation de lecture seule puis affiche le classeur !

Si ton but est uniquement de lire les données de ton fichier, il te faut tout simplement définir ta connexion en read only et tout deviendra transparent !

"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & SourceFile & ";Mode=Read;Extended Properties=""Excel 12.0;HDR=Yes;"";"

**Zeabon** · 21/10/2020, 08h50

Envoyé par Thumb down

Si ton but est uniquement de lire les données de ton fichier, il te faut tout simplement définir ta connexion en read only et tout deviendra transparent !

Je suis D'accords avec Thumb là-dessus.

J'ai déjà créé des pseudos BDD avec Excels en utilisant une fichier interface séparé.
9/10ième de mes connexions à la BDD étaient en lecture seule pour éviter les ouvertures en modification simultanée.

La solution de détecter si le fichier est déjà ouvert est utile seulement si tu souhaites y accéder en modifications pour écrire/modifier/supprimer quelque chose.

Zeabon

**Thumb down** · 21/10/2020, 09h57

Bonjour,
Juste un petit détail le delete, comme le create table,drop table etc... N'existe pas en ado Excel !

**deTouteBoT** · 22/10/2020, 00h15

Bonjour,

Thumb down, je pense que c'est exactement ce qu'il me fallait!! Je prends vos remarques en note ça me servira surement merci

J'aurais encore besoin de vos lumières à propos de mon code je viens de remarquer un petit bug

quelque chose au niveau des dates qui n'allaient pas, quand je rentre des dates dont le nombre de jour est inférieur à 12, elle considère la date au format anglais

(les fichiers sources sont au format français)

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
 
Sub test()
Dim rng As Range
ticker = "flute"
champs = "Arg1"
dateD = "05/03/2013"
dateF = "25/03/2014"
Set rng = ThisWorkbook.Sheets("Feuil2").Range("A1")
Call extractionValeurCelluleClasseurFerme(CStr(ticker), CStr(champs), CStr(dateD), CStr(dateF), rng)
 
End Sub

La date de début qu'elle me sort est le "03/05/2014", par contre elle me sort bien la bonne date de fin, auriez vous une idée ?

Je pense que le souci vient de l'utilisation du format date de sql

Code :

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

"SELECT DateValue,[" & champs0 & "] FROM [" & Feuille & "] WHERE DateValue>= #" & dateD & "# And DateValue <= #" & dateF & "# GROUP BY DateValue,[" & champs0 & "]"

j'ai réfléchis à une solution en inversant les jours et mois dès lors que le nombre de jour est inférieur à 12 mais il y a un moyen plus propre de le faire je suis preneuse!

Merci à vous

Lucy

**Thumb down** · 22/10/2020, 09h38

Bonjour,
Essais comme ça

Code :

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

DateValue>= #" & format(dateD,"yyyy-mm-dd") & "#

Le mieux pour éviter ce genre de désagrément, c'est de faire des requêtes paramétrable {Adodb.Command}.

**deTouteBoT** · 22/10/2020, 12h16

ça marche nickel !!! merci

Qu'entends tu par des requêtes paramétrables ?

J'aurais une autre question, le fait de créer des objets tels que

Code :

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

Set Source = CreateObject("ADODB.Connection")

évite bien de devoir cocher manuellement les références à ADO ?

Lucy

**Thumb down** · 22/10/2020, 12h38

Code :

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

dim nc as New  ADODB.Connection

Cette forme décrire implique ADODB.Connection soit connu dans les références du projet {menu=>outils=>références=>ActiveX data Object}

Alors qu'avec creatobject ("ADODB.Connection") tu utilises la version installée sur ton ordinateur et tu t'affrenchi des références. Mais tu perds l'accès à la saisie intuitive et les constantes Ado.

**Pierre Fauconnier** · 22/10/2020, 13h39

Salut.

Perso, utiliser la liaison tardive (donc sans référencer la bibliothèque ADO) n'est pertinent que si ton code est susceptible d'être démarré de différentes machines, et encore. Je n'ai pas rencontré ces dernières années de problèmes à utiliser la référence 2.7 de ADO. Cette façon de procéder te pénalise lors de la saisie du code, surtout pour des requêtes paramétrées, car tu ne disposes effectivement pas de la saisie semi-automatique et des constantes ADO comme le dit Thumb, mais tu te prives également des tests de compilation car les erreurs de code ne surviendront qu'à l'exécution.

Une requête paramétrée est une requête dans laquelle on remplace certaines valeurs (notamment celles de la clause WHERE) par des paramètres, c'est-à-dire des variables que l'on spécifie hors de la requête sql. Un exemple vaut mieux qu'un long discours.

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
Sub Test()
  Dim cn As ADODB.Connection
  Dim cm As ADODB.Command
  Dim pm As ADODB.Parameter
  Dim rs As ADODB.Recordset
  Dim Filename As String
  Dim Date1 As Date, Date2 As Date
  Dim sql As String
 
  Date1 = DateSerial(2020, 1, 1)
  Date2 = DateSerial(2020, 1, 31)
 
  Filename = "c:\data\temp\source-odbc.xlsx"
  sql = "select * from [feuil1$] where date >=? and date <=?"
  Set cn = New ADODB.Connection
  cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Filename + ";Extended Properties=Excel 12.0;"
  Set cm = New ADODB.Command
  cm.ActiveConnection = cn
  cm.CommandText = sql
  cm.Parameters.Append cm.CreateParameter("p1", adDate, adParamInput, 4, Date1)
  cm.Parameters.Append cm.CreateParameter("p2", adDate, adParamInput, 4, Date2)
  Set rs = cm.Execute()
End Sub

Dans la requête sql de l'exemple, j'ai remplacé les dates par des ?. La commande sql reçoit les deux dates en paramètres. C'est le moteur ADO qui remplacera les ? par les valeurs des paramètres en fonction du type de paramètre renseigné. ADO pour excel n'acceptant les paramètres nommés, il est indispensable de créer les paramètres dans l'ordre de leur apparition dans la requête SQL!

L'avantage est que tu n'as pas à te préoccuper de la façon dont tu dois passer les valeurs (comme dans ton exemple, passer les dates au format mm-dd-yyyy et les encadrer par des #). L'utilisation des paramètres permet également de passer facilement du texte sans devoir toi-même échapper les quelques caractères qui posent problème (l'apostrophe, les jokers % et _, ...). Ca permet également d'avoir un texte de requête plus simple à gérer car tu ne dois pas concaténer toutes tes variables...

Que du bonheur

J'ai écrit 4 billets sur le sujet (c'est pour connecter Excel et Access, mais dans les grandes lignes, c'est ok pour Excel). Voici le lien vers le premier des quatre.

En plus des restrictions "CRUD" dont parle Thumb, le problème de ADO pour Excel est que les jointures sont limitées et que les champs sont typés par Excel en fonction des 8 premières lignes récupérées du recordset. Perso, je n'aime pas du tout travailler avec ADO pour Excel, et je lui préfère de loin le rapatriement des données en VBA suivi d'un traitement local ou, surtout sur les versions actuelles de l'outil, Power Query qui devient pour moi incontournable et à prendre en mains rapidement pour booster ta productivité.

**Thumb down** · 22/10/2020, 13h59

Bonjour Pierre,
Je rajouterai encore un bémol à l'utilisation d'Ado pour Excel Ado n'a pas été implémenté pour des versions supérieur à 2004 il ne dépasse pas la taille d'un xls!

connexions multiples à une base de donnée grâce au modèle ADO

Macros et VBA Excel

Discussions similaires

Partager

Partager