Connection Object

Connection Object

데이터 원본에 대해 열려 있는 연결을 나타냅니다.

 

Excel VBA에서 ADO를 사용하기 위해서는 ADO Library를 참조해야 합니다.

 

연결하기

다음 코드는 암시적으로 연결하는 방법입니다.

Sub GetDataFromAccessDatabase()
' 레코드셋 선언 및 인스턴스화
    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset

    ' 암시적 연결 생성 및 레코드셋 열기
    rst.Open "Titles", _
             "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\download\Biblio.mdb;", _
             adOpenForwardOnly, _
             adLockReadOnly, _
             adCmdTable

    ' 레코드셋 닫기 및 정리
    rst.Close
    Set rst = Nothing
End Sub

다음 코드는 명시적으로 연결합니다.

Sub GetDataFromAccessDatabase()
    ' Connection 및 Recordset 선언
    Dim conn As ADODB.Connection
    Dim rst As ADODB.Recordset

    ' 인스턴스화
    Set conn = New ADODB.Connection
    Set rst = New ADODB.Recordset
    
    ' Connection 열기
    conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\download\Biblio.mdb;"
    
    ' Recordset 열기 (명시적인 Connection 사용)
    rst.Open "Titles", _
        conn, _
        adOpenForwardOnly, _
        adLockReadOnly, _
        adCmdTable
      
    ' Recordset 및 Connection 닫기
    rst.Close
    conn.Close

   ' 정리
   Set rst = Nothing
   Set conn = Nothing

End Sub

 

DSN을 사용하여 데이터 원본에 연결하기

Sub ConnectToDatabase()
    Dim con As Object
    Set con = CreateObject("ADODB.Connection")
    
    ' DSN 이름 및 로그인 정보 설정
    Dim dsnName As String
    
    
    dsnName = "BiblioDSN"
   
    
    ' 데이터베이스 연결
    con.Open dsnName
    
    ' ConnectionString 출력
    Debug.Print "ConnectionString: " & con.ConnectionString
    
    ' 연결 닫기 및 정리
    con.Close
    Set con = Nothing
End Sub

' Output
' ConnectionString: Provider=MSDASQL.1;Data Source=BiblioDSN;

 

Connection 개체를 여는 두 가지 방법

1. ConnectionString을 사용하는 방법입니다.

Sub ConnectToAccessDatabase()
' Connection 선언
    Dim conn As ADODB.Connection

    ' 인스턴스화
    Set conn = New ADODB.Connection

    ' ConnectionString 설정 및 Connection 열기
    conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\download\Biblio.mdb;"
    conn.Open

    Debug.Print "Connection opened with ADO Version " & conn.Version

    conn.Close

    ' 정리
    Set conn = Nothing

End Sub

' Output
' Connection opened with ADO Version 10.0

 

2. Open 메서드를 호출할 때 직접 연결 문자열을 전달합니다.

Sub ConnectToAccessDatabase()
' Connection 선언
    Dim conn As ADODB.Connection

    ' 인스턴스화
    Set conn = New ADODB.Connection

    'Connection 열기
    conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\download\Biblio.mdb;"

    Debug.Print "Connection opened with ADO Version " & conn.Version

    conn.Close

    ' 정리
    Set conn = Nothing

End Sub

' Output
' Connection opened with ADO Version 10.0

ConnectionString 속성 읽기

Sub ConnectToDatabase()

    Dim con As ADODB.Connection
    Dim rst As ADODB.Recordset

    Set con = New ADODB.Connection
    Set rst = New ADODB.Recordset

    rst.Open "Titles", _
             "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\download\Biblio.mdb;", _
             adOpenForwardOnly, , _
             adCmdTable

    Set con = rst.ActiveConnection

    Debug.Print con.ConnectionString

    rst.Close
    con.Close

    Set rst = Nothing
    Set con = Nothing

End Sub

' Output
' Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;
' Data Source=D:\download\Biblio.mdb;
' Mode=Share Deny None;Jet OLEDB:System database="";
' Jet OLEDB:Registry Path="";
' Jet OLEDB:Database Password="";
' Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locking Mode=1;
' Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;
' Jet OLEDB:New Database Password="";
' Jet OLEDB:Create System Database=False;
' Jet OLEDB:Encrypt Database=False;
' Jet OLEDB:Don't Copy Locale on Compact=False;
' Jet OLEDB:Compact Without Replica Repair=False;
' Jet OLEDB:SFP=False;

ADO 버전 번호 및 연결 상태 확인

Sub CheckADOVersion()
    Dim con As ADODB.Connection
    Set con = New ADODB.Connection

    Debug.Print con.Version

    Set con = Nothing
End Sub

' Output
' 10.0

 

기본 데이터베이스 변경하고 메시지 출력하기

Sub ConnectionExample()
    Dim con As Object
    Set con = CreateObject("ADODB.Connection")

    ' DSN을 사용하여 연결
    con.Open "SQLNorthwindDSN"

    ' default database 변경
    con.DefaultDatabase = "Master"

    ' 변경된 default database 이름 표시
    Debug.Print "The default database is now set to: " & con.DefaultDatabase

    ' 연결 닫기 및 정리
    con.Close
    Set con = Nothing
End Sub

' Output
' The default database is now set to: master

 

데이터 액세스 권한 설정

' Connection 생성 함수
Function CreateConnection() As ADODB.Connection
    Dim con As ADODB.Connection
    Set con = New ADODB.Connection
    Set CreateConnection = con
End Function

' Connection 열기 함수
Sub OpenConnection(con As ADODB.Connection, DSNName As String)
    con.Mode = adModeShareDenyWrite ' 다른 사용자에게 쓰기 접근 권한을 제거하는 설정
    con.Open "DSN=" & DSNName       ' Connection 객체를 connection string으로 열기
End Sub

' 현재 권한 확인 함수
Function CheckPermissions(con As ADODB.Connection) As String
    Dim sPermissions As String
    
    Select Case (con.Mode)
        Case adModeUnknown:
            sPermissions = "Unknown or unset permissions."
        Case adModeRead:
            sPermissions = "User cannot read data."
        Case adModeWrite:
            sPermissions = "User cannot write data."
        Case adModeReadWrite:
            sPermissions = "User cannot read nor write data."
        Case adModeShareDenyRead:
            sPermissions = "Other users cannot read data."
        Case adModeShareDenyWrite:
            sPermissions = "Other users cannot write data."
        Case adModeShareExclusive:
            sPermissions = "Other users cannot read or write data."
        Case adModeShareDenyNone:
            sPermissions = "Other users cannot do anything with data."
    End Select
    
    CheckPermissions = sPermissions
End Function

' 메인 실행 서브루틴
Sub Main()
    
   ' 새로운 Connection 객체 생성
   Dim conn As ADODB.Connection
   Set conn = CreateConnection()

   ' 데이터 소스 연결 열기
   OpenConnection conn, "SQLNorthwindDSN"

   ' 권한 확인 및 출력
   Debug.Print CheckPermissions(conn)

   ' 연결 닫기
   conn.Close

  ' 메모리 정리
  Set conn = Nothing

End Sub

' Output
' Other users cannot write data.

 

연결 시간 초과 처리하기

' 데이터베이스 연결을 생성합니다.
Public Function CreateConnection() As ADODB.Connection
    Dim con As New ADODB.Connection
    con.ConnectionTimeout = 2
    Set CreateConnection = con
End Function

' 데이터베이스에 연결합니다.
Public Sub OpenConnection(con As ADODB.Connection)
    On Error GoTo ERR_OpenConnection:
    
    con.Open "DSN=SQLNorthwindDSN"
    
    Exit Sub
    
ERR_OpenConnection:
    HandleErrors con

End Sub

' 데이터베이스 연결을 닫습니다.
Public Sub CloseConnection(con As ADODB.Connection)
   If Not (con Is Nothing) Then
       If (con.State = adStateOpen) Then
           con.Close
       End If
   End If
End Sub

' 에러를 처리합니다.
Public Sub HandleErrors(con As ADODB.Connection)
    Dim oErr As ADODB.Error
    
    For Each oErr In con.Errors
        Select Case (Err.Number)
        Case adErrStillConnecting:
            MsgBox "The connection timed out on attempting to open."
        Case Else:
            MsgBox "Other Error: " & oErr.Description
        End Select
    Next oErr

End Sub

' 메인 함수입니다.
Public Sub OpenDataSource()
   Dim conn As ADODB.Connection
   
   Set conn = CreateConnection()
   
   OpenConnection conn
   
   CloseConnection conn
   
   Set conn = Nothing
End Sub

 

Execute 메서드로 SQL 문을 실행하기

Sub TestExecuteSelect()
    Dim con As ADODB.Connection
    Dim rst As ADODB.Recordset

    Set con = New ADODB.Connection

    ' 데이터베이스에 연결합니다.
    con.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\download\Northwind.accdb;"

    ' SELECT 쿼리를 실행하고, 결과를 Recordset 개체에 저장합니다.
    Set rst = con.Execute("SELECT * FROM Customers", , adCmdText)

    ' Recordset의 모든 레코드를 순회합니다.
    Do Until rst.EOF
        Debug.Print "Customer ID: " & rst.Fields("CustomerID").Value & ", Address: " & rst.Fields("Address").Value
        rst.MoveNext    ' 다음 레코드로 이동합니다.
    Loop

    ' Recordset 개체가 열려있다면 닫고, 메모리에서 해제합니다.
    If Not (rst Is Nothing) Then
        If (rst.State And adStateOpen) = adStateOpen Then
            rst.Close
        End If
        Set rst = Nothing
    End If
    ' Connection 개체가 열려있다면 닫고, 메모리에서 해제합니다.
    If Not (con Is Nothing) Then
        If (con.State And adStateOpen) = adStateOpen Then
            con.Close
        End If
        Set con = Nothing
    End If

End Sub

' 출력 결과
' Customer ID: 1, Address: 123 Oak Street
' Customer ID: 2, Address: 234 Elm Street
' Customer ID: 3, Address: 123 Elm Street
' Customer ID: 4, Address: 444 Birch Avenue
' Customer ID: 5, Address: 567 Green Avenue
' Customer ID: 6, Address: 6001 Purple Street
' Customer ID: 7, Address: 70 N Blue Water Lane
' Customer ID: 8, Address: 888 Mesquite Street

 

OpenSchema 메서드로 스키마 정보 검색하기

다음 코드는 데이터베이스 내의 모든 테이블 및 뷰 목록을 가져옵니다.

Sub GetTableNames()
    Dim con As ADODB.Connection  ' Connection 개체를 선언합니다.
    Dim rst As ADODB.Recordset   ' Recordset 개체를 선언합니다.

    Set con = New ADODB.Connection  ' Connection 개체를 생성합니다.

' 데이터베이스에 연결합니다. 이 예제에서는 Microsoft Access 데이터베이스 파일에 연결하였습니다.
    con.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\download\Northwind.accdb;"

    ' OpenSchema 메서드를 사용하여 테이블 정보를 가져옵니다.
    Set rst = con.OpenSchema(adSchemaTables)

    ' Recordset의 모든 레코드(여기서는 테이블 이름)를 순회합니다.
    Do Until rst.EOF
        Debug.Print "Table Name: " & rst.Fields("TABLE_NAME").Value
        rst.MoveNext  ' 다음 레코드로 이동합니다.
    Loop

    ' Recordset 개체가 열려있다면 닫고, 메모리에서 해제합니다.
    If Not (rst Is Nothing) Then
        If (rst.State And adStateOpen) = adStateOpen Then
            rst.Close
        End If
        Set rst = Nothing
    End If

    ' Connection 개체가 열려있다면 닫고, 메모리에서 해제합니다.
    If Not (con Is Nothing) Then
        If (con.State And adStateOpen) = adStateOpen Then
            con.Close
        End If
        Set con = Nothing
    End If

End Sub