VBA
ADOの使用

備考

データソースへの接続の作成

ADOを介してデータソースにアクセスするための最初の手順は、ADO Connectionオブジェクトを作成することです。 DSN、ユーザーID、およびパスワードを.Openメソッドに渡してDSN接続を開くことも可能ですが、これは通常、接続文字列を使用してデータソースパラメータを指定して行います。

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認証を使用してパスワードを取得することで実現できます。

クエリを使用したレコードの取得

クエリは2つの方法で実行でき、どちらも返された行のコレクションであるADO Recordsetオブジェクトを返します。以下の両方の例では、簡潔にするために、データソースへの接続の例を作成する 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

2番目の方法は、実行するクエリのADO Commandオブジェクトを作成することです。これにはもう少しコードが必要ですが、パラメータ化されたクエリを使用するためには必要です。

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を介してサポートするほとんどすべてのデータベース機能を実行できます。この場合、 Execute関数によって返されたRecordsetは必ずしも使用する必要はありませんが、@@ Identityまたは同様のSQLコマンドを使用してINSERT文を実行した後にキー割り当てを取得すると便利です。以下の例では、簡潔にするために、データソースへの接続の例を作成する 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文は任意の種類のユーザ入力を連結して作成するべきではありません。代わりに、それらをパラメータ化する必要があります（「 パラメータ化されたコマンドの作成」を参照）。

パラメータ化されたコマンドの作成

ADO接続を介して実行されるSQLにユーザー入力が含まれる必要がある場合は、SQLインジェクションの可能性を最小限に抑えるために、パラメーターをパラメーター化することをお勧めします。このメソッドは、長い連結よりも読みやすく、より堅牢で保守可能なコードを容易にします（つまり、 Parameter配列を返す関数を使用することによって）。

標準ODBC構文では、パラメータは指定されてい?クエリテキストに「プレースホルダ」を追加し、クエリに表示される順序と同じ順序でパラメータにCommandを追加します。

以下の例では、簡潔にするためにデータソースへの接続を作成する 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文にパラメータを指定できます。