7강 – VBA로 엑셀에서 Database 연결, Data처리 기본개념

이번 강좌에서는 엑셀에서 VBA로 DB연결 및 Data처리를 위해 이해해야 할 기본개념과 연결방법을 알아보고자 한다.

DB연결 및 Data처리를 위한 기본개념

Excel VBA프로그램에서 Database에 접속해서 Data처리 요청을 하고 결과를 받아오는 모습은 아래의 그림과 같이 간단히 표현할 수 있다. ADO를 통해 Data처리요청을 하고, OLEDB 또는 ODBC는 Database연결을 담당한다.

ADO(ActiveX Data Object) : ODBC나 OLEDB는 DB연결을 담당하는 기능이고 ADO는 DB에 접속해서 Data를 처리하는 역할을 담당한다.
ODBC(Open Database Connectivity) : 다양한 관계형Database(RDBMS)에 접근할 수 있도록 마이크로소프트에서 개발한 표준 규격을 말하며 각 DBMS제조사별로 만들어진 ODBC Driver를 client에 설치해야 한다.
OLEDB(Object Linking Embedding) : ODBC가 관계형Database에 접근할 수 있도록 만들어진 규격인데 비해, OLEDB는 Database뿐만아니라 다양한 종류의 Data(파일, 이메일, 엑셀파일 등…)에 접근할 수 있도록 만들어 진 ODBC이후에 나온 발전된 규격이다.

* 실제 Microsoft의 자료를 보면 그림은 아래 그림과 같이 복잡하다.더 자세한 내용을 원하면 다음 사이트를 방문해 보자.

https://msdn.microsoft.com/en-us/library/ms810810.aspx

Current MDAC/WDAC architecture

Database에 접속해서 Data처리 요청을 하고 결과를 받아오는 기본개념을 이해했으니 이제 실제로 Database에 연결하는 방법은 알아보자. 연결하는 방법은 3가지로 정리할 수 있다. 예시는 Microsoft SQL Server를 이용한다. Oracle, MySQL, Maria DB등 다른 DB 연결방법도 유사하며 이 후 강좌에서 설명하기로 한다.

Excel에서 Database연결방법

※ Microsoft SQL Server를 기준으로 설명한다.

1) ODBC - DSN이용

DSN(Data Source Name)은 연결에 필요한 정보를 담고 있는 것이라고 생각하면 된다. 다음의 순서대로 DSN을 설정하고 DSN을 이용하여 VBA에서 DB를 연결한다.(Windows10기준으로 설명하며 Windows버전에 따라 약간 다를 수 있다)

제어판>관리도구>ODBC 데이터원본(설치된 ODBC의 종류에 따라 32비트 또는 64비트를 선택한다)
사용자 DSN과 시스템DSN 탭이 있는데 시스템DSN을 선택한다. 두가지는 기능적으로 동일한데 사용자DSN은 해당 DSN을 만든 사용자만 사용할 수 있으며 시스템DSN은 만든 사용자에 관계없이 모든 사용자가 사용할 수 있다.
추가버튼 클릭
드라이버 종류 선택(SQL Server를 선택)
이름과 연결할 서버를 지정
Database의 계정으로 로그인하려면, "사용자가 입력한 로그인ID 및 암호를 사용하는 SQL Server인증 사용"을 선택하고 ID와 암호를 입력
다음 클릭
마침 클릭
아래와 같은 그림이 나타나면 "데이터 원본테스트"버튼을 클릭해서 DB연결 테스트를 한다.
아래 그림이 나오면 정상적으로 DSN이 추가된 것이다.
이제 VBA에 코드에서 다음을 입력하고 DB에 연결해서 Data를 가져온다.


Sub test_dsn()

Dim rs As New ADODB.Recordset
Dim strSQL As String
Dim strConn As String
Dim i As Integer

'//연결문자열을 만든다. DSN=TEST_DSN의 형식으로 ODBC설정할때 등록한 DSN이름을 적고 DB Id,Password,Database명을 적는다.
strConn = "DSN=TEST_DSN;UID=sa;PWD=x1234;DATABASE=test_db;"

strSQL = "SELECT * FROM user_info"

'//DB에 연결하면서 Recordset을 연다.
rs.Open strSQL, strConn
If rs.EOF Then
    MsgBox "조회조건에 해당하는 자료가 없습니다."
Else
    '타이틀을 표시한다.
    For i = 1 To rs.Fields.Count
        Cells(1, i).Value = rs.Fields(i - 1).Name
    Next


    With ActiveSheet
        '조회한 결과집합(rs - 레코드셋)을 "출력"Sheet의 A2지점을 꼭지점으로 해서 출력한다.
        .Range("A2").CopyFromRecordset rs
    End With
End If


rs.Close
Set rs = Nothing

End Sub

2) ODBC – DSN이용안함

DSN을 이용하지 않으면 DSN등록절차는 필요없고 연결문자를 다음과 같이 변경하기만 하면 된다.

'//연결문자열을 만든다. DSN=TEST_DSN의 형식으로 ODBC설정할때 등록한 DSN이름을 적고 DB Id,Password,Database명을 적는다.
strConn = "DSN=TEST_DSN;UID=sa;PWD=x1234;DATABASE=test_db;"

'//위를 아래와 같이 변경한다.

'//연결문자열을 만든다. DSN대신에 다음과 같이 Drive, Server명을 적고, DB Id,Password,Database명을 적는다.
strConn = "Driver={SQL Server};Server=192.168.65.129;Database=test_db;UID=sa;PWD=x1234;"

3) OLEDB 이용

OLEDB를 이용하면 관계형Database뿐만 아니라 다양한 데이터소스에 연결할 수 있고 실행속도가 빠른 장점이 있다. 연결문자를 다음과 같이 바꾸면 된다.


'//연결문자열을 만든다.
strConn = "Provider=SQLOLEDB;Data Source=192.168.65.129;Initial Catalog=test_db;User ID=sa;Password=x1234;"

각각의 상세한 연결방법은 첨부파일을 참고한다.

첨부(실습용 파일) : 7강_VBA를_이용하여DB연결.xlsm

이번 강좌에서는 Database연결을 해 보았다. Database연결 이후에는 ADO를 이용하여 Data를 처리하면 된다. ADO의 상세기능은 다음 강좌에서 설명한다.

[ 엑셀에서 Database사용하기 강좌 목록 ]