Importation d’un fichier SQL

par Hervé Inisan · Publié 16 juin 2012 · Mis à jour 2 mars 2017

Je souhaiterais exécuter une série d’instructions SQL placées dans un fichier texte. Comment faire ?

Scénario

En général, quand vous importez des données dans Access, elles sont dans un format texte (délimité/CSV, largeur fixe), ou Excel. Dans ce cas, vous n’êtes pas concerné par cet article. 🙂

Par contre, ce qui suit peut vous intéresser si :

vous devez injecter des données à partir d’un fichier SQL contenant une série de INSERT INTO (ou DELETE, UPDATE).
vous devez modifier la structure de vos tables à partir d’instructions SQL ALTER TABLE. Ce scénario est pratique si vous devez faire évoluer une base « à distance », sans pouvoir intervenir manuellement dessus. Une approche peut consister à faire exécuter une séquence d’instructions SQL sur la base à modifier.

Par exemple, voici une séquence SQL simple qu’on souhaite dérouler dans Access :

-- Ajouter un champ Email
ALTER TABLE [tbl Newsletter]
  ADD COLUMN Email TEXT(200);

-- Une personne
INSERT INTO [tbl Newsletter] (Nom, Prénom, Email)
  VALUES('Inisan', 'Hervé', 'test@test.com');
  
-- Une autre personne
INSERT INTO [tbl Newsletter] (Nom, Prénom, Email)
  VALUES('Zefilin', 'Agathe', 'agathe.zefilin@provider.com');

-- Ajouter un champ Email

ALTER TABLE [tbl Newsletter]

ADD COLUMN Email TEXT(200);

-- Une personne

INSERT INTO [tbl Newsletter] (Nom, Prénom, Email)

VALUES('Inisan', 'Hervé', 'test@test.com');

-- Une autre personne

INSERT INTO [tbl Newsletter] (Nom, Prénom, Email)

VALUES('Zefilin', 'Agathe', 'agathe.zefilin@provider.com');

Problèmes

Access ne gère pas les commentaires SQL (démarrant par deux tirets).
Une phrase SQL exécutée par CurrentDb.Execute ou DoCmd.RunSQL ne peut contenir qu’une seule instruction.

Solution

On va mettre en place une petite moulinette pour traiter ce type de fichiers et exécuter la séquence SQL comme il faut. J’en profite pour ajouter une journalisation simplifiée des actions, de façon à détecter facilement quelle instruction aurait échoué. Voici comment faire la mise en place :

Dans le menu Outils / Références de Visual Basic Editor, cochez la bibliothèque Microsoft Scripting Runtime. Elle est utilisée pour la lecture de fichiers texte.
Créez un module de classe (pas un module standard), et recopiez-y ce qui suit. La classe sera appelée SQLReader.

Option Compare Database
Option Explicit

Public Enum LogVerbosity
    none = 0
    Verbose = 1
    CommandsOnly = 2
End Enum

' ---
' ATTRIBUTS
' ---

Private m_SQLFile As String
Private m_logVerbosity As LogVerbosity
Private m_LogHandle As Integer

' ---
' PROPRIETES DE LA CLASSE
' ---

Property Get SQLFile() As String
    SQLFile = m_SQLFile
End Property

Property Let SQLFile(strSQLFile As String)
    If Dir(strSQLFile) = "" Then
        MsgBox "Fichier introuvable : " & strSQLFile
        m_SQLFile = ""
    Else
        m_SQLFile = strSQLFile
    End If
End Property

Property Get Verbosity() As LogVerbosity
    Verbosity = m_logVerbosity
End Property

Property Let Verbosity(vb As LogVerbosity)
    m_logVerbosity = vb
End Property

Property Get LogFile() As String
    LogFile = CurrentProject.Path & "\sqllog.txt"
End Property

' ---
' METHODES
' ---

Function Execute() As Boolean
    ' Charger le fichier de commandes SQL
    If Dir(m_SQLFile) = "" Then Exit Function
    
    Dim fso As Scripting.FileSystemObject
    Dim stm As Scripting.TextStream
    Dim strCmd As String
    Dim strLine As String
    
    Set fso = New Scripting.FileSystemObject
    
    ' Ouvrir le fichier SQL
    Set stm = fso.OpenTextFile(m_SQLFile, ForReading, False, TristateFalse)
    
    ' Ouvrir le fichier de journalisation
    LogOpen
    
    ' Lire les instructions SQL
    On Error GoTo SQLErr
    strCmd = ""
    While Not stm.AtEndOfStream
        ' La ligne...
        strLine = Trim(stm.ReadLine)
       
        If Left(strLine, 2) = "--" Then
            If Me.Verbosity = Verbose Then
                Log "Commentaire", strLine
            End If
        Else
            ' Instruction SQL
            strCmd = strCmd & strLine
            If Right(strLine, 1) = ";" Then
                ' Exécuter la commande
                CurrentDb.Execute strCmd
                Log "Exécution terminée.", strCmd
                strCmd = ""
            End If
        End If
    Wend
    
    ' Fermer le fichier de journalisation
    LogClose
    
    stm.Close
    Access.Application.RefreshDatabaseWindow
    Execute = True
    Exit Function
    
SQLErr:
    Log "ERREUR", strCmd
    LogClose
    Execute = False
    Exit Function
End Function

' ---
' GESTION DE LA JOURNALISATION
' ---

Private Sub LogOpen()
    If Me.Verbosity <> LogVerbosity.none Then
        m_LogHandle = FreeFile
        
        Open Me.LogFile For Output As #m_LogHandle
    End If
End Sub

Private Sub LogClose()
    If Me.Verbosity <> LogVerbosity.none Then
        Close #m_LogHandle
    End If
End Sub

Private Sub Log(strMessage As String, strSQL As String)
    If Me.Verbosity = none Then Exit Sub
    
    Print #m_LogHandle, strMessage
    If strSQL <> "" Then
        Print #m_LogHandle, "> " & strSQL
    End If
    Print #m_LogHandle,
End Sub

Private Sub Class_Initialize()
    Me.Verbosity = Verbose
End Sub

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

Option Compare Database

Option Explicit

Public Enum LogVerbosity

none = 0

Verbose = 1

CommandsOnly = 2

End Enum

' ---

' ATTRIBUTS

' ---

Private m_SQLFile As String

Private m_logVerbosity As LogVerbosity

Private m_LogHandle As Integer

' ---

' PROPRIETES DE LA CLASSE

' ---

Property Get SQLFile() As String

SQLFile = m_SQLFile

End Property

Property Let SQLFile(strSQLFile As String)

If Dir(strSQLFile) = "" Then

MsgBox "Fichier introuvable : " & strSQLFile

m_SQLFile = ""

Else

m_SQLFile = strSQLFile

End If

End Property

Property Get Verbosity() As LogVerbosity

Verbosity = m_logVerbosity

End Property

Property Let Verbosity(vb As LogVerbosity)

m_logVerbosity = vb

End Property

Property Get LogFile() As String

LogFile = CurrentProject.Path & "\sqllog.txt"

End Property

' ---

' METHODES

' ---

Function Execute() As Boolean

' Charger le fichier de commandes SQL

If Dir(m_SQLFile) = "" Then Exit Function

Dim fso As Scripting.FileSystemObject

Dim stm As Scripting.TextStream

Dim strCmd As String

Dim strLine As String

Set fso = New Scripting.FileSystemObject

' Ouvrir le fichier SQL

Set stm = fso.OpenTextFile(m_SQLFile, ForReading, False, TristateFalse)

' Ouvrir le fichier de journalisation

LogOpen

' Lire les instructions SQL

On Error GoTo SQLErr

strCmd = ""

While Not stm.AtEndOfStream

' La ligne...

strLine = Trim(stm.ReadLine)

If Left(strLine, 2) = "--" Then

If Me.Verbosity = Verbose Then

Log "Commentaire", strLine

End If

Else

' Instruction SQL

strCmd = strCmd & strLine

If Right(strLine, 1) = ";" Then

' Exécuter la commande

CurrentDb.Execute strCmd

Log "Exécution terminée.", strCmd

strCmd = ""

End If

Wend

' Fermer le fichier de journalisation

LogClose

stm.Close

Access.Application.RefreshDatabaseWindow

Execute = True

Exit Function

SQLErr:

Log "ERREUR", strCmd

LogClose

Execute = False

Exit Function

End Function

' ---

' GESTION DE LA JOURNALISATION

' ---

Private Sub LogOpen()

If Me.Verbosity <> LogVerbosity.none Then

m_LogHandle = FreeFile

Open Me.LogFile For Output As #m_LogHandle

End If

End Sub

Private Sub LogClose()

If Me.Verbosity <> LogVerbosity.none Then

Close #m_LogHandle

End If

End Sub

Private Sub Log(strMessage As String, strSQL As String)

If Me.Verbosity = none Then Exit Sub

Print #m_LogHandle, strMessage

If strSQL <> "" Then

Print #m_LogHandle, "> " & strSQL

End If

Print #m_LogHandle,

End Sub

Private Sub Class_Initialize()

Me.Verbosity = Verbose

End Sub

Tester le code

Pour tester tout ça :

Créez un fichier SQL, par exemple en recopiant les instructions SQL plus haut. Dans ce cas, votre base est supposée avoir une table tbl Newsletter, avec des champs Nom (Texte) et Prénom (Texte). Si ce n’est pas le cas, une erreur se produira (mais ça peut être l’occasion de tester la gestion des erreurs par SQLReader !).
Créez un module standard, et recopiez ce code, en aménageant le chemin du fichier SQL :

' ---
' IMPORTATION DE FICHIER SQL
' ---
'
Sub SQLReaderTest()
    Dim sr As SQLReader
    
    ' Initialiser le SQLReader
    Set sr = New SQLReader
    sr.SQLFile = "C:UsersHervéDesktoptest.sql"
    sr.Verbosity = Verbose
    
    ' Exécuter les commandes SQL
    If Not sr.Execute Then
        MsgBox "Des erreurs sont survenues, consultez le journal.", vbExclamation
        Shell "notepad.exe """ & sr.LogFile & """", vbNormalFocus
    Else
        MsgBox "Exécution terminée.", vbInformation
    End If
    
    ' Terminé !
    Set sr = Nothing
End Sub

' ---

' IMPORTATION DE FICHIER SQL

' ---

Sub SQLReaderTest()

Dim sr As SQLReader

' Initialiser le SQLReader

Set sr = New SQLReader

sr.SQLFile = "C:UsersHervéDesktoptest.sql"

sr.Verbosity = Verbose

' Exécuter les commandes SQL

If Not sr.Execute Then

MsgBox "Des erreurs sont survenues, consultez le journal.", vbExclamation

Shell "notepad.exe """ & sr.LogFile & """", vbNormalFocus

Else

MsgBox "Exécution terminée.", vbInformation

End If

' Terminé !

Set sr = Nothing

End Sub

Ce code crée une instance de SQLReader, renseigne le chemin du fichier SQL à charger, définit le niveau de détail (Verbosity) du journal d’erreurs, et exécute la séquence SQL. En cas d’erreur, le bloc-notes Windows affiche le journal des erreurs.

Journal d’exécution

Un journal trace l’exécution de chaque commande.

Par défaut, il s’appelle sqllog.txt, et il est placé dans le même dossier que la base de données.
Vous pouvez définir son niveau de détail comme plus haut. sr.Verbosity = Verbose (maximum de détails), sr.Verbosity = CommandsOnly (seules les commandes SQL sont tracées, pas les commentaires), sr.Verbosity = none (rien n’est journalisé).

Étiquettes : access 2010 fichier sql table vba

Walid dit :

26 décembre 2016 à 12 h 16 min

Bonjour,

Je me permets un tout petit commentaire. A l’exécution le log n’est pas placé dans le même dossier que la base de données mais dans le dossier parent (en tout cas sur mon poste)
Pour corriger cela il faut modifier la fonction de la manière suivante :
Property Get LogFile() As String
LogFile = CurrentProject.Path & « \ » & « sqllog.txt »
End Property

C’est juste un petit détail.. En tout cas merci mile fois 🙂

Répondre
- Hervé Inisan dit :
  
  2 mars 2017 à 22 h 47 min
  
  Merci pour le retour. Effectivement, lorsque j’ai migré le blog il y a quelque temps, des \ ont été avalés par la procédure. En voici un ! 🙂
  J’ai corrigé sur l’article (on peut simplifier votre concaténation).
  
  Répondre
Jµstin dit :

6 février 2015 à 18 h 52 min

Bonjour Hervé,
Tout d’abord, merci beaucoup de tous vos posts qui me sont toujours d’une grande aide…
J’arrive à faire fonctionner l’importation de fichier avec votre exemple, pensez-vous qu’il est possible d’importer des fichiers SQL pour automatiser la création de requête via SELECT ?
Sans surprise, j’ai tout de même fait le test mais cela ne fonctionne pas ^^

Merci de votre réponse

Répondre
- Hervé Inisan dit :
  
  7 février 2015 à 9 h 42 min
  
  Le code fourni est plutôt prévu pour exécuter des instructions SQL en séquence (des requêtes Action au sens Access, c’est-à-dire des requêtes qui ne renvoient pas de résultat). Du coup effectivement :
  – Les requêtes SELECT ne fonctionneront pas dans ce scénario.
  – L’outil ne crée pas d’objets dans la base de données, comme des nouvelles requêtes.
  Si j’ai suivi, l’idée est de stocker des instructions SELECT et de les créer automatiquement dans la base ?
  
  Répondre
Somers Jean dit :

6 mai 2014 à 14 h 50 min

Bonjour, Hervé.

Je rencontre un problème à l’exécution du module standard: Erreur de compilation: type défini par l’utilisateur non défini.

Après validation, ce qui est surligné est: Function Execute() As Boolean.

As-tu un indice?!!!
Merci.

Répondre
- Hervé Inisan dit :
  
  6 mai 2014 à 19 h 27 min
  
  Somers Jean > Le code est bien copié dans un module de classe (appelé SQLReader) ?
  Et la référence Microsoft Scripting Runtime est bien activée ?
  Sinon, au cas où : où s’arrête la compilation après un Debogage/Compiler ?
  
  Répondre
Hervé Inisan dit :

20 juin 2013 à 18 h 31 min

nmiyer > Tu as bien recopié la portion de code dans un module de classe ? (et en début de ce module ?)

Répondre
nmiyer dit :

19 juin 2013 à 9 h 55 min

Bonjour herve,

je n’arrive pas à faire fonctionner le code de ton importation de fichier j’ai une erreur au niveau de « Option Compare Database »
erreur attendu : texte ou Binary
Merci

Répondre

Importation d’un fichier SQL

Scénario

Problèmes

Solution

Tester le code

Journal d’exécution

Vous aimerez aussi...

8 réponses

Laisser un commentaire Annuler la réponse

En direct des forums self-access.com

Thèmes Access

Importation d’un fichier SQL

Scénario

Problèmes

Solution

Tester le code

Journal d’exécution

Vous aimerez aussi...

Calcul de rang

Ajouter une année à une date

Calcul de rang avec critère

8 réponses

Laisser un commentaire Annuler la réponse

En direct des forums self-access.com

Thèmes Access