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

공개됨 글쓴이 Admin댓글 8개

이번 강좌에서는 엑셀에서 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를 사용하고 있는데 연결문자열만 모르겠습니다.
        암튼 여태까지만도 엄청난 도움이 되었습니다.
        정말 감사드립니다.

  1. 안녕하세요 강의 보고 있습니다.
    정말 많은 도움이 되고 있습니다. 감사합니다 ^^

    현재 ODBC와 SQL Server에 연결하려 하는데요,

    사전에 MS SQL SERVER 20XX 버전을 설치하고(이 어플리케이션이 ODBC 인지요?),
    MS SQL SERVER 20XX를 지원하는 Cafe24와 같은 유료 웹 서버에 가입해서,

    해당 웹서버의 IP주소를 연결할 SQL 서버에 기입하는 것인지요?

    제가 네트워크 구성은 처음인지라 조언 좀 부탁드리겠습니다. ^^
    감사합니다.

    • 안녕하세요. 답변이 늦었습니다 ㅠㅠ…
      MS SQL Server는 Database입니다. Microsoft홈페이지에 가시면 다운로드해서 PC에 설치해서 사용할수도 있습니다(https://www.microsoft.com/ko-kr/sql-server/sql-server-editions-express)
      물론 Cafe24같은데서 서비스를 받을 수도 있습니다. ODBC는 Open DataBase Connectivity의 약자인데 PC와 Database를 연결해주는 중간매개 역할을 하는 프로그램이라고 보시면 됩니다.
      그리고 ip입력은 웹서버가 아니라 Database의 서버의 ip여야 합니다.
      감사합니다.

댓글 남기기

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