VBA
Praca z ADO

Uwagi

Nawiązywanie połączenia ze źródłem danych

Pierwszym krokiem w dostępie do źródła danych za pośrednictwem ADO jest utworzenie obiektu Connection ADO. Zazwyczaj odbywa się to za pomocą ciągu połączenia w celu określenia parametrów źródła danych, chociaż możliwe jest również otwarcie połączenia DSN poprzez przekazanie DSN, ID użytkownika i hasła do metody .Open .

Należy pamiętać, że DSN nie jest wymagane do połączenia ze źródłem danych za pośrednictwem ADO - do dowolnego źródła danych, które ma dostawcę ODBC, można podłączyć się za pomocą odpowiedniego ciągu połączenia. Podczas gdy określone parametry połączenia dla różnych dostawców są poza zakresem tego tematu, ConnectionStrings.com stanowi doskonałą pomoc w znalezieniu odpowiedniego ciągu dla twojego dostawcy.

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

Zauważ, że hasło bazy danych jest zawarte w ciągu połączenia w powyższym przykładzie tylko dla zachowania przejrzystości. Najlepsze praktyki nakazują, aby nie przechowywać haseł bazy danych w kodzie. Można to osiągnąć, biorąc hasło za pomocą danych wprowadzonych przez użytkownika lub za pomocą uwierzytelnienia systemu Windows.

Pobieranie rekordów za pomocą zapytania

Zapytania można wykonywać na dwa sposoby, z których oba zwracają obiekt ADO Recordset który jest zbiorem zwracanych wierszy. Zauważ, że oba poniższe przykłady używają funkcji OpenDatabaseConnection z przykładu Nawiązywanie połączenia ze źródłem danych w celu skrócenia. Pamiętaj, że składnia kodu SQL przekazywanego do źródła danych zależy od dostawcy.

Pierwsza metoda polega na przekazaniu instrukcji SQL bezpośrednio do obiektu Connection i jest najłatwiejszą metodą wykonywania prostych zapytań:

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

Druga metoda polega na utworzeniu obiektu Command ADO dla zapytania, które chcesz wykonać. Wymaga to trochę więcej kodu, ale jest konieczne, aby użyć sparametryzowanych zapytań:

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

Należy pamiętać, że polecenia wysyłane do źródła danych są podatne na wstrzykiwanie SQL , celowe lub niezamierzone. Zasadniczo zapytania nie powinny być tworzone przez łączenie danych wejściowych użytkownika. Zamiast tego należy je sparametryzować (patrz Tworzenie poleceń sparametryzowanych ).

Wykonywanie funkcji innych niż skalarne

Połączenia ADO mogą służyć do wykonywania praktycznie dowolnej funkcji bazy danych obsługiwanej przez dostawcę za pośrednictwem SQL. W takim przypadku nie zawsze konieczne jest użycie Recordset zwróconego przez funkcję Execute , chociaż może być ono przydatne do uzyskiwania przypisań klawiszy po instrukcjach INSERT za pomocą @@ Identity lub podobnych poleceń SQL. Zwróć uwagę, że w poniższym przykładzie użyto funkcji OpenDatabaseConnection z przykładu Nawiązywanie połączenia ze źródłem danych w celu skrócenia.

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

Należy pamiętać, że polecenia wysyłane do źródła danych są podatne na wstrzykiwanie SQL , celowe lub niezamierzone. Zasadniczo nie należy tworzyć instrukcji SQL poprzez łączenie danych wejściowych użytkownika. Zamiast tego należy je sparametryzować (patrz Tworzenie poleceń sparametryzowanych ).

Tworzenie sparametryzowanych poleceń

Za każdym razem, gdy SQL wykonywany przez połączenie ADO musi zawierać dane wejściowe użytkownika, za najlepszą metodę uważa się jego parametryzację w celu zminimalizowania szansy na wstrzyknięcie SQL. Ta metoda jest również bardziej czytelna niż długie konkatenacje i umożliwia bardziej niezawodny i łatwiejszy do utrzymania kod (tj. Poprzez użycie funkcji, która zwraca tablicę Parameter ).

W standardowej składni ODBC podano parametry ? „symbole zastępcze” w tekście zapytania, a następnie parametry są dodawane do Command w tej samej kolejności, w jakiej występują w zapytaniu.

Zwróć uwagę, że w poniższym przykładzie użyto funkcji OpenDatabaseConnection z nawiązywania połączenia ze źródłem danych dla zwięzłości.

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

Uwaga: Powyższy przykład pokazuje sparametryzowaną instrukcję UPDATE, ale dowolnej instrukcji SQL można nadać parametry.