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 인증을 사용하여 수행 할 수 있습니다.

쿼리로 레코드 가져 오기

쿼리는 두 가지 방법으로 수행 할 수 있으며 둘 다 반환 된 행 컬렉션 인 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

두 번째 방법은 실행할 쿼리에 대한 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 문에 매개 변수를 제공 할 수 있습니다.