16강 - VBA로 MariaDB에 연결하여 Data처리하기(ADO를 이용하여 조회,추가,수정,삭제)

2017-06-12 2022-02-11 / 23 댓글

16강에서는 VBA로 MariaDB에 연결하여 Data를 조회,추가,수정,삭제하는 작업을 해 보고자 한다.

MariaDB는 MySQL과 동일한 소스코드를 기반으로 만들어 졌으므로 사용법, API등이 거의 동일하다. 버전번호도 5.5까지는 동일하게 갔는데 MySQL 5.6으로 갈때 MariaDB는 버전 번호를 10.0으로 갔다. 2017년 6월 현재까지는 MySQL 5.6이 MariaDB 10.x 버전과 거의 호환된다. 비호환 항목도 있으므로 다음의 MariaDB 사이트에서 확인해 보면 된다. https://mariadb.com/kb/ko/mariadb-mysql/.
거의 다 호환이 되므로 MySQL용으로 설치한 ODBC Driver를 이용하여 MariaDB에 연결해도 문제없이 잘동한다. MariaDB사이트에서도 MySQL ODBC사용해도 잘 잘동한다고 나와 있다(Connector/ODBC for MySQL should also generally work with MariaDB).

그렇지만 언제까지 MySQL과 MariaDB가 호환될지 모르는 일이니 여기서는 MariaDB에서 제공하는 ODBC를 사용해서 연결해 보기로 하자. MariaDB용 ODBC Driver를 잘못 설치하거나 설정을 잘못하면 연결이 안되어 연결방법을 찾느라 고생할 수도 있으므로 아래 순서대로 차근차근 따라해 보자. 그러나 각자 PC의 OS 종류 및 버전, MySQL의 서버의 버전, 구성방식 등이 차이가 있을 수 있고 이 때문에 연결방법이 약간씩 다를 수 있다. 만약 연결이 안될때는 아래에 따로 적은 Trouble shooting부분을 참고하기 바란다.

1. 설치환경 및 사전작업

실습에 사용된 PC(Client) 및 MariaDB Server의 설치환경은 다음과 같다. 각자 다를 수 있다. 환경이 달라서 작업이 잘 되지 않는다면 MariaDB의 ODBC관련 사이트(https://mariadb.com/kb/en/mariadb/about-mariadb-connector-odbc/)를 방문하여 문제를 해결하기 바란다. 설치 및 설정 등에 관한 상세한 자료를 찾을 수 있다.
- Client O/S : Window 10 Pro, 64 Bit
  ODCB Driver : MariaDB ODBC 2.0 Driver 64 Bit
- Server O/S : Windows 10 Pro, 64 Bit
  MariaDB Server 10.2.
MariaDB용 ODBC설치 : MariaDB 홈페이지(https://downloads.mariadb.org/connector-odbc/)에서 MariaDB용 ODBC Driver(MariaDB Connector/ODBC)프로그램을 다운로드 해서 설치한다. 설치 PC의 Windows OS가 32비트이면 32비트 프로그램을 다운로드 해야 하고 64비트이면 64비트 프로그램을 다운로드 해야 한다. 설치시 특별히 어려운 점은 없다.
ODBC에서 DSN등록 : DSN을 이용하여 DB에 연결할 경우에만 필요하다. 내 PC의 OS의 종류에 맞게 ODBC프로그램을 실행해서 DSN을 등록해야 하는데 만약 OS가 64비트이면 64비트 ODBC를 실행해서 등록한다. DSN은 시스템DSN 또는 사용자DSN으로 등록하면 되는데 두개는 기능적으로 동일하다. 사용자DSN은 시스템에 로그인한 사용자만 사용할 수 있으며 시스템DSN은 로그인 사용자에 관계없이 보고, 사용할 수 있다. 각자 업무환경을 고려하여 둘 중에 하나를 선택하면 된다. DSN 등록시 DB연결에 관한 각종 Option도 설정가능하다. 아래 그림에 있는 필요한 Option을 선택하면 된다. DSN을 사용하지 않으면 아래의 선택 Option을 연결문자열에서 지정해야 한다.

MariaDB DSN Configuration 1

MariaDB DSN Configuration 2

2. 간단한 조회기능으로 먼저 개념 익히기

DB를 연결하고 처리하는 기본 개념을 익히기 위해 다음과 같이 Excel sheet에 Data를 출력하는 간단한 기능을 먼저 구현해 보자.

getting data from database to excel sheet — Database에서 Data를 가져와서 Excel sheet에 출력

1) Table 생성

MariaDB Server가 설치되어 있다고 가정하고 다음의 SQL로 테스트용 Table을 생성하자.

CREATE TABLE users (
    deptname varchar(50) NOT NULL,
    username varchar(50) NOT NULL,
    id int NOT NULL  PRIMARY KEY,
    salary float NOT NULL    
);

조회에 필요한 데이터는 INSERT SQL을 수행하여 각자 입력한다.

2) VBA에서 소스입력

VBA Module에 다음의 소스코드를 입력한다. MariaDB Server에서 Data를 가져와서 Excel sheet에 필드명과 Data를 출력하는 기능이다. 소스코드에 대한 설명은 주석에 달아 놓았다.

'//MariaDB Server에서 Data를 가져와서 Excel sheet에 출력하기
Sub test()

Dim conn As ADODB.Connection
Dim rs As New ADODB.Recordset
Dim strSQL As String
Dim i As Integer
    
    
    '//엑셀 Sheet에서 기존 조회내용 지우기
    Sheets("출력").Select
    Columns("A:E").Select
    Selection.ClearContents

           
    Set conn = New ADODB.Connection
           
    
    '//MariaDB에 연결하기 위해서는 여러가지 방법이 있을 수 있으나 여기에서는 아래 2가지 방식으로 연결문자열을 만든다.
    '//각자 사정에 맞는 방식을 사용하면 된다.
    '//
    '// 연결문자열에 들어가는 각각의 Keyword의 의미는 다음과 같다. 각자 환경에 맞도록 값을 수정한다.
    '// 각 설정값에 대한 상세한 설명은 아래의 MariaDB사이트를 참조할것
    '// ==&amp;amp;gt; https://mariadb.com/kb/en/mariadb/about-mariadb-connector-odbc/
    '//
    '// Driver : ODBC Driver이름, Windows에서 ODBC를 실행하여 사용자DSN이나 시스템DSN 탭에서 추가버튼을 누르면 Driver의 이름을 확인할 수 있다.
    '// DSN : ODBC설정에서 등록하는 Data source명(Data Source Name)
    '// Server : MariaDB Server Host name(보통 IP Address를 많이 쓴다)
    '// Port : MariaDB Server에 접근하기 위한 TCP/IP Port(MariaDB는 기본값이 3306이다)
    '// Uid : DB에 Login하는 User ID
    '// Pwd : Password
    '// Option : Option에 지정하는 값은 MariaDB ODBC의 작동방식을 지정하는 다양한 플래그에 대한 숫자 값의 합계임. 기본값은 0
    
    
    '// 방식1 : DSN을 이용하여 연결(상세한 연결Option은 DSN을 만들때 지정할 수 있다)
    conn.ConnectionString = "DSN=TEST_DSN_MariaDB;Uid=root;Pwd=x1234;Option=2;"
    
    '// 방식2 : DSN 이용안함, 대신 Server명, Port등을 지정해 주어야 한다.
    '//MySQL ODBC Driver를 사용해도 작동함    conn.ConnectionString = "Driver={MySQL ODBC 5.3 Unicode Driver};Server=192.168.65.128;Port=3306;Database=test;User=root;Password=x1234;Option=2;"
    'conn.ConnectionString = "Driver={MariaDB ODBC 2.0 Driver};Server=192.168.65.128;Port=3306;Database=test;User=root;Password=x1234;Option=2;"

    '//Database Connection을 Open한다.
    conn.Open
   
    Set rs = New ADODB.Recordset
    rs.CursorLocation = adUseClient
    
    
    '//조회할SQL을 문자열로 만든다.
    strSQL = "SELECT deptname,username,id,salary" & _
           "  FROM users " & _
           " WHERE id > 0 "
           
    
    '//Database에서 Data를 가져온다.
    rs.Open Source:=strSQL, ActiveConnection:=conn, CursorType:=adOpenForwardOnly, LockType:=adLockReadOnly, Options:=adCmdText
           
    '//Database에서 Data를 가져와서 Data가 있으면 Excel sheet에 출력한다.
    If rs.EOF Then
        MsgBox "조회조건에 해당하는 자료가 없습니다."
    Else
    
        '//users Table의 필드명을 가져와서 Excel sheet에 첫행에 표시한다.
        For i = 0 To rs.Fields.Count - 1
          Cells(1, i + 1).Value = rs.Fields(i).Name
        Next
    
        '//첫번째 레코드로 이동한다.
        rs.MoveFirst
        
        '//조회한 결과집합(rs)을 "출력"Sheet의 A2지점을 꼭지점으로 해서 출력한다.
        With ActiveSheet
           .Range("A2").CopyFromRecordset rs
        End With
        
    End If
                
    '//Recordset개체와 Connection개체의 연결을 닫는다.
    rs.Close
    conn.Close
    
    '//Recordset개체와 Connection개체를 소멸시킨다.
    Set rs = Nothing
    Set conn = Nothing
    
End Sub

3. Data 조회,추가,수정,삭제 기능 구현

아래 그림과 같이 VBA Form을 이용하여 DB의 Data를 조회,추가,수정,삭제 하는 기능이다. Excel sheet에 있는 버튼을 클릭하면 Form이 나타나고 각각의 버튼을 클릭했을때 Data를 처리하는 기능이다. VBA프로그램코드는 첨부의 파일을 참고하기 바란다.

조회기능 : Form에서 조회버튼을 누르면 Database에서 Data를 가져와서 Form에 조회한다.
저장기능 : Form에서 저장버튼을 누르면 Database에 Data를 추가(Insert)하거나 수정(Update)한다.
삭제기능 : Form에서 삭제버튼을 누르면 Database에서 Data를 삭제(Delete)한다.

※ Trouble Shooting

첨부의 VBA프로그램을 다운로드 받아서 필요한 부분을 작업하고 각자 환경에 맞게 VBA을 수정하여 실행하여도 에러가 나면 아래의 케이스를 확인해 보기 바란다.

"[ODBC 드라이버 관리자] 지정된 DSN은 드라이버와 응용 프로그램 간 아키텍처 불일치를 포함합니다" 에러 발생시 : OS의 종류와 설치된 ODBC드라이버의 종류가 다르기 때문에 발생한다. 예를 들면 64비트 운영체제에서 32비트 ODBC를 사용하면 오류가 발생한다. OS의 종류에 맞는 ODBC Driver를 다운로드해서 설치해야 한다.
"Can't connect to MySQL server on ..." 에러 발생시 : MariaDB ODBC인데도 불구하고 메시지는 MySQL Server에 연결할 수가 없다고 나온다. ODBC Driver가 이름만 다르고 속은 MySQL ODBC Driver와 거의 동일한 모양이다. MariaDB Server가 Shut down되었을 때 발생할 수 있다. 시스템관리자에게 MariaDB가 살아 있는지 확인해 본다.
[ODBC 드라이버 관리자] 데이터 원본 이름이 없고 기본 드라이버를 지정하지 않았습니다" 에러 발생 시 : DB연결문자열이 잘못되었을 수 있다. 예를 들면 Driver명을 잘 못 지정하거나 DSN명이 틀리면 이 에러가 발생할 수 있다. Windows에서 ODBC를 실행하여 DSN명이 정확한 지 확인해보고 DSN명이 정확하다면 사용자DSN이나 시스템DSN 탭에서 추가버튼을 눌러서 Driver의 이름을 확인한다. DB연결문자열에 있는 Driver명과 ODBC설정에 있는 Driver명은 동일해야 한다. 예제에서는 Driver 명이 "MariaDB ODBC 2.0 Driver"인데 만약 "MariaDB ODBC 2.0 Driver"로 입력하면 이 에러가 발생한다. 뒤에 적은 문자열을 자세히 살펴보기 바란다. "MariaDB"와 "ODBC 2.0" 사이에 공백이 2칸이다. 공백 하나만 잘못 입력해도 Driver인식이 안되므로 아래 그림과 같이 정확한 이름을 입력하여야 한다.
MariaDB ODBC Connectstring

위의 방법으로 해결이 되지 않는다면 MariaDB에서 제공하는 아래의 사이트를 참고한다.
https://mariadb.com/kb/en/mariadb/documentation/

이상으로 16강을 마친다. 구체적인 기능은 첨부의 실습용 파일을 참고하기 바라며, 이 프로그램은 Database프로그램의 구조를 쉽게 이해할 수 있도록 하기 위해서 만든 것이므로 실제로 사용하기에는 비효율적인 부분이 많다, 실제 업무에 적용시에는 입력Data의 Validation, DB연결을 공통기능으로 만든다든지 하는 추가적인 노력이 필요함을 알려둔다.

첨부(실습용 엑셀 매크로 파일) :

실습용 엑셀파일 다운로드 :16강_VBA로 MariaDB에 연결하여 Data처리하기(ADO를 이용하여 조회_추가_수정_삭제).xlsm

실습용 엑셀파일 다운로드(listview버전 추가) :16강_VBA로 MariaDB에 연결하여 Data처리하기(ADO를 이용하여 조회_추가_수정_삭제)_listview추가_20220211.zip

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

엑셀웍스 책 출간 안내

그동안 엑셀웍스에 올린 '엑셀 강좌', '함수 사용법', 여러 '실무 예제'에서 핵심만 뽑아 '된다! 엑셀 수식 & 함수' 책으로 출간하였습니다.

자세히 보기

온라인 구매처: 예스24 교보문고 알라딘

“16강 - VBA로 MariaDB에 연결하여 Data처리하기(ADO를 이용하여 조회,추가,수정,삭제)”의 23개의 댓글

박소현
2018-09-06 / 17:55

mariadb에서 dsn을 연결하려 하는데요,
ODBC는 1.0_32 Bit 버전이고, 마리아디비 5.3버전 ,윈도우 XP 입니다.
연결하는 과정에서 odbc관리자에서 추가를 하여 위에 제공된 글까지는 완료하였는데, 매처음 data source의 이름을 결정하는 곳에서 error : 잘못된 드라이버 또는 변환기 이름입니다. 라는 문구가 뜹니다. 이유가 뭔지 모르겠습니다..ㅠㅠ

답글
1. Admin
  2018-09-09 / 23:40
  
  안녕하세요. vba소스에서 DSN부분이 안된다는 말씀이신가요? 만약에 그렇다면 ODBC설정에서 추가한 DSN을 선택해서 “Test DSN”을 해 보시기 바랍니다(ODBC를 열고 시스템DSN>추가한 MariaDB용 DSN선택>오른쪽 구성버튼 클릭>Next>Test DSN클릭). 여기서 잘 안된다면 DSN이 잘못추가 된것이고 잘된다면 vba 소스가 문제가 있으니 소스를 admin@xlworks.net를 보내주시면 확인해 보겠습니다.
  감사합니다.
  
  답글
이명신
2018-10-02 / 09:09

안녕하세요. 올려주신 강좌 덕분에 정말 많은 도움이 되고 있습니다.
강좌를 따라 공부하던 중에 해결이 안되는 부분이 있어서 이렇게 문의 드립니다.
데이터베이스 테이블에서 'date' 형식의 필드 내용을 가져올 때
'-2147467259(80004005)'런타임 오류가 발생하였습니다:
데이터 공급자나 기타 서비스가 E_FAIL 상태를 반환했습니다.
라는 오류메세지가 뜹니다.
예를 들어 생년월일 필드를 DATE 형식으로 지정하여 값을 입력해 놓았습니다.
그런데 데이터 형식을 숫자나 텍스트로 변경하면 조회가 가능한데 DATE형식으로 하면
위와 같은 메세지가 뜹니다. 데이터베이스에서 이래저래 만져봐도 안되고, 엑셀 VBA에서 코드를
수정하면 해결이 되는 부분인지, 만약 가능하다면 어떻게 수정하면 되는지 궁금합니다.
데이터베이스에서 가능하면 생년월일 같은 필드는 DATE형식으로 유지하고 싶습니다.
며칠을 인터넷 서칭을 하며 문제를 해결하려 했지만 결국 못하고 이렇게 문의드립니다.
도움을 주신다면 정말 감사하겠습니다.

답글
1. Admin
  2018-10-03 / 17:29
  
  안녕하세요.
  E_FAIL상태 오류는 구글 검색을 해 보니 날짜가 NULL일때 주로 생기는 오류라고 합니다(아래 링크 참조)
  http://mimibogiri.blogspot.com/2011/10/efail.html
  테이블 생성시 날짜타입을 NOT NULL로 하시거나 SELECT해 올때 NULL처리를 해 보시기 바랍니다.
  아래 예제 파일에 날짜타입 추가해서 테스트 해 보았는데 정상작동 됩니다. 다운로드해서 참고하세요.
  https://xlworks.net/wp-content/uploads/2018/10/16강_VBA로-MariaDB에-연결하여-Data처리하기ADO를-이용하여-조회_추가_수정_삭제_Date칼럼추가.xlsm
  
  감사합니다.
  
  답글
  1. 익명
    2018-10-03 / 21:06
    
    답변 감사드립니다.
    ^^ 저는 아무리 해도 오류문구가 계속 뜨네요...
    제 경우는 아무래도 not null문제는 아닌 것 같습니다.
    답글에 달아주신 파일을 다운받아 테스트 해도 저한테는 같은 오류가 뜨네요.
    물론 저한테 맞게 수정해서 데이터베이스에 연결했습니다. ^^
    
    공부를 더 열심히 해서 해결하도록 하겠습니다.
    다시 한번 답변해주신 것 감사드립니다.
    
    답글
김성진
2018-11-15 / 23:58

강의 잘읽었습니다. 제가 다니는 회사는 서울. 인천. 부산등에 지점을 가지고 있는 유통업체입니다. 서울에 서버를 두고 마리아db가 설치되어있고요. 본점과 지점에서 판매량등이 특정프로그램에 의해 마리아디비에 쌓이고 있습니다. 특정프로그램은 외주를 줘서 만들었습니다. 외주업체가 지속적으로 무리하게 유지보수비를 요구하여 제가 직접 엑셀로 판매량등이 기록되는 프로그램을 만드려고 합니다. 네트워크를 잘 몰라서 이게 가능한지 여쭙고 싶습니다.

답글
1. Admin
  2018-11-16 / 01:33
  
  안녕하세요. 말씀하신 환경에서 엑셀로 시스템을 구축하는 것은 불가능합니다. 여러 지역에 지점이 있고 서울에 있는 DB에서 DATA가 관리되어야 한다면 동시성을 보장할 수 있는 전문 Database가 필요합니다. 엑셀에서는 이러한 기능을 제공하지 않습니다.
  감사합니다.
  
  답글
  1. 김성진
    2018-11-16 / 06:39
    
    엑셀에 입력되는 데이터를 마리아디비로 넘기면 되지 않을까요? 16강 내용이 엑셀데이터를 마리아디비로 넘기는것 아닌지요? 꼭 실시간일 필요는 없습니다. 지점a에서 입력한 엑셀데이터를 본사에 있는 마리아디비로 넘기는 방식이 불가능한건지 여쭙니다.
    
    답글
    1. Admin
      2018-11-16 / 21:29
      
      DB서버와 프로그램이 같은 네트워크상에 있다면 가능한것으로 알고 있습니다(IP address로 식별이 가능한 네트워크). Domain name으로도 가능한지는 네트워크 전문가에게 문의하셔야 할것 같습니다.
      
      답글
김성진
2018-11-15 / 23:59

엑셀과 비주얼베이직으로 만드려고 합니다.

답글
김성한
2019-03-20 / 18:08

엑셀의 비주얼베이직으로 마리아 디비의 데이타를 SQL 문을 통해 실시간으로 가져올 수 있습니다.
또한 가져온 데이타를 엑셀 시트에 정해진 서식에 뿌려 인쇄할 수도 있습니다. 현재 사용하고 있습니다.

답글
김성한
2019-03-20 / 18:11

당연히 마리아디비 서버는 본사에 있고 다른 도시의 현장에서 데이타를 입력합니다.

답글
이민우
2022-01-28 / 12:03

개인적으로는 '엑셀에서 DB를 사용하여 자료를 관리하고 활용 1강~18강까지' 너무 잘 정리해 주셔서 국내, 해외사이트를 통틀어 마치 '엑셀과 DB의 바이블'같은 소중한 포스팅이라고 생각됩니다.

이렇게 정리해서 공유해 주시기까지 얼마나 많은 노력이 있으셨을지 생각해보면 감사함에 숙연해지기까지 합니다. 혹시라도 기회가 되신다면 16강만이라도 기존 listbox대신 listview를 사용한 실습용파일을 부탁드려봐도 될런지 조심스럽게 청을 올려봅니다.

답글
1. Admin
  2022-01-29 / 12:02
  
  안녕하세요. 못난 글을 잘 평가해 주셔서 감사합니다^^.
  listview로 만드는 것은 2월중에 해서 사이트에 올려보겠습니다.
  감사합니다.
  
  답글
  1. 이민우
    2022-01-31 / 19:58
    
    진심으로 감사드립니다.
    
    답글
    1. Admin
      2022-02-11 / 21:04
      
      listview 버전 추가되었습니다. 본문 아래 쪽에 다운로드 링크 있습니다.
      
      답글
      1. 이민우
        2022-02-12 / 00:15
        
        네~ 적용해보니 아주 완벽하게 잘 작동합니다. 너무너무 감사드립니다.
잠자는라이언
2022-05-28 / 18:19

감사의 인사를 먼저 드립니다.

디비에서 값을 불러오면 한글이 깨져서 불러와 집니다. cafe24마리아디비 사용중이고요
해결 방법이 없을까요? 참고로 입력은 웹디비로 한글이 잘 넘어갑니다.

답글
1. Admin
  2022-05-29 / 19:32
  
  maria DB에서 HeidiSQL같은 도구로 select 문을 날려서 한글이 정상적으로 표시되는지 먼저 확인해 보셔야 할것 같습니다. character set 설정된 것이 unicode(utf8등)를 지원하는지도 확인해 보셔야 할것 같습니다.
  
  답글
류근률
2022-11-03 / 16:29

좋은글 감사합니다

보면서 따라 진행하였고 odbc 관리자를 통하여 마리아디비에는 접속이 가능했으나
vb에서 연결 시 [Microsoft][ODBC 드라이버 관리자] 데이터 원본 이름이 없고 기본 드라이버를 지정하지 않았습니다 라는 오류 메세지가 뜹니다.
conn.ConnectionString = "Driver={MariaDB ODBC 3.1 Driver};Server=127.0.0.1;Port=3306;Database=TESTDB;User=root;Password=1234;Option=2"
이렇게 사용하였구요 ado 라이브러리는 2.7 library 사용했습니다.
확인해봐야 할 것이 있을까요..?

답글
1. Admin
  2022-11-04 / 11:58
  
  위에 "※ Trouble Shooting" 부분 참고하시고요, "MariaDB ODBC 3.1 Driver" 이 부분에서 MariaDB 뒤의 공백 갯수 확인해 보세요.
  
  답글
  1. 익명
    2022-11-04 / 17:21
    
    그부분을 제대로 읽어 보지 못했네요..
    64비트 노트북인데 혹시나 해서 32비트 connector 설치해서 해결 했습니다.
    답변감사합니다
    
    답글
이태관
2022-11-19 / 13:06

감사합니다
Range 부분이 병합되어 있다면 어찌하면 되나요?

답글