Requete de excel vers Access très longue

**jojo86** · 12/10/2017, 14h55

Bonjour,
Je ne comprends pas. J’exécute une requête depuis excel vers une base access qui est très très très longue... Lorsque j’exécute cette requête directement depuis le fichier access, le resultat est instantané...

Je ne comprends pas du tout pourquoi. Merci de votre aide.

Ma requete :

Code :

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

1
2
3
 
'Le champ Ordre est indexé dans la base
Select t1.`Ordre` From ImportIW49N as t1 Where t1.`Ordre` Not In (Select distinct t2.Ordre from ImportIW49N as t2 Where t2.StatutOP Like '%CONF%' Or t2.StatutOP Like '%CNFP%')

Le code :

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
 
Sub connexionbaseAccess(Fichier As String)
Set CnAccess = New ADODB.Connection
    With CnAccess
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .ConnectionString = "Data Source=" & Fichier
        .Open
    End With
    Set RstAccess = New ADODB.Recordset
End Sub
 
 
 
Sub Test()
Dim requete As String
Dim NomFeuille As String
Dim feuille As Excel.Worksheet
 
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
 
    requete = "Select t1.`Ordre` From ImportIW49N as t1 Where t1.`Ordre` Not In (Select distinct t2.Ordre from ImportIW49N as t2 Where t2.StatutOP Like '%CONF%' Or t2.StatutOP Like '%CNFP%')"
    NomFeuille = "Sans_Aucune_Confirmation"
 
    'RAZ de la feuille
    If SheetExists(NomFeuille) Then
        ThisWorkbook.Sheets(NomFeuille).Delete
    End If
    ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)).Name = NomFeuille
    Set feuille = ThisWorkbook.Sheets(NomFeuille)
 
    With feuille
        Call connexionbaseAccess(PathBase)
        RstAccess.Open requete, CnAccess, adOpenStatic, adLockOptimistic
 
        For i = 0 To RstAccess.Fields.Count - 1
            .Cells(1, i + 1) = RstAccess.Fields(i).Name
        Next i
 
        If RstAccess.RecordCount > 0 Then
            [A2].CopyFromRecordset RstAccess
        End If
    End With
 
    Call DeconnexionBaseAccess
    Set feuille = Nothing
 
 
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub

Invité · 12/10/2017, 17h47

Bonsoir,

Il n'y a pas grand chose à dire sur ta requête, hormis le distinct qui n'est pas utile ordre est in ou il ne l'est pas!

Préfères cependant cette forme d'écriture t1.[Ordre] à t1.`Ordre`

Également

Code :

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

1
2
Set CnAccess = New ADODB.Connection
 CnAccess.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ";"

n'oublies pas de compacter ta base Access de temps en temps et d'indexé les champs utilisés le.plus souvent dans tes close where!

Indépendamment de tout ça, qu'est ce qui justifie requête et sous requête?

Ordre	StatutOP
1	CONF
2	CONF
3	CONF
1	CNFP
2	CNFP
3	CNFP
1	TITI
2	TITI
3	TITI

Select distinct t2.Ordre from ImportIW49N as t2 Where t2.StatutOP Like '%CONF%' Or t2.StatutOP Like '%CNFP%'
donnera :

1

2

3

Select t1.`Ordre` From ImportIW49N as t1 Where t1.`Ordre` Not In (Select distinct t2.Ordre from ImportIW49N as t2 Where t2.StatutOP Like '%CONF%' Or t2.StatutOP Like '%CNFP%')
donnera :

1
2
3
1
2
3
1
2
3

**jojo86** · 16/10/2017, 17h12

Envoyé par dysorthographie

Indépendamment de tout ça, qu'est ce qui justifie requête et sous requête?

Et bien en fait, j'ai besoin de lister tous les enregistrements pour lesquels je n'ai aucun statut "CONF" ou "CNFP"
Si nous imaginons les données suivantes :

Ordre	OP	Statut
1	2	toto
1	3	CNFP
1	4	tata
2	10	titi
2	11	toto
2	12	tata
3	2	tata
3	4	test
3	5	tonton
3	6	CNFP
4	2	test
4	4	voiture

D'après ces données, je veux ressortir tous les Ordres qui n'ont aucunes opérations soit à CNFP ou soit à CONF.
Je dois donc obtenir, pour le tableau ci-dessus, la liste des Ordre suivante :
- 2
- 4

C'est pour ça que je faisais d'abord la sélection de tous les Ordres avec au moins un statut CNFP ou CONF et qu'ensuite je fais un Not In...
Merci de ton aide !

Invité · 16/10/2017, 17h17

Bonsoir,
Donc à mon sans il faut faire

Code :

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

1
2
 
Select distinct t2.Ordre from ImportIW49N as t2 Where t2.StatutOP Like '%CONF%' Or t2.StatutOP Like '%CNFP%'

**jojo86** · 16/10/2017, 17h20

Envoyé par dysorthographie

Bonsoir,
Donc à mon sans il faut faire

Code :

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

1
2
 
Select distinct t2.Ordre from ImportIW49N as t2 Where t2.StatutOP Like '%CONF%' Or t2.StatutOP Like '%CNFP%'

Tu as du me lire un peu vite

Si pour un ordre, au moins une OP est à CONF ou CNFP, alors l'ordre ne doit pas ressortir...

Invité · 16/10/2017, 17h25

Oui désolé

Code :

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

1
2
3
 
*
Select distinct t2.Ordre from ImportIW49N as t2 Where t2.StatutOP not  Like '%CONF%' and t2.StatutOP not Like '%CNFP%'

Maintenant si tu ne veux pas de toto qui a le même ordre que CNFP c'est ta requête qui est la bonne! Et là c'est le compactage de ta base Access qu'il faut faire.

Sur mes bases de données je le fait toutes les nuits!

**jojo86** · 16/10/2017, 17h53

Envoyé par dysorthographie

Oui désolé

Code :

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

1
2
3
 
*
Select distinct t2.Ordre from ImportIW49N as t2 Where t2.StatutOP not  Like '%CONF%' and t2.StatutOP not Like '%CNFP%'

Maintenant si tu ne veux pas de toto qui a le même ordre que CNFP c'est ta requête qui est la bonne! Et là c'est le compactage de ta base Access qu'il faut faire.

Sur mes bases de données je le fait toutes les nuits!

Je galère !
Cette requête me remonte les ordres 1 2 3 et 4 car dans tous les ordres, j'ai au moins une ligne sans le statut CONF et/ou CNFP.... Alors que je ne veux que les ordres pour lesquels je n'ai aucune ligne CONF ou CNFP...

Invité · 16/10/2017, 18h03

La boucle est bouclé on en revient à ce que je disais au début!

Il n'y a pas grand chose à dire sur ta requête, hormis le distinct qui n'est pas utile ordre est in ou il ne l'est pas!

Compactes ta base Access!

**jojo86** · 17/10/2017, 08h54

Envoyé par dysorthographie

La boucle est bouclé on en revient à ce que je disais au début!

Compactes ta base Access!

Merci ! On en revient donc au point de départ !

Je viens de compacter et j'ai verifié, mon champ Ordre et StatutOP est bien indexé.
J'ai 12300 lignes et la requete tourne pendant 4 minutes, ça me semble beaucoup... Je ne pige pas. Je suis reparti d'une ancienne base access que j'ai épurée en supprimant les tables inutiles. Je vais peut être repartir d'un fichier a plat pour voir.

Une dernière question, lorsqu'on modifie le paramétrage d'un champ pour l'indexer. Est-ce que l'indexation est immédiate ou bien faut-il compacter la base pour le rendre indexé?

Merci de ton aide.

Invité · 17/10/2017, 09h07

Bonjour,
l'indexation est immédiate, elle permet de créer dans une table système trier par ordre alphanumérique une copie des index, la recherche ce fait par dichotomie donc plus rapide!

et en plus tu n'as pas à le gérer dans ta requête qui elle ne change pas!

**jojo86** · 17/10/2017, 09h24

Envoyé par dysorthographie

Bonjour,
l'indexation est immédiate, elle permet de créer dans une table système trier par ordre alphanumérique une copie des index, la recherche ce fait par dichotomie donc plus rapide!

et en plus tu n'as pas à le gérer dans ta requête qui elle ne change pas!

Merci, j'avais éditer mon précédent message pendant que tu me répondais, tu n'as donc pas du voir ma modification !
La voici :
J'ai 12300 lignes et la requete tourne pendant 4 minutes, ça me semble beaucoup... Je ne pige pas. Je suis reparti d'une ancienne base access que j'ai épurée en supprimant les tables inutiles. Je vais peut être repartir d'un fichier a plat pour voir.
Après test sur un nouveau fichier tout neuf, le temps de traitement est le même...