Discussion :

[sackSin]

Bonjour,

Je développe une application sous VBA access afin d'automatiser certaines requêtes access.

Je suis arrivé à implémenter une par une toutes les requêtes nécessaires pour la mettre en place de mon application.

Dans un premier temps j'ai constaté que en mettant

Code :

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

SELECT*INTO Table FROM(...)

pour créer les tables intermédiaires sur chacune de ces requêtes, mon programme devient très lent.

Par exemple avec cette requête

Code :

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

con.Execute "SELECT*FROM " & TableAccessModif & " WHERE NOT EXISTS( SELECT * FROM " & nomTableVerif & " WHERE " & TableAccessModif & ".EX_ASS=" & nomTableVerif & ".ANNEE);"

qui s'exécute assez rapidement

si je remplace par celui-là

Code :

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

con.Execute "SELECT * INTO " & nomTableRejetExAss & " FROM (SELECT*FROM " & TableAccessModif & " WHERE NOT EXISTS( SELECT * FROM " & nomTableVerif & " WHERE " & TableAccessModif & ".EX_ASS=" & nomTableVerif & ".ANNEE));"

le temps d'exécution explose.

Enfin quand je mets tous les bouts de code ensemble pour les exécuter ensemble alors le programme plante et l'application se referme.

Qu'est ce que je peux faire pour arriver à optimiser ce programme. Un grand MERCI.

[User]

Bonjour,

Avez-vous essayé comme ça :

Code :

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

con.Execute "SELECT * INTO " & nomTableRejetExAss & " FROM " & TableAccessModif & " WHERE NOT EXISTS( SELECT * FROM " & nomTableVerif & " WHERE " & TableAccessModif & ".EX_ASS=" & nomTableVerif & ".ANNEE);"

Cdlt,

[Arkham46]

Bonjour,

Mettre un index sur ex_as (si cela en vaut la peine, ça dépend de la répartition des données, c'est à dire le nombre de ex_ass différents, le nombre d'enregistrements par ex_ass).

Eviter EXISTS, utiliser plutôt une jointure à gauche pour une requête de non correspondance.

[Simplifi]

Hello
j'ai testé différentes rapidité d'exécution
pour les petits nombres d'enregistrements, les requêtes sont plus lentes que le travail en recordset
par contre pour les très grands nombres d'enregistrements c'est le contraire.
personnellement, n'étant pas un dieu du SQL je préfère le mode graphique pour les créer et j'enregistre toujours un exemple même quand j'ai besoin du SQL dans ma programmation

PS: donne ton problème de base, il est peut être encore plus simple que tu ne l'imagine

[sackSin]

Hello
j'ai testé différentes rapidité d'exécution
pour les petits nombres d'enregistrements, les requêtes sont plus lentes que le travail en recordset
par contre pour les très grands nombres d'enregistrements c'est le contraire.
personnellement, n'étant pas un dieu du SQL je préfère le mode graphique pour les créer et j'enregistre toujours un exemple même quand j'ai besoin du SQL dans ma programmation

PS: donne ton problème de base, il est peut être encore plus simple que tu ne l'imagine

Hello ! Merci à vous tous pour votre intérêt à mon pb.

J'ai une table de données Excel qui a plusieurs colonnes parmi lesquelles CONTRAT (la référence du contrat) et Ex_ass (exercice d'assurance).
En me servant d'une base access je veux séparer ces données Excel en trois blocs :

==>Donnée 1 : Données n'ayant pas de référence contrat (CONTRAT) dans la base access
==>Donnée 2 : Données qui ont pour exercice d'assurance inférieure ou égale à une date se trouvant dans la Base access (cette date pourrait être l'année de souscription)
==>Donnée 3 : Le reste des données qui ne respecte pas les deux cas précédents.

Pour terminer je dois retranscrire le fichier Donnée 3 en format txt.

Je précise que le fichier Excel initial nécessite un certain nombre de traitement intermédiaire pour créer de nouvelles colonnes et en supprimer d'autres avant de procéder à la séparation des données.

J'ai fais le choix d'exécuter toutes ces opérations sous VBA access avant de retourner les résultats sur excel. Pendant le traitement sous access je choisis les trois noms suivants pour les trois blocs de fichiers excel attendus:

Donnée 1 devient TableRejetContrat
Donnée 2 devient TableRejetExAss
Donnée 3 devient TableTxt

Ma démarche est la suivante :

1. Créer une table dans la base access pour recevoir les données du fichier Excel
   

   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
   Function CreateTable(dBzf As String, NomTableAccess As String)
       Dim testControl As Boolean
       testControl = TableExiste(dBzf, NomTableAccess)
       If testControl = True Then
           Call DeleteTable(dBzf, NomTableAccess)
       End If
       'This example uses ADODB instead of the DAO shown in the previous
       'ones because DAO does not support the DECIMAL and GUID data types
       Dim con As New ADODB.Connection
       con.Open "provider=Microsoft.ACE.OLEDB.12.0; data source=" & dBzf
       con.Execute "" _
           & "CREATE TABLE [" & NomTableAccess & "](" _
               & " [Auto]                      COUNTER" _
               & ",[DATE_DEBUT]                DATETIME" _
               & ",[DATE_FIN]                  DATETIME" _
               & ",[CONTRAT]                   CHAR" _
               & ",[CATEGORIE]                 MEMO" _
               & ",[RAISON_SOCIALE]            MEMO" _
               & ",[< OU = 2017]               DECIMAL(18,5)" _
               & ",[2018]                      DECIMAL(18,5)" _
               & ",[2019]                      DECIMAL(18,5)" _
               & ",[TOTAL]                     DECIMAL(18,5)" _
               & ",[CONTRATCO]                 MEMO" _
               & ",CONSTRAINT [PrimaryKey] PRIMARY KEY ([Auto])" _
           & ");"
       con.Execute "CREATE INDEX [Multi-Field Index] ON [" & NomTableAccess & "]([Auto]);"
       con.Close
   End Function

2. J'importe les données du fichier excel vers la base access
   

   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
   Function ExcelToAccess(LienBD As String, TableBD As String, FichierExcel As String, TableExcel As String)
       Dim Cn As New ADODB.Connection
       Dim oProdRS As New ADODB.Recordset
       Dim oRS As New ADODB.Recordset
       Dim oConn As New ADODB.Connection
       Dim j As Integer
       '----------------------------------------------------------------------------------------------
       'Créer une table access
       Call CreateTable(LienBD, TableBD)
       'Connection à la Base Access
       oConn.Open "provider=Microsoft.ACE.OLEDB.12.0; data source=" & LienBD
       'les données seront placés dans la table créer précédenmment
       oRS.Open "SELECT * FROM " & TableBD, oConn, adOpenDynamic, adLockOptimistic
       '----------------------------------------------------------------------------------------------
       'Connection au classeur Excel
       Cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
           "Data Source=" & FichierExcel & ";" & _
           "Extended Properties=""Excel 8.0;"""
       'requête pour extraire les données de la Feuil1
       oProdRS.Open "SELECT * FROM [" & TableExcel & "$]", Cn, adOpenStatic
       ' ---------------------------------------------------------------------------------------------
       'Transfert des données dans la base
       Do While Not (oProdRS.EOF)
           oRS.AddNew
           For j = 0 To oRS.Fields.Count - 2
               oRS.Fields(j + 1).Value = oProdRS.Fields(j).Value
           Next j
           oRS.Update
           oRS.Filter = adFilterNone
           oProdRS.MoveNext
       Loop
       '-------------------------------------------
       oProdRS.Close
       Cn.Close
       'FichierExcel = Dir
       'Loop
       oRS.Close
       oConn.Close
   End Function

3. Requête pour mettre la table importée sous la disposition attendue
   

   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
   Function TableTrans(LienTabATrans As String, TableATranposer As String, CodeGest As String, TabTransposee As String)
       'Verifie si la table TableTrans existe alors elle sera supprimée
       Dim testControl As Boolean
       testControl = TableExiste(LienTabATrans, TabTransposee)
       If testControl = True Then
           Call DeleteTable(LienTabATrans, TabTransposee)
       End If
       'Se connecter à la base access et executer la requete
       Dim con As New ADODB.Connection
       con.Open "provider=Microsoft.ACE.OLEDB.12.0; data source=" & LienTabATrans
       con.Execute "" _
                   & "SELECT * INTO " & TabTransposee & " FROM (" _
                   & "SELECT " & Chr(34) & CodeGest & Chr(34) & " AS CODE_GESTIONNAIRE, CONTRAT, RAISON_SOCIALE, CATEGORIE, DATE_DEBUT, DATE_FIN, " & Chr(34) & "2017" & Chr(34) & " AS EX_ASS, [< OU = 2017] AS MONTANT_FACTURE FROM " & TableATranposer & " UNION ALL " _
                   & "SELECT " & Chr(34) & CodeGest & Chr(34) & " AS CODE_GESTIONNAIRE, CONTRAT, RAISON_SOCIALE, CATEGORIE, DATE_DEBUT, DATE_FIN, " & Chr(34) & "2018" & Chr(34) & " AS EX_ASS, [2018] AS MONTANT_FACTURE FROM " & TableATranposer & " UNION ALL " _
                   & "SELECT " & Chr(34) & CodeGest & Chr(34) & " AS CODE_GESTIONNAIRE,CONTRAT, RAISON_SOCIALE, CATEGORIE, DATE_DEBUT, DATE_FIN, " & Chr(34) & "2019" & Chr(34) & " AS EX_ASS, [2019] AS MONTANT_FACTURE FROM " & TableATranposer & ") WHERE MONTANT_FACTURE <> 0 ;"
       con.Close
   End Function

4. Requête sur la base acces pour créer la table qui me servira à interroger la table
   

   Code :

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

   1
   2
       con.Execute "SELECT * INTO " & nomTableVerif0 & " FROM (SELECT Gruadcom.num, Gruadcom.police, Gruadcom.ug, Gruadcom.type_com, Gruadcom.DISPONIBLE AS fin, Gruadcom.gar1, Gruabadd.I FROM Gruadcom INNER JOIN Gruabadd ON (Gruadcom.ug = Gruabadd.UG) AND (Gruadcom.police = Gruabadd.POLICE) AND (Gruadcom.num = Gruabadd.CONTRAT) GROUP BY Gruadcom.num, Gruadcom.police, Gruadcom.ug, Gruadcom.type_com, Gruadcom.DISPONIBLE, Gruadcom.gar1, Gruabadd.I HAVING (((Gruadcom.type_com)=" & Chr(34) & "gest" & Chr(34) & ") AND ((Gruadcom.gar1)=" & Chr(34) & "0041" & Chr(34) & "))) WHERE Gruabadd.I NOT IN (" & Chr(39) & "0" & Chr(39) & ", " & Chr(39) & "9" & Chr(39) & ", " & Chr(39) & "C" & Chr(39) & ") ;"  
       con.Execute "SELECT * INTO " & nomTableVerif & " FROM (SELECT " & Chr(39) & "A" & Chr(39) & " & num & police & ug AS CONTRAT, MID(fin, 1, 4) AS ANNEE FROM " & nomTableVerif0 & ");"

5. Requête pour créer la table de "TableTxt" (que je vais retranscrire en txt)
   

   Code :

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

      con.Execute "SELECT * INTO " & nomTableTxt & " FROM (SELECT*FROM " & TableAccessModif & " INNER JOIN " & nomTableVerif & " ON (" & TableAccessModif & ".CONTRAT = " & nomTableVerif & ".CONTRAT) AND (" & TableAccessModif & ".EX_ASS > " & nomTableVerif & ".ANNEE));"

6. Requête pour créer la table de "TableRejetContrat" pour les contrats non-référencés
   

   Code :

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

       con.Execute "SELECT * INTO " & nomTableRejetContrat & " FROM (SELECT*FROM " & TableAccessModif & " WHERE NOT EXISTS( SELECT * FROM " & nomTableVerif & " WHERE " & TableAccessModif & ".CONTRAT=" & nomTableVerif & ".CONTRAT));"

7. Enfin requête pour créer la table "TableRejetExAss"

Code :

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

   con.Execute "SELECT * INTO " & nomTableRejetExAss & " FROM (SELECT*FROM " & TableAccessModif & " WHERE NOT EXISTS( SELECT * FROM " & nomTableVerif & " WHERE " & TableAccessModif & ".EX_ASS=" & nomTableVerif & ".ANNEE));"

A ce niveau sans chercher à retourner mes trois table dans excel, j'ai voulu rassembler tous les codes précédents dans un module principal

Code :

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

   Sub main() End Sub

et le code plante.

[sackSin]

Problème résolu !