Optimisation d'un code

**toufik135** · 19/01/2014, 17h07

Bonjour à tous,

Je fais une simple requête select all from une table de ma base de donnée (comportant 45000 lignes).
Le soucis est qu'excel met 8 minutes

avant de terminer et d'afficher les résulats sur une feuille.

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
 
Private Sub CommandButton1_Click()
 
    Dim cnn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim rsSQL As String
    Dim Rw, c, Col As Long
    Dim MyField, Location As Range
 
    Application.Calculation = xlCalculationManual    
    Application.ScreenUpdating = False
 
    Set cnn = New ADODB.Connection
    Set rs = New ADODB.Recordset
    Set Location = [A2]
 
    cnn.Open "Driver={SQL Server};Server=pnd-fp01\kluwer2005;Database=Exp_GSI;User Id=****;Password=****;"
    rsSQL = "SELECT * from matable"
    rs.Open rsSQL, cnn, adOpenForwardOnly
 
    Rw = Location.Row
    Col = Location.Column
    c = Col
    With rs
         While Not .EOF
              For Each MyField In rs.Fields
                Cells(Rw, c) = MyField
                c = c + 1
                Next MyField
        rs.MoveNext
        Rw = Rw + 1
        c = Col
        Wend
    End With
 
    rs.Close
    Set rs = Nothing
    Set cnn = Nothing
 
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
 
End Sub

Invité · 19/01/2014, 17h56

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
Private Sub CommandButton1_Click()
 
    Dim cnn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim rsSQL As String
      Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
 
    Set cnn = New ADODB.Connection
    Set rs = New ADODB.Recordset
 
    cnn.Open "Driver={SQL Server};Server=pnd-fp01\kluwer2005;Database=Exp_GSI;User Id=****;Password=****;"
    rsSQL = "SELECT * from matable"
    rs.Open rsSQL, cnn, adOpenForwardOnly
 
 
   If rs.EOF = False Then
    Range("A2").CopyFromRecordset rs
   End If
 
 
 
    rs.Close
    Set rs = Nothing
    Set cnn = Nothing
 
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
 
End Sub

**toufik135** · 19/01/2014, 22h43

Cela fonctionne sur cette requête mais lorsque j'exécute une grande requête je rencontre une erreur 3704 Cette opération n'est pas autorisé si l'objet est fermé... ??

à la ligne :

Code :

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

1
2
 
If rs.EOF = False Then

Code complet :

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
64
65
66
67
68
69
70
 
Private Sub CommandButton1_Click()
 
    Dim cnn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim rsSQL As String
    Dim Rw, c, Col As Long
    Dim MyField, Location As Range
 
    Application.ScreenUpdating = False
 
    Set cnn = New ADODB.Connection
    Set rs = New ADODB.Recordset
 
    cnn.Open "Driver={SQL Server};Server=******;Database=Exp_GSI;User Id=*****;Password=*****;"
 
    rsSQL = "DELETE FROM QRYT_18012014_185702613 "
 
    rsSQL = rsSQL & "INSERT INTO QRYT_18012014_185702613(tdbetkla_prime, tddockla_prime, tdjaar_prime, tddagbfin_prime, tddagbdiv_prime, tddivdet_prime, tdfindet_prime,"
    rsSQL = rsSQL & "tdbetkla_docnr, tdbetkla_boekper, tdbetkla_btwper, tdbetkla_datum, tdbetkla_update, tdbetkla_ref, tdbetkla_bedragd, tdbetkla_bedragc, tdbetkla_bedrag, "
    rsSQL = rsSQL & "tdbetkla_bedragbasd, tdbetkla_bedragbasc, tdbetkla_bedragbas)"
    rsSQL = rsSQL & "SELECT tdbetkla.tdbetkla_prime, tdbetkla.tddockla_prime, tdbetkla.tdjaar_prime, tdbetkla.tddagbfin_prime, tdbetkla.tddagbdiv_prime, tdbetkla.tddivdet_prime,"
    rsSQL = rsSQL & "tdbetkla.tdfindet_prime,  tdbetkla.tdbetkla_docnr, tdbetkla.tdbetkla_boekper, tdbetkla.tdbetkla_btwper, tdbetkla.tdbetkla_datum,  tdbetkla.tdbetkla_update,"
    rsSQL = rsSQL & "tdbetkla.tdbetkla_ref, tdbetkla.tdbetkla_bedragd, tdbetkla.tdbetkla_bedragc, tdbetkla.tdbetkla_bedrag, tdbetkla.tdbetkla_bedragbasd, tdbetkla.tdbetkla_bedragbasc,"
    rsSQL = rsSQL & "tdbetkla.tdbetkla_bedragbas      FROM tdbetkla WHERE ((tdbetkla.tdjaar_prime < 4) or      (tdbetkla.tdjaar_prime = 4 and tdbetkla.tdbetkla_boekper <= 12))"
 
    rsSQL = rsSQL & "SELECT tddockla.tddockla_prime, tddockla.tdalgrek_prime, tddagbdiv.tddagbdiv_prime, tddagbdiv.tddagbdiv_alfa,     tddagbfin.tddagbfin_prime, tddagbfin.tddagbfin_alfa, tddagbver.tddagbver_prime, tddagbver.tddagbver_alfa,"
    rsSQL = rsSQL & "tddockla.tdjaar_prime , tdjaar.tdjaar_alfa, tddockla.tdverkoop_prime, tddockla.tddivdet_prime, tddockla.tdfindet_prime, tdklant.tdklant_prime, tdklant.tdklant_alfa, tdklant.tdklant_naam, tdklant.tdklant_straat, tdklant.tdklant_huisnr,"
    rsSQL = rsSQL & "tdklant.tdklant_busnr , tdklant.tdklant_postnr, tdklant.tdklant_woonplaats, tdklant.tdklant_telnr, tdklant.tdklant_faxnr, tdklant.tdklant_gsmnr, tdklant.tdklant_email, tdklant.tdklant_vrijveld1, tdklant.tdklant_vrijveld2, tdmunt.tdmunt_prime, tdmunt.tdmunt_decim,"
    rsSQL = rsSQL & "tddockla.tddockla_docsoort , tddockla.tddockla_docnr, tddockla.tddockla_boekper, tddockla.tddockla_btwper, tddockla.tddockla_datum, tddockla.tddockla_verval, tddockla.tddockla_update, tddockla.tddockla_koers, tddockla.tddockla_wo, tddockla.tddockla_onzeref, tddockla.tddockla_uwref, "
    rsSQL = rsSQL & "tddockla.tddockla_ref, tddockla.tddockla_bedragd,"
    rsSQL = rsSQL & "tddockla.tddockla_bedragc, tddockla.tddockla_bedrag, tddockla.tddockla_bedragbasd, tddockla.tddockla_bedragbasc, "
    rsSQL = rsSQL & "tddockla.tddockla_bedragbas, tddockla.tddockla_debcre, tddockla.tddockla_konkort, tddockla.tddockla_toestand,"
    rsSQL = rsSQL & "(tddockla.tddockla_bedragbasd - tddockla.tddockla_bedragbasc) as bedragbas,     (tddockla.tddockla_bedragd - tddockla.tddockla_bedragc) as bedrag,"
    rsSQL = rsSQL & "ISNULL((SELECT SUM(tdbetkla_bedragd - tdbetkla_bedragc) FROM tdbetkla "
    rsSQL = rsSQL & "WHERE tdbetkla.tddockla_prime = tddockla.tddockla_prime and ((tdbetkla.tdjaar_prime < 4) or (tdbetkla.tdjaar_prime = 4 and tdbetkla_boekper <= 12))), 0) AS betaald, ISNULL((SELECT SUM(tdbetkla_bedragbasd - tdbetkla_bedragbasc) "
    rsSQL = rsSQL & "FROM tdbetkla    WHERE tddockla.tddockla_prime = tdbetkla.tddockla_prime and ((tdbetkla.tdjaar_prime < 4) or (tdbetkla.tdjaar_prime = 4 and tdbetkla_boekper <= 12))), 0) "
    rsSQL = rsSQL & "AS betaaldbas, tddockla.tddockla_bedragd - tddockla.tddockla_bedragc +      COALESCE((SELECT SUM(tdbetkla_bedragd - "
    rsSQL = rsSQL & "tdbetkla_bedragc) FROM tdbetkla    WHERE tddockla.tddockla_prime = tdbetkla.tddockla_prime and ((tdbetkla.tdjaar_prime < 4) or (tdbetkla.tdjaar_prime = 4 and tdbetkla_boekper <= 12))), 0) "
    rsSQL = rsSQL & "AS saldo, tddockla.tddockla_bedragbasd - tddockla.tddockla_bedragbasc +     COALESCE((SELECT SUM(tdbetkla_bedragbasd - tdbetkla_bedragbasc) FROM tdbetkla "
    rsSQL = rsSQL & "WHERE tddockla.tddockla_prime = tdbetkla.tddockla_prime and ((tdbetkla.tdjaar_prime < 4) or (tdbetkla.tdjaar_prime = 4 and tdbetkla_boekper <= 12))), 0) "
    rsSQL = rsSQL & "AS saldobas, tdbetkla_1.tdbetkla_prime as tdbetkla_prime_bet, tddagbdiv_1.tddagbdiv_prime as tddagbdiv_prime_bet,    tddagbdiv_1.tddagbdiv_alfa as tddagbdiv_alfa_bet, tddagbfin_1.tddagbfin_prime as tddagbfin_prime_bet, "
    rsSQL = rsSQL & "  tddagbfin_1.tddagbfin_alfa as tddagbfin_alfa_bet, tdjaar_1.tdjaar_prime as tdjaar_prime_bet,    tdjaar_1.tdjaar_alfa as tdjaar_alfa_bet,    tdbetkla_1.tddivdet_prime as tddivdet_prime_bet, tdbetkla_1.tdfindet_prime as tdfindet_prime_bet, tdbetkla_1.tdbetkla_docnr as tdbetkla_docnr_bet, tdbetkla_1.tdbetkla_boekper "
    rsSQL = rsSQL & " as tdbetkla_boekper_bet,    tdbetkla_1.tdbetkla_btwper as tdbetkla_btwper_bet, tdbetkla_1.tdbetkla_datum as tdbetkla_datum_bet,    tdbetkla_1.tdbetkla_update as tdbetkla_update_bet, tdbetkla_1.tdbetkla_ref as tdbetkla_ref_bet, "
    rsSQL = rsSQL & "tdbetkla_1.tdbetkla_bedragd as tdbetkla_bedragd_bet, tdbetkla_1.tdbetkla_bedragc as tdbetkla_bedragc_bet,    tdbetkla_1.tdbetkla_bedrag as tdbetkla_bedrag_bet, tdbetkla_1.tdbetkla_bedragbasd as tdbetkla_bedragbasd_bet, "
    rsSQL = rsSQL & "tdbetkla_1.tdbetkla_bedragbasc as tdbetkla_bedragbasc_bet, tdbetkla_1.tdbetkla_bedragbas as tdbetkla_bedragbas_bet,    (tdbetkla_1.tdbetkla_bedragbasd - tdbetkla_1.tdbetkla_bedragbasc) as bedragbas_Bet, "
    rsSQL = rsSQL & "(tdbetkla_1.tdbetkla_bedragd - tdbetkla_1.tdbetkla_bedragc) as bedrag_Bet    FROM tddockla LEFT JOIN   tdmunt ON tddockla.tdmunt_prime = tdmunt.tdmunt_prime LEFT JOIN "
    rsSQL = rsSQL & "tdjaar ON tddockla.tdjaar_prime = tdjaar.tdjaar_prime LEFT OUTER JOIN QRYT_18012014_185702613 tdbetkla_1 LEFT JOIN   tdjaar tdjaar_1 ON tdbetkla_1.tdjaar_prime = tdjaar_1.tdjaar_prime "
    rsSQL = rsSQL & "ON tddockla.tddockla_prime = tdbetkla_1.tddockla_prime LEFT OUTER JOIN   tddagbfin tddagbfin_1 ON tdbetkla_1.tddagbfin_prime = tddagbfin_1.tddagbfin_prime LEFT OUTER JOIN "
    rsSQL = rsSQL & "tddagbfin ON tddockla.tddagbfin_prime = tddagbfin.tddagbfin_prime LEFT OUTER JOIN   tddagbdiv ON tddockla.tddagbdiv_prime = tddagbdiv.tddagbdiv_prime LEFT OUTER JOIN "
    rsSQL = rsSQL & "tddagbver ON tddockla.tddagbver_prime = tddagbver.tddagbver_prime LEFT OUTER JOIN   tdklant ON tddockla.tdklant_prime = tdklant.tdklant_prime LEFT OUTER JOIN "
    rsSQL = rsSQL & "tddagbdiv tddagbdiv_1 ON tdbetkla_1.tddagbdiv_prime = tddagbdiv_1.tddagbdiv_prime   WHERE (tddockla.tdalgrek_prime between 400000 and 499999) and (tdklant.tdklant_alfa between '10advertis' and 'zwevaengin') "
    rsSQL = rsSQL & "and ((tddockla.tdjaar_prime < 4) or (tddockla.tdjaar_prime = 4 and tddockla_boekper <= 12)) and tddockla_toestand >= 0 and tddockla_toestand <= 9 and tddockla.tddockla_bedragd - tddockla.tddockla_bedragc + "
    rsSQL = rsSQL & "ISNULL((SELECT SUM(tdbetkla_bedragd - tdbetkla_bedragc) FROM tdbetkla   WHERE tddockla.tddockla_prime = "
    rsSQL = rsSQL & "tdbetkla.tddockla_prime and ((tdbetkla.tdjaar_prime < 4) or (tdbetkla.tdjaar_prime = 4 and tdbetkla_boekper <= 12))), 0) <> 0 order by 15, 14, 34, 1, 69, 57"
 
    Set rs = cnn.Execute(rsSQL)
 
    If rs.EOF = False Then
        Range("A2").CopyFromRecordset rs
    End If
 
 
    rs.Close
    Set rs = Nothing
    Set cnn = Nothing
 
    Application.ScreenUpdating = True
 
End Sub

Merci pour votre aide,

**tellihi** · 19/01/2014, 23h09

Bonjour toufik,

tu peux tenter de diviser ta requête en 2 string et recordset distinct car je vois que tu fais une jointure sur la table où tu fais l'insertion (celle de ta première requête).

**toufik135** · 19/01/2014, 23h11

Merci, c'était cela mon problème, cela fonctionne mnt.

Optimisation d'un code [XL-2010]

Macros et VBA Excel

Vue hybride

Discussions similaires

Partager

Partager