VBA
Mit ADO arbeiten

Bemerkungen

Herstellen einer Verbindung zu einer Datenquelle

Der erste Schritt beim Zugriff auf eine Datenquelle über ADO ist das Erstellen eines ADO- Connection . In der Regel wird dazu eine Verbindungszeichenfolge verwendet, um die Datenquellenparameter anzugeben. Es ist jedoch auch möglich, eine DSN-Verbindung zu öffnen, indem DSN, Benutzer-ID und Kennwort an die .Open Methode übergeben werden.

Beachten Sie, dass ein DSN nicht erforderlich ist, um eine Verbindung zu einer Datenquelle über ADO herzustellen. Jede Datenquelle, die über einen ODBC-Provider verfügt, kann mit der entsprechenden Verbindungszeichenfolge verbunden werden. Während bestimmte Verbindungszeichenfolgen für verschiedene Anbieter außerhalb des Bereichs dieses Themas liegen, ist ConnectionStrings.com eine hervorragende Referenz, um die geeignete Zeichenfolge für Ihren Anbieter zu finden.

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

Beachten Sie, dass das Datenbankkennwort im obigen Beispiel nur aus Gründen der Übersichtlichkeit in der Verbindungszeichenfolge enthalten ist. Best Practices setzen voraus , dass Datenbankkennwörter nicht im Code gespeichert werden. Dies kann erreicht werden, indem das Kennwort über die Benutzereingabe oder die Windows-Authentifizierung verwendet wird.

Datensätze mit einer Abfrage abrufen

Abfragen können auf zwei Arten ausgeführt werden. Beide geben ein ADO- Recordset Objekt zurück, bei dem es sich um eine Sammlung von zurückgegebenen Zeilen handelt. Beachten Sie, dass die beiden folgenden Beispiele aus Gründen der Kürze die OpenDatabaseConnection Funktion aus dem Beispiel Herstellen einer Verbindung mit einer Datenquelle verwenden . Denken Sie daran, dass die an die Datenquelle übergebene Syntax des SQL anbieterspezifisch ist.

Die erste Methode besteht darin, die SQL-Anweisung direkt an das Connection-Objekt zu übergeben. Dies ist die einfachste Methode zum Ausführen einfacher Abfragen:

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

Die zweite Methode ist das Erstellen eines ADO- Command für die Abfrage, die Sie ausführen möchten. Dies erfordert etwas mehr Code, ist aber erforderlich, um parametrisierte Abfragen verwenden zu können:

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

Beachten Sie, dass an die Datenquelle gesendete Befehle anfällig für die SQL-Injektion sind , entweder absichtlich oder unbeabsichtigt. Im Allgemeinen sollten Abfragen nicht durch Verketten von Benutzereingaben jeglicher Art erstellt werden. Sie sollten stattdessen parametrisiert werden (siehe Erstellen parametrisierter Befehle ).

Nicht-Skalar-Funktionen ausführen

Mit ADO-Verbindungen können nahezu alle Datenbankfunktionen ausgeführt werden, die der Provider über SQL unterstützt. In diesem Fall ist es nicht immer erforderlich, das von der Execute Funktion zurückgegebene Recordset zu verwenden, obwohl es nützlich sein kann, Schlüsselzuweisungen nach INSERT-Anweisungen mit @@ Identity- oder ähnlichen SQL-Befehlen abzurufen. Beachten Sie, dass im folgenden Beispiel die OpenDatabaseConnection Funktion aus dem Beispiel Herstellen einer Verbindung zu einer Datenquelle verwendet wird .

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

Beachten Sie, dass an die Datenquelle gesendete Befehle anfällig für die SQL-Injektion sind , entweder absichtlich oder unbeabsichtigt. Im Allgemeinen sollten SQL-Anweisungen nicht durch Verketten von Benutzereingaben jeglicher Art erstellt werden. Sie sollten stattdessen parametrisiert werden (siehe Erstellen parametrisierter Befehle ).

Parametrisierte Befehle erstellen

Immer wenn SQL, die über eine ADO-Verbindung ausgeführt werden, Benutzereingaben enthalten muss, gilt es als bewährte Methode, diese zu parametrisieren, um die Wahrscheinlichkeit einer SQL-Injection zu minimieren. Diese Methode ist auch lesbarer als lange Verkettungen und ermöglicht robusteren und wartbaren Code (z. B. durch Verwendung einer Funktion, die ein Array von Parameter zurückgibt).

In der Standard-ODBC-Syntax werden Parameter angegeben ? "Platzhalter" im Abfragetext und die Parameter werden dann in derselben Reihenfolge an den Command angehängt, in der sie in der Abfrage angezeigt werden.

Beachten Sie, dass das folgende Beispiel die OpenDatabaseConnection Funktion aus Herstellen einer Verbindung zu einer Datenquelle verwendet .

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

Hinweis: Das obige Beispiel veranschaulicht eine parametrisierte UPDATE-Anweisung, es können jedoch beliebige SQL-Anweisungen angegeben werden.