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

공개됨 글쓴이 Admin댓글 6개

이번 강좌에서는 엑셀에서 VBA로 DB연결 및 Data처리를 위해 이해해야 할 기본개념과 연결방법을 알아보고자 한다.
Excel VBA프로그램에서 Database에 접속해서 Data처리 요청을 하고 결과를 받아오는 모습은 아래의 그림과 같이 간단히 표현할 수 있다. ADO를 통해 Data처리요청을 하고, OLEDB 또는 ODBC는 Database연결을 담당한다.

ado_odbc_oledb_concept

  • 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_architecture

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은 만든 사용자에 관계없이 모든 사용자가 사용할 수 있다.
  • 추가버튼 클릭
    dsn_setting_01
  • 드라이버 종류 선택(SQL Server를 선택)
    dsn_setting_02
  • 이름과 연결할 서버를 지정
    dsn_setting_03
  • Database의 계정으로 로그인하려면, “사용자가 입력한 로그인ID 및 암호를 사용하는 SQL Server인증 사용”을 선택하고 ID와 암호를 입력
    dsn_setting_04
  • 다음 클릭
    dsn_setting_05
  • 마침 클릭
    dsn_setting_06
  • 아래와 같은 그림이 나타나면 “데이터 원본테스트”버튼을 클릭해서 DB연결 테스트를 한다.
    dsn_setting_07
  • 아래 그림이 나오면 정상적으로 DSN이 추가된 것이다.
    dsn_setting_08
  • 이제 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사용하기 강좌 목록 ]

 

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

    • 안녕하세요.
      7강 이후에… 다음 강좌를 준비해야 하는데.. 제가 요새 게을러져서 준비를 못하고 있네요 ㅠ…
      아무튼 강좌가 도움이 되시길 바랍니다.
      감사합니다^^

      • 마지막까지 열심히 따라해서 파일을 작성해보고 있습니다.
        7강에서 나온 SQL 서버가 없어서 네트워크중의 한 컴퓨터의 공유폴더에 액세스db를 사용하고 있는데 연결문자열만 모르겠습니다.
        암튼 여태까지만도 엄청난 도움이 되었습니다.
        정말 감사드립니다.

댓글 남기기

이메일은 공개되지 않습니다.