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

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

PostgreSQL은 MySQL, MariaDB 등의 Open source DB와 연결방법이 크게 다르지 않다. PostgreSQL용 ODBC를 설정하고 연결문자열을 만드는 방법이 거의 동일하다. 그렇지만 ODBC Driver를 잘못 설치하거나 설정을 잘못하면 연결이 안되어 연결방법을 찾느라 고생할 수도 있으므로 아래 순서대로 차근차근 따라해 보자. 그러나 각자 PC의 OS 종류 및 버전, MySQL의 서버의 버전, 구성방식 등이 차이가 있을 수 있고 이 때문에 연결방법이 약간씩 다를 수 있으므로 각자 환경에 맞도록 설정해야 한다. 만약 연결이 안될때는 아래에 따로 적은 Trouble shooting부분을 참고하기 바란다.

1. 설치환경 및 사전작업

실습에 사용된 PC(Client) 및 PostgreSQL DB Server의 설치환경은 다음과 같다. 각자 다를 수 있다. 환경이 달라서 작업이 잘 되지 않는다면 PostgreSQL의 ODBC관련 사이트(https://odbc.postgresql.org/)를 방문하여 문제를 해결하기 바란다. 설치 및 설정 등에 관한 상세한 자료를 찾을 수 있다.
- Client O/S : Window 10 Pro, 64 Bit
  ODCB Driver : PostgreSQL Unicode(x64)
- Server O/S : Windows 10 Pro, 64 Bit
  PostgreSQL 9.6.
PostgreSQL용 ODBC설치 : PostgreSQL ODBC 다운로드 페이지(https://www.postgresql.org/ftp/odbc/versions/msi/)에서 PostgreSQL용 ODBC Driver프로그램을 다운로드 해서 설치한다. 이 사이트에 들어가면 zip파일이 여러 개가 보이는데 최신 것 중에서 각자의 PC환경에 맞는 것을 다운로드 한다. 설치 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도 설정가능하다. 아래 그림에 있는 필요한 Options를 선택하면 된다. DSN을 사용하지 않으면 아래의 선택 Option을 연결문자열에서 지정해야 한다.< ODBC를 실행하여 시스템 DSN에서 "추가" 버튼을 클릭한다>

< 아래에서 "PostgreSQL Unicode(x64)"를 선택한다. 필요에 따라 바로 위에 있는 PostgreSQL ANSI(x64)를 선택할 수도 있다 >
< DB 접속정보를 입력한다, Data Source는 원하는 이름을 입력한다. 필요 정보를 입력하고 "Test"버튼을 클릭하여 DB에 접속이 되는 지 확인한다 >

< 만약에 DB접속이 제대로 되지 않으면 다음과 같은 에러가 날 수 있다. PC의 IP가 막혀있다는 뜻이다. 접속 할 수 있도록 시스템 관리자에게 요청해야 한다 >

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

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

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

1) Table 생성

PostgreSQL DB 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에 다음의 소스코드를 입력한다. PostgreSQL DB Server에서 Data를 가져와서 Excel sheet에 필드명과 Data를 출력하는 기능이다. 소스코드에 대한 설명은 주석에 달아 놓았다.

'//PostgreSQL DB 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
           
    
    '// PostgreSQL DB에 연결하기 위해서는 여러가지 방법이 있을 수 있으나 여기에서는 아래 2가지 방식으로 연결문자열을 만든다.
    '//각자 사정에 맞는 방식을 사용하면 된다.
    '//
    '// 연결문자열에 들어가는 각각의 Keyword의 의미는 다음과 같다. 각자 환경에 맞도록 값을 수정한다.
    '// 각 설정값에 대한 상세한 설명은 아래의 PostgreSQL ODBC사이트를 참조할것
    '// ==&gt; https://odbc.postgresql.org/docs/config.html
    '//
    '// Driver : ODBC Driver이름, Windows에서 ODBC를 실행하여 사용자DSN이나 시스템DSN 탭에서 추가버튼을 누르면 Driver의 이름을 확인할 수 있다.
    '// DSN : ODBC설정에서 등록하는 Data source명(Data Source Name)
    '// Server : MariaDB Server Host name(보통 IP Address를 많이 쓴다)
    '// Port : PostgreSQL DB Server에 접근하기 위한 TCP/IP Port(MariaDB는 기본값이 5432이다)
    '// Username : DB에 Login하는 User ID
    '// Password : Password
    '// 기타 Option : 추가적인 연결Option을 지정할 수 있다. 위에 언급한 사이트에서 확인할 수 있다.
    
    
    '// 방식1 : DSN을 이용하여 연결(상세한 연결Option은 DSN을 만들때 지정할 수 있다)
    'conn.ConnectionString = "DSN=TEST_DSN_PostgreSQL;Username=postgres;Password=x1234;"
    
    '// 방식2 : DSN 이용안함, 대신 Server명, Port등을 지정해 주어야 한다.
    conn.ConnectionString = "Driver={PostgreSQL Unicode(x64)};Server=192.168.65.128;Port=5432;Database=test;Username=postgres;Password=x1234;"

    '//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를 다운로드해서 설치해야 한다.
"timeout expired" 에러 발생시 : DB연결시간을 초과하거나 PostgreSQL DB Server가 Shut down되었을 때 발생할 수 있다. 시스템관리자에게 DB가 살아 있는지 확인해 본다.
[ODBC 드라이버 관리자] 데이터 원본 이름이 없고 기본 드라이버를 지정하지 않았습니다" 에러 발생 시 : DB연결문자열이 잘못되었을 수 있다. 예를 들면 Driver명을 잘 못 지정하거나 DSN명이 틀리면 이 에러가 발생할 수 있다. Windows에서 ODBC를 실행하여 DSN명이 정확한 지 확인해보고 DSN명이 정확하다면 사용자DSN이나 시스템DSN 탭에서 추가버튼을 눌러서 Driver의 이름을 확인한다. VBA소스의 DB연결문자열에 있는 Driver명과 ODBC설정에 있는 Driver명은 동일해야 한다. 예제에서는 Driver 명이 "PostgreSQL Unicode(x64)"인데 만약 "PostgreSQL Unicode(x64)"로 입력하면 이 에러가 발생한다. 뒤에 적은 문자열을 자세히 살펴보기 바란다. "PostgreSQL"와 "Unicode" 사이에 공백이 2칸이다. 공백 하나만 잘못 입력해도 Driver인식이 안되므로 아래 그림과 같이 정확한 이름을 입력하여야 한다.
PostgreSQL ODBC Connectstring

위의 방법으로 해결이 되지 않는다면 MariaDB에서 제공하는 아래의 사이트를 참고하거나 댓들을 올려주기 바란다. https://odbc.postgresql.org/

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

첨부(실습용 엑셀 매크로 파일) : 17강_VBA로 PostgreSQL에 연결하여 Data처리하기(ADO를 이용하여 조회_추가_수정_삭제).xlsm

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