VBA
Работа с ADO

замечания

Подключение к источнику данных

Первым шагом в доступе к источнику данных через ADO является создание объекта ADO Connection . Обычно это делается с использованием строки подключения для указания параметров источника данных, хотя также можно открыть соединение DSN, передав DSN, идентификатор пользователя и пароль в метод .Open .

Обратите внимание, что DSN не требуется для подключения к источнику данных через ADO - любой источник данных, у которого есть поставщик ODBC, может быть подключен к соответствующей строке соединения. Хотя конкретные строки подключения для разных поставщиков не входят в сферу применения этой темы, ConnectionStrings.com является отличной ссылкой для поиска соответствующей строки для вашего провайдера.

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

Обратите внимание, что пароль базы данных включен в строку соединения в приведенном выше примере только для ясности. Наилучшая практика будет диктовать не хранение паролей базы данных в коде. Это может быть выполнено путем ввода пароля через пользовательский ввод или с помощью проверки подлинности Windows.

Получение записей с запросом

Запросы могут выполняться двумя способами, оба из которых возвращают объект ADO Recordset который представляет собой набор возвращенных строк. Обратите внимание, что в обоих примерах ниже используется функция OpenDatabaseConnection из OpenDatabaseConnection « Создание соединения с источником данных» для краткости. Помните, что синтаксис SQL, переданный источнику данных, специфичен для провайдера.

Первый метод - передать инструкцию SQL непосредственно объекту Connection и является самым простым методом для выполнения простых запросов:

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

Второй способ - создать объект Command ADO для запроса, который вы хотите выполнить. Для этого требуется немного больше кода, но это необходимо для использования параметризованных запросов:

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

Обратите внимание, что команды, отправленные источнику данных, уязвимы для SQL-инъекций , преднамеренных или непреднамеренных. В общем случае запросы не должны создаваться путем объединения пользовательского ввода любого типа. Вместо этого они должны быть параметризованы (см. Создание параметризованных команд ).

Выполнение нескалярных функций

Соединения ADO могут использоваться для выполнения практически любой функции базы данных, которую поставщик поддерживает через SQL. В этом случае не всегда необходимо использовать Recordset возвращаемый функцией Execute , хотя он может быть полезен для получения назначений ключей после операторов INSERT с помощью @@ Identity или аналогичных SQL-команд. Обратите внимание, что приведенный ниже пример использует функцию OpenDatabaseConnection из OpenDatabaseConnection « Создание соединения с источником данных» для краткости.

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

Обратите внимание, что команды, отправленные источнику данных, уязвимы для SQL-инъекций , преднамеренных или непреднамеренных. В общем случае SQL-запросы не должны создаваться путем объединения пользовательских данных любого типа. Вместо этого они должны быть параметризованы (см. Создание параметризованных команд ).

Создание параметризованных команд

Каждый раз, когда SQL, выполняемый через соединение ADO, должен содержать пользовательский ввод, считается оптимальной для его параметризации, чтобы минимизировать вероятность внедрения SQL. Этот метод также более читабельен, чем длинные конкатенации, и обеспечивает более надежный и поддерживаемый код (т. Е. С помощью функции, возвращающей массив Parameter ).

В стандартном синтаксисе ODBC задаются параметры ? «заполнители» в тексте запроса, а затем параметры добавляются к Command в том же порядке, что и в запросе.

Обратите внимание, что в приведенном ниже примере для краткости используется функция OpenDatabaseConnection из OpenDatabaseConnection « Создание соединения с источником данных» .

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

Примечание. В приведенном выше примере демонстрируется параметризованный оператор UPDATE, но любому оператору SQL могут быть заданы параметры.