VBA
Travailler avec ADO

Remarques

Connexion à une source de données

La première étape pour accéder à une source de données via ADO consiste à créer un objet ADO Connection . Cela se fait généralement en utilisant une chaîne de connexion pour spécifier les paramètres de la source de données, bien qu'il soit également possible d'ouvrir une connexion DSN en transmettant le DSN, l'ID utilisateur et le mot de passe à la méthode .Open .

Notez qu'un DSN n'est pas obligé de se connecter à une source de données via ADO - toute source de données disposant d'un fournisseur ODBC peut être connectée à l'aide de la chaîne de connexion appropriée. Bien que des chaînes de connexion spécifiques à différents fournisseurs soient hors de la portée de cette rubrique, ConnectionStrings.com est une excellente référence pour trouver la chaîne appropriée à votre fournisseur.

Const SomeDSN As String = "DSN=SomeDSN;Uid=UserName;Pwd=MyPassword;"

Public Sub Example()
    Dim database As ADODB.Connection
    Set database = OpenDatabaseConnection(SomeDSN)
    If Not database Is Nothing Then
        '... Do work.
        database.Close          'Make sure to close all database connections.
    End If
End Sub

Public Function OpenDatabaseConnection(ConnString As String) As ADODB.Connection
    On Error GoTo Handler
    Dim database As ADODB.Connection
    Set database = New ADODB.Connection
    
    With database
        .ConnectionString = ConnString
        .ConnectionTimeout = 10             'Value is given in seconds.
        .Open
    End With
     
    OpenDatabaseConnection = database
     
    Exit Function
Handler:
    Debug.Print "Database connection failed. Check your connection string."
End Function

Notez que le mot de passe de la base de données n'est inclus dans la chaîne de connexion de l'exemple ci-dessus que pour des raisons de clarté. Les meilleures pratiques exigent de ne pas stocker les mots de passe de base de données dans le code. Cela peut être accompli en prenant le mot de passe via l'entrée utilisateur ou en utilisant l'authentification Windows.

Récupérer des enregistrements avec une requête

Les requêtes peuvent être exécutées de deux manières, les deux Recordset objet ADO Recordset qui est une collection de lignes renvoyées. Notez que les deux exemples ci-dessous utilisent la fonction OpenDatabaseConnection partir de l'exemple Créer une connexion à une source de données pour des raisons de brièveté. Rappelez-vous que la syntaxe du SQL transmis à la source de données est spécifique au fournisseur.

La première méthode consiste à transmettre l'instruction SQL directement à l'objet Connection et constitue la méthode la plus simple pour exécuter des requêtes simples:

Public Sub DisplayDistinctItems()
    On Error GoTo Handler
    Dim database As ADODB.Connection
    Set database = OpenDatabaseConnection(SomeDSN)
    
    If Not database Is Nothing Then
        Dim records As ADODB.Recordset
        Set records = database.Execute("SELECT DISTINCT Item FROM Table")
        'Loop through the returned Recordset.
        Do While Not records.EOF      'EOF is false when there are more records.
            'Individual fields are indexed either by name or 0 based ordinal.
            'Note that this is using the default .Fields member of the Recordset.
            Debug.Print records("Item")
            'Move to the next record.
            records.MoveNext
        Loop
    End If
CleanExit:
    If Not records Is Nothing Then records.Close
    If Not database Is Nothing And database.State = adStateOpen Then
        database.Close
    End If
    Exit Sub
Handler:
    Debug.Print "Error " & Err.Number & ": " & Err.Description
    Resume CleanExit
End Sub

La deuxième méthode consiste à créer un objet de Command ADO pour la requête que vous souhaitez exécuter. Cela nécessite un peu plus de code, mais est nécessaire pour pouvoir utiliser des requêtes paramétrées:

Public Sub DisplayDistinctItems()
    On Error GoTo Handler
    Dim database As ADODB.Connection
    Set database = OpenDatabaseConnection(SomeDSN)
    
    If Not database Is Nothing Then
        Dim query As ADODB.Command
        Set query = New ADODB.Command
        'Build the command to pass to the data source.
        With query
            .ActiveConnection = database
            .CommandText = "SELECT DISTINCT Item FROM Table"
            .CommandType = adCmdText
        End With
        Dim records As ADODB.Recordset
        'Execute the command to retrieve the recordset.
        Set records = query.Execute()

        Do While Not records.EOF
            Debug.Print records("Item")
            records.MoveNext
        Loop
    End If
CleanExit:
    If Not records Is Nothing Then records.Close
    If Not database Is Nothing And database.State = adStateOpen Then
        database.Close
    End If
    Exit Sub
Handler:
    Debug.Print "Error " & Err.Number & ": " & Err.Description
    Resume CleanExit
End Sub

Notez que les commandes envoyées à la source de données sont vulnérables à l'injection SQL , intentionnelle ou non. En général, les requêtes ne doivent pas être créées en concaténant une entrée utilisateur quelconque. Au lieu de cela, ils doivent être paramétrés (voir Création de commandes paramétrées ).

Exécution de fonctions non scalaires

Les connexions ADO peuvent être utilisées pour effectuer à peu près toutes les fonctions de base de données prises en charge par le fournisseur via SQL. Dans ce cas, il n'est pas toujours nécessaire d'utiliser le jeu d' Recordset renvoyé par la fonction Execute , bien qu'il puisse être utile pour obtenir des affectations de clés après des instructions INSERT avec @@ Identity ou des commandes SQL similaires. Notez que l'exemple ci-dessous utilise la fonction OpenDatabaseConnection partir de l'exemple Créer une connexion à une source de données pour des raisons de brièveté.

Public Sub UpdateTheFoos()
    On Error GoTo Handler
    Dim database As ADODB.Connection
    Set database = OpenDatabaseConnection(SomeDSN)
    
    If Not database Is Nothing Then
        Dim update As ADODB.Command
        Set update = New ADODB.Command
        'Build the command to pass to the data source.
        With update
            .ActiveConnection = database
            .CommandText = "UPDATE Table SET Foo = 42 WHERE Bar IS NULL"
            .CommandType = adCmdText
            .Execute        'We don't need the return from the DB, so ignore it.
        End With
    End If
CleanExit:
    If Not database Is Nothing And database.State = adStateOpen Then
        database.Close
    End If
    Exit Sub
Handler:
    Debug.Print "Error " & Err.Number & ": " & Err.Description
    Resume CleanExit
End Sub

Notez que les commandes envoyées à la source de données sont vulnérables à l'injection SQL , intentionnelle ou non. En général, les instructions SQL ne doivent pas être créées en concaténant une entrée utilisateur quelconque. Au lieu de cela, ils doivent être paramétrés (voir Création de commandes paramétrées ).

Création de commandes paramétrées

Chaque fois que SQL exécuté via une connexion ADO doit contenir une entrée utilisateur, il est conseillé de le paramétrer afin de minimiser les risques d'injection SQL. Cette méthode est également plus lisible que les longues concaténations et facilite un code plus robuste et maintenable (en utilisant une fonction qui renvoie un tableau de Parameter ).

Dans la syntaxe ODBC standard, les paramètres sont donnés ? "espaces réservés" dans le texte de la requête, puis les paramètres sont ajoutés à la Command dans l'ordre dans lequel ils apparaissent dans la requête.

Notez que l'exemple ci-dessous utilise la fonction OpenDatabaseConnection partir de la création d'une connexion à une source de données pour plus de concision.

Public Sub UpdateTheFoos()
    On Error GoTo Handler
    Dim database As ADODB.Connection
    Set database = OpenDatabaseConnection(SomeDSN)
    
    If Not database Is Nothing Then
        Dim update As ADODB.Command
        Set update = New ADODB.Command
        'Build the command to pass to the data source.
        With update
            .ActiveConnection = database
            .CommandText = "UPDATE Table SET Foo = ? WHERE Bar = ?"
            .CommandType = adCmdText
            
            'Create the parameters.
            Dim fooValue As ADODB.Parameter
            Set fooValue = .CreateParameter("FooValue", adNumeric, adParamInput)
            fooValue.Value = 42
            
            Dim condition As ADODB.Parameter
            Set condition = .CreateParameter("Condition", adBSTR, adParamInput)
            condition.Value = "Bar"
            
            'Add the parameters to the Command
            .Parameters.Append fooValue
            .Parameters.Append condition
            .Execute
        End With
    End If
CleanExit:
    If Not database Is Nothing And database.State = adStateOpen Then
        database.Close
    End If
    Exit Sub
Handler:
    Debug.Print "Error " & Err.Number & ": " & Err.Description
    Resume CleanExit
End Sub

Remarque: L'exemple ci-dessus illustre une instruction UPDATE paramétrée, mais toute instruction SQL peut recevoir des paramètres.