Fonction qui renvoie une table contenant résultat d'une requête SQL sur une base de données Access

**Philippe Tulliez** · 11/08/2013, 14h52

Bonjour,
Je vous propose une petite procédure qui importe le résultat d'une requête faite sur une base de données Access dans une feuille Excel.
Cette requête est exécutée par une fonction nommée QueryAccess qui renvoie une table contenant le résultat d'une requête faite sur une base de données Access.

Référencement

Pour faire fonctionner la fonction, il est nécessaire de référencer Microsoft DAO 3.6 ObjectLibrary. Dans l'éditeur VBA Outils/Références...

Base de données

La base de données utilisée est Comptoir.mdb livrée avec Access.

Les variables servant au test

Query - Contient la requête SQL.
db - Nom du fichier contenant la base de données précédé du chemin complet
En production, ces deux variables sont placées dans une cellule mais pour une meilleure compréhension du code, j'ai utilisé des constantes.
shtExport - CodeName de la feuille Excel où a lieu l'exportation du résultat de la requête sur la base de données Access

Les arguments de la fonction

dbFullName de type String, contient le chemin complet + le nom de la base de données.
SqlQuery de type String, contient la requête SQL.

En-tête de module

Code :

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

1
2
3
Option Explicit
Const Query As String = "SELECT Clients.Société, Clients.Fonction, Clients.Ville, Clients.Région FROM Clients;"
Const db As String = "Z:\Test\_mso Vba - Access\DataBase\Comptoir.mdb"

La fonction QueryAccess

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
Function QueryAccess(dbFullName As String, SqlQuery As String)
 ' Nécessite de référencer Microsoft DAO x.x ObjectLibrary
 ' Arguments
 ' dbFullName ' Chemin +  nom du fichier
 ' SqlQuery ' Chaîne de caractère contenant la requête SQL
 '  Variables - Déclaration et affectation des valeurs
 Dim db As DAO.Database, Rs As DAO.Recordset
 Dim myTable(), count As Long, Elem As Integer
 Set db = Workspaces(0).OpenDatabase(dbFullName, ReadOnly:=True)
 Set Rs = db.OpenRecordset(SqlQuery)
 ' Lecture des enregistrements de la requête
 While Not Rs.EOF
  ReDim Preserve myTable(Rs.Fields.count, count)
  For Elem = 0 To Rs.Fields.count - 1
   If count = 0 Then
     myTable(Elem, count) = Rs(Elem).SourceField ' Etiquettes de colonnes
    Else
     myTable(Elem, count) = IIf(IsNull(Rs(Elem)), "", Rs(Elem))
   End If
  Next Elem
  count = count + 1: Rs.MoveNext
 Wend
 QueryAccess = Application.WorksheetFunction.Transpose(myTable)
 Rs.Close: db.Close: Set Rs = Nothing
End Function

La procédure de test

Code :

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

1
2
3
4
5
6
7
8
9
10
11
Sub TestQuery()
 Dim myTable(), dbExport As Range
 ' Dim db As String, Query As String
 ' db = shtParam.Range("pDataBase")
 ' Query = shtSql.Range("B3")
 myTable = QueryAccess(db, Query)
 With shtExport
  Set dbExport = .Range("A1", .Cells(UBound(myTable, 1), UBound(myTable, 2)))
 End With
 dbExport = myTable
End Sub

**davidbd** · 13/12/2013, 16h19

Bonjour,

J'ai un erreur avec shtExport, pourquoi? Il est écrit dans mon éditeur VBA que la variable n'est pas définie.

**illight** · 13/12/2013, 16h28

La réponse est ici :

shtExport - CodeName de la feuille Excel où a lieu l'exportation du résultat de la requête sur la base de données Access

**davidbd** · 13/12/2013, 16h50

Ouais, j'ai remplacé shtExport par Feuil1 et ça ne marche pas encore

**Philippe Tulliez** · 13/12/2013, 17h59

Bonjour,
CodeName est une propriété de l'objet WorkSheet (feuille).
Lorsque l'on est sur la feuille dont on veut modifier le CodeName, il suffit de sélectionner l'onglet [Developpeur] et cliquer sur la commande Propriété du groupe Contrôles. Dans la boîte de dialogue des propriétés de la feuille, il suffit de remplacer la propriété (Name) par son nom (par exemple ShtExport.
La propriété CodeName est différente de la propriété Name qui est la seule visible par l'utilisateur.

**davidbd** · 13/12/2013, 18h47

Je suis sur Excel 2003. Où se trouve cet onglet?

**Philippe Tulliez** · 13/12/2013, 19h00

Bonjour,
Si tu es sur Excel 2003, il faut passer obligatoirement par l'éditeur VBA.
Tu sélectionnes la feuille à renommer dans l'explorateur d'objet et ensuite tu modifies la propriété (Name) (Attention pas Name) et tu modifies le nom par shtExport.

**davidbd** · 13/12/2013, 19h05

C'est très clair comme explication! Je viens de jouer un petit peu avec votre code et j'ai ajusté le CodeName et tout fonctionne comme je le veux!

Merci beaucoup pour votre aide et j'espère que certains autres débutants pourront se dérouiller avec cette petite clairification!

**davidbd** · 06/01/2014, 14h25

Bonjour M. Tulliez,

J'ai peaufiné votre code et je suis maintenant en mesure de mettre en forme mon tableau (bordures, couleurs, caractères, etc...). Toutefois, je remarque que mon tableau n'importe pas la première ni la dernière ligne de la requête.
Avez-vous quelque chose à me proposer?

Merci pour votre réponse et bonne journée!

**Philippe Tulliez** · 12/01/2014, 16h37

Bonjour,
Merci de m'avoir signalé ce bug. Il y avait effectivement un problème mais d'après mes tests avec le premier enregistrement.
La correction est faite et j'en ai profité pour ajouter un argument facultatif WithLabel qui permet en le mettant à False de n'avoir que les données sélectionnées (sans les étiquettes de colonnes).

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
Function QueryAccess(dbFullName As String, SqlQuery As String, Optional WithLabel As Boolean = True)
 ' Philippe Tulliez
 ' v 2.0 - 11/01/14 ' Ajouté argument optionnel WithLabel
 ' Nécessite de référencer Microsoft DAO x.x ObjectLibrary
 ' Arguments
 ' dbFullName ' Chemin +  nom du fichier
 ' SqlQuery ' Chaîne de caractère contenant la requête SQL
 ' [WithLabel] ' True ou Omis renvoie les étiquettes de colonnes
' 
' Variables - Déclaration et affectation des valeurs
 Dim db As DAO.Database, Rs As DAO.Recordset
 Dim myTable(), count As Long, Elem As Integer
 Set db = Workspaces(0).OpenDatabase(dbFullName, ReadOnly:=True)
 Set Rs = db.OpenRecordset(SqlQuery)
 ' Lecture des enregistrements de la requête
 While Not Rs.EOF
  ReDim Preserve myTable(Rs.Fields.count, count)
  If count = 0 And WithLabel Then
   For Elem = 0 To Rs.Fields.count - 1: myTable(Elem, count) = Rs(Elem).SourceField: Next ' Etiquettes de colonnes
   count = count + 1: ReDim Preserve myTable(Rs.Fields.count, count)
  End If
  For Elem = 0 To Rs.Fields.count - 1
    myTable(Elem, count) = IIf(IsNull(Rs(Elem)), "", Rs(Elem))
  Next Elem
  count = count + 1: Rs.MoveNext
 Wend
 QueryAccess = Application.WorksheetFunction.Transpose(myTable)
 Rs.Close: db.Close: Set Rs = Nothing
End Function

**davidbd** · 22/01/2014, 18h35

Merci M. Tulliez!

Cette portion du code marche à merveille.

Merci et bonne journée!

**Philippe Tulliez** · 22/01/2014, 19h30

Bonsoir,

mais l'utilisation des constantes m'en empêche.

Si tu lis bien ce que j'ai écris dans mon explication. (en rouge)

Les variables servant au test
Query - Contient la requête SQL.
db - Nom du fichier contenant la base de données précédé du chemin complet
En production, ces deux variables sont placées dans une cellule mais pour une meilleure compréhension du code, j'ai utilisé des constantes.
shtExport - CodeName de la feuille Excel où a lieu l'exportation du résultat de la requête sur la base de données Access

Dans mon classeur de test, j'ai mis la requête dans une cellule donc il suffit de placer par exemple cinq requêtes dans cinq cellules d'une colonne et dans une boucle invoquer la fonction en passant comme argument le contenu de la cellule.

Fonction qui renvoie une table contenant résultat d'une requête SQL sur une base de données Access

Contribuez

Discussions similaires

Partager

Partager