Optimiser requête SQL sous VBA access

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:

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:

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:

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.

Bonjour,

Avez-vous essayé comme ça :

Code:

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

Cdlt,

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.

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

Citation:

Envoyé par 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

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 :
Créer une table dans la base access pour recevoir les données du fichier Excel
Code:

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
J'importe les données du fichier excel vers la base access
Code:

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
Requête pour mettre la table importée sous la disposition attendue
Code:

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
Requête sur la base acces pour créer la table qui me servira à interroger la table
Code:

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 & ");"
Requête pour créer la table de "TableTxt" (que je vais retranscrire en txt)

Code:

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

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

Code:

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

Enfin requête pour créer la table "TableRejetExAss"
Code:

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:

Sub main() End Sub

et le code plante.

Problème résolu !

Bonsoir,

Oui, comme les programmeurs qui utilisent un autre environnement qu'Access, ils ont tendance à utiliser ADO au lieu de DAO ;)

Cdlt,