VBA
Werken met ADO

Opmerkingen

Verbinding maken met een gegevensbron

De eerste stap bij het openen van een gegevensbron via ADO is het maken van een ADO- Connection . Dit wordt meestal gedaan met behulp van een verbindingsreeks om de gegevensbronparameters op te geven, hoewel het ook mogelijk is om een DSN-verbinding te openen door de DSN, het gebruikers-ID en het wachtwoord door te geven aan de .Open methode.

Merk op dat een DSN niet vereist is om verbinding te maken met een gegevensbron via ADO - elke gegevensbron met een ODBC-provider kan worden verbonden met de juiste verbindingsreeks. Hoewel specifieke verbindingsreeksen voor verschillende providers buiten het bestek van dit onderwerp vallen, is ConnectionStrings.com een uitstekende referentie voor het vinden van de juiste string voor uw provider.

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

Merk op dat het databasewachtwoord in de bovenstaande voorbeeldreeks alleen voor de duidelijkheid is opgenomen in de verbindingsreeks. Best practices zouden voorschrijven dat databasewachtwoorden niet in code worden opgeslagen. Dit kan worden bereikt door het wachtwoord via gebruikersinvoer te nemen of Windows-verificatie te gebruiken.

Records ophalen met een zoekopdracht

Query's kunnen op twee manieren worden uitgevoerd, die beide een ADO Recordset object retourneren dat een verzameling geretourneerde rijen is. Merk op dat in beide onderstaande voorbeelden de OpenDatabaseConnection functie OpenDatabaseConnection van het voorbeeld Verbinding maken met een gegevensbron wordt gebruikt om het kort te houden. Vergeet niet dat de syntaxis van de SQL die aan de gegevensbron wordt doorgegeven, providerspecifiek is.

De eerste methode is om de SQL-instructie rechtstreeks aan het Connection-object door te geven en is de eenvoudigste methode voor het uitvoeren van eenvoudige query's:

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

De tweede methode is om een ADO- Command maken voor de query die u wilt uitvoeren. Dit vereist iets meer code, maar is nodig om geparametriseerde zoekopdrachten te gebruiken:

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

Houd er rekening mee dat opdrachten die naar de gegevensbron worden verzonden, gevoelig zijn voor SQL-injectie , opzettelijk of onbedoeld. Over het algemeen mogen query's niet worden gemaakt door gebruikersinvoer van welke aard dan ook samen te voegen. In plaats daarvan moeten ze worden geparametriseerd (zie Parameteropdrachten maken ).

Niet-scalaire functies uitvoeren

ADO-verbindingen kunnen worden gebruikt om vrijwel elke databasefunctie uit te voeren die de provider via SQL ondersteunt. In dit geval is het niet altijd nodig om de Recordset te gebruiken die wordt geretourneerd door de functie Execute , hoewel het handig kan zijn om sleuteltoewijzingen te verkrijgen na INSERT-instructies met @@ Identity of vergelijkbare SQL-opdrachten. Merk op dat in het onderstaande voorbeeld de OpenDatabaseConnection functie OpenDatabaseConnection uit het voorbeeld Verbinding maken met een gegevensbron wordt gebruikt met het oog op beknoptheid.

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

Houd er rekening mee dat opdrachten die naar de gegevensbron worden verzonden, gevoelig zijn voor SQL-injectie , opzettelijk of onbedoeld. Over het algemeen mogen SQL-instructies niet worden gemaakt door gebruikersinvoer van welke aard dan ook samen te voegen. In plaats daarvan moeten ze worden geparametriseerd (zie Parameteropdrachten maken ).

Geparametriseerde opdrachten maken

Telkens wanneer SQL die via een ADO-verbinding wordt uitgevoerd, gebruikersinvoer moet bevatten, wordt het als beste praktijk beschouwd om dit te parametreren om de kans op SQL-injectie te minimaliseren. Deze methode is ook leesbaarder dan lange aaneenschakelingen en vergemakkelijkt robuustere en onderhoudbare code (dat wil zeggen door een functie te gebruiken die een reeks Parameter retourneert).

In standaard ODBC-syntaxis worden parameters gegeven ? "tijdelijke aanduidingen" in de querytekst en vervolgens worden parameters aan de Command in dezelfde volgorde als in de query.

Merk op dat het onderstaande voorbeeld de OpenDatabaseConnection functie van de Verbinding maken met een gegevensbron voor beknoptheid gebruikt.

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

Opmerking: In het bovenstaande voorbeeld wordt een geparametriseerde instructie UPDATE getoond, maar aan elke SQL-instructie kunnen parameters worden gegeven.