VBA 코딩 패턴: ADO DB Connection String 생성

이번 글은 엑셀 VBA에서 ADO DB Connection String 생성 과 편집에 대한 기능 구현을 살펴본다.

---

1. ADO 개념과 필요성

1.1. ADO(ActiveX Data Objects) 개념

ADO는 다양한 데이터 저장소(data store)에 접근하여 데이터를 C.R.U.D(Create, Read, Update, Delete)할 수 있는 library이다.

Microsoft docs 사이트에서는 ADO를 다음과 같이 설명하고 있다.

ActiveX Data Objects (ADO) is a high-level, easy-to-use interface to OLE DB. OLE DB is a low-level, high-performance interface to a variety of data stores. Both ADO and OLE DB can work with relational (tabular) and nonrelational (hierarchical or stream) data.

ADO provides a layer of abstraction between your client or middle-tier application and the low-level OLE DB interfaces. ADO uses a small set of Automation objects to provide a simple and efficient interface to OLE DB. This interface makes ADO a good choice for developers in higher level languages, such as Visual Basic and VBScript, who want to access data without having to learn the intricacies of COM and OLE DB.

출처: https://docs.microsoft.com/en-us/sql/ado/guide/ado-introduction?view=sql-server-ver15 

1.2. ADO(Active Data Objects) 필요성

이전 글 엑셀 VBA 강좌(1): 엑셀 VBA 개요_1.3. 엑셀 VBA로 무엇을 할 수 있나? 에서 소개한 내용중 Database와 관련된 내용이 있었다.

이 중에서 Database와 관련하여 데이터를 조회하거나 저장, 변경, 삭제할 때 ADO가 필요하다. 엑셀 VBA에서 사용할 수 있는 Database 관련 library는 여러 가지(ADO, OLE DB, ODBC 등)가 있으나 ADO가 가장 범용적이고 사용하기에 편리하다.

2. ADO DB Connection String 생성

2.1. 참조 추가

이 코드는 2개의 library를 참조 추가해야 한다.

▼ Microsoft ActiveX Data Objects 2.8 Library 참조 추가

“Microsoft ActiveX Data Objects 6.1 Library”가 더 최신 버전이기는 하나, 별 차이점을 알 수 없어서 보통 2.8을 사용한다.

▼ Microsoft OLE DB Service Component 1.0 Type Library 참조 추가

Connection String을 생성하거나 편집할 수 있게 해 주는 MSDASC.DataLinks 클래스를 사용하기 위하여 참조 추가한다.

2.2. ADO DB Connection String 생성 기능 구현

이전 글 엑셀 VBA 강좌(10): 엑셀 VBA로 개발하여 사용 중인 도구_5.7. Excel Data Downloader에 다음과 같이 Connection String을 생성하는 버튼이 있었다.

위 화면에서 “Connection String” 버튼의 클릭 이벤트 처리 코드는 다음과 같다.

Private Sub btnBuildConnectionString_Click()
On Error GoTo 0

    Dim cn As ADODB.Connection, MSDASCObj As MSDASC.DataLinks, oCurrentRange As Range
    Dim eOrgXlEnableCancelKey As XlEnableCancelKey

    Set oCurrentRange = ActiveCell

    Set MSDASCObj = New MSDASC.DataLinks

    eOrgXlEnableCancelKey = Application.EnableCancelKey
    Application.EnableCancelKey = xlDisabled
    Set cn = New ADODB.Connection
    cn.ConnectionString = oCurrentRange
    If MSDASCObj.PromptEdit(cn) = True Then
        oCurrentRange = cn.ConnectionString
    End If

    Set cn = Nothing
    Set MSDASCObj = Nothing

    Application.EnableCancelKey = eOrgXlEnableCancelKey
End Sub

이 코드는 MSDASC.DataLinks, ADODB.Connection 클래스를 이용하여 Connection String(연결 문자열)을 새로 만들거나 편집하는 기능을 구현한 코드이다.

• 7행: 시트에서 현재 위치 cell(ActiveCell)을 oCurrentRange에 할당한다. C16 cell부터 아래쪽으로 여러 개의 Connection String을 입력할 수 있어서 먼저 현재 위치를 선택한 후 기능을 실행한다.
• 13~14행: ADODB.Connection 개체(cn)를 생성하고 그 개체의 ConnectionString 속성을 oCurrentRange 값으로 할당한다.
• 15행: MSDASC.DataLinks 개체의 PromptEdit 함수에 ADODB.Connection 개체(cn)를 parameter로 전달하여 실행한다. 이 함수를 실행하면 다음과 같이 “데이터 연결 속성” 창이 보인다. (공급자 목록은 설치되어 있는 목록이 보이므로 사용자 환경마다 서로 다를 수 있다. 아래 “연결” 탭은 “Microsoft OLE DB Provider for ODBC Drivers”를 선택했을 때 보이는 화면이다.)

참고로, 공급자를 “Microsoft OLE DB Provider for Oracle”을 선택했을 때는 “연결” 탭이 다음과 같이 바뀐다.

• 16행: PromptEdit 함수 반환 값이 True인 경우 즉, “데이터 연결 속성” 창에서 “확인” 버튼을 클릭하여 ConnectionString(연결문자열)을 생성/변경 완료한 경우 그 값을 oCurrentRange에 기록한다.

이 방법을 적용하면 다음과 같은 장점이 있다.

• 오류 가능성 감소
  • DB ConnectionString을 직접 타이핑해서 만드는 경우 오타가 있거나, 설치되어 있지 않은 공급자를 지정하여 오류가 발생할 수 있다.
  • 이 방법을 적용하면 DB ConnectionString을 오류없이 만들 수 있다.
• 재사용성 향상
  • 소스코드상에 DB ConnectionString을 문자열로 관리하는 경우(하드 코딩된 경우)는 다른 환경에서 해당 기능을 실행하려고 할 때 매번 소스코드를 변경해 줘야 한다. 이러면 어떤 소스코드를 어떻게 편집해야 할지 알고 있는 사용자만 재사용할 수 있다.
  • DB ConnectionString을 소스코드가 아닌 UI(엑셀 시트 등)에 보이게 두고 편집 가능하게 기능을 제공하면 누구나 재사용할 수 있어 재사용성이 향상된다.
• 편의성 향상(ODBC DSN 생성 필요 없음)
  • ODBC를 사용하는 경우는 미리 설정된 접속 정보와 그 명칭(DSN, Data Source Name)이 필요하다.
  • 이 방법을 적용하면 ODBC DSN을 생성할 필요가 없어 간편하다.

단점은 ConnectionString을 만들 때 비밀번호를 포함했다면 UI에 그 비밀번호가 노출될 수 있다는 점이다. 비밀번호를 공개하면 안 되는 경우 주의할 필요가 있다.