이번 강좌에서는 엑셀에서 VBA로 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사용하기 강좌 목록 ]
- 18강 - VBA로 MS SQL Server에 연결하여 Data처리하기(Stored Procedure로 조회)
- 17강 - VBA로 PostgreSQL에 연결하여 Data처리하기(ADO를 이용하여 조회,추가,수정,삭제)
- 16강 - VBA로 MariaDB에 연결하여 Data처리하기(ADO를 이용하여 조회,추가,수정,삭제)
- 15강 - VBA로 MySQL에 연결하여 Data처리하기(ADO를 이용하여 조회,추가,수정,삭제)
- 14강 - VBA로 Oracle DB Server에 연결하여 Data처리하기(ADO를 이용하여 조회,추가,수정,삭제)
- 13강 - VBA로 MS SQL Server에 연결하여 Data처리하기(ADO를 이용하여 조회,추가,수정,삭제)
- 12강 – VBA로 Access DB에 연결하여 Data처리하기(ADO를 이용하여 Data삭제)
- 11강 – VBA로 Access DB에 연결하여 Data처리하기(ADO를 이용하여 Data추가)
- 10강 – VBA로 Access DB에 연결하여 Data처리하기(ADO를 이용하여 Data조회 및 저장)
- 9강 – VBA로 Access DB에 연결하여 Data처리하기(ADO를 이용하여 연결, 조회)
- 8강 : VBA로 엑셀에서 Database 연결 및 처리를 위한 ADO이해
- 7강 – VBA로 엑셀에서 Database 연결, Data처리 기본개념
- 6강 – VBA로 엑셀Sheet에 연결하기(Data삭제-ADO이용안함)
- 5강 – VBA로 엑셀Sheet에 연결하기(ADO를 이용하여 Data추가)
- 4강 - VBA로 엑셀Sheet에 연결하기(ADO를 이용하여 조회 및 저장)
- 3강 - VBA로 엑셀Sheet에 연결하기(ADO를 이용하여 연결, 조회)
- 2강 - Microsoft Query를 이용하여 Database에 연결하기
- 1강 - 들어가기 - Database는 무엇인가, 왜 엑셀에서 Database를 다루는가?
멋진 강좌 잘 보고있습니다.
멋진 강좌 잘 보고있습니다.
안녕하세요.
7강 이후에... 다음 강좌를 준비해야 하는데.. 제가 요새 게을러져서 준비를 못하고 있네요 ㅠ...
아무튼 강좌가 도움이 되시길 바랍니다.
감사합니다^^
마지막까지 열심히 따라해서 파일을 작성해보고 있습니다.
7강에서 나온 SQL 서버가 없어서 네트워크중의 한 컴퓨터의 공유폴더에 액세스db를 사용하고 있는데 연결문자열만 모르겠습니다.
암튼 여태까지만도 엄청난 도움이 되었습니다.
정말 감사드립니다.
안녕하세요. 저는 액세스DB를 안써봐서 모르겠지만... 아래 사이트에 나와있는 DB연결 문자열 사용하시면 될것 같습니다.
https://www.connectionstrings.com/access/
와우 상황에 따른 연결 문자열이군요... 감사합니다. 정말 큰 힘이 됩니다.
안녕하세요 강의 보고 있습니다.
정말 많은 도움이 되고 있습니다. 감사합니다 ^^
현재 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여야 합니다.
감사합니다.