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사용하기 강좌 목록 ]

• 1강 - 들어가기 - Database는 무엇인가, 왜 엑셀에서 Database를 다루는가?
• 2강 - Microsoft Query를 이용하여 Database에 연결하기
• 3강 - VBA로 엑셀Sheet에 연결하기(ADO를 이용하여 연결, 조회)
• 4강 - VBA로 엑셀Sheet에 연결하기(ADO를 이용하여 조회 및 저장)
• 5강 – VBA로 엑셀Sheet에 연결하기(ADO를 이용하여 Data추가)
• 6강 – VBA로 엑셀Sheet에 연결하기(Data삭제-ADO이용안함)
• 7강 – VBA로 엑셀에서 Database 연결, Data처리 기본개념
• 8강 - VBA로 엑셀에서 Database 연결 및 처리를 위한 ADO이해
• 9강 – VBA로 Access DB에 연결하여 Data처리하기(ADO를 이용하여 연결, 조회)
• 10강 – VBA로 Access DB에 연결하여 Data처리하기(ADO를 이용하여 Data조회 및 저장)
• 11강 – VBA로 Access DB에 연결하여 Data처리하기(ADO를 이용하여 Data추가)
• 12강 – VBA로 Access DB에 연결하여 Data처리하기(ADO를 이용하여 Data삭제)
• 13강 - VBA로 MS SQL Server에 연결하여 Data처리하기(ADO를 이용하여 조회,추가,수정,삭제)
• 14강 - VBA로 Oracle DB Server에 연결하여 Data처리하기(ADO를 이용하여 조회,추가,수정,삭제)
• 15강 - VBA로 MySQL에 연결하여 Data처리하기(ADO를 이용하여 조회,추가,수정,삭제)
• 16강 - VBA로 MariaDB에 연결하여 Data처리하기(ADO를 이용하여 조회,추가,수정,삭제)
• 17강 - VBA로 PostgreSQL에 연결하여 Data처리하기(ADO를 이용하여 조회,추가,수정,삭제)
• 18강 - VBA로 MS SQL Server에 연결하여 Data처리하기(Stored Procedure로 조회)