엑셀, 곽승주님의 오튜공구함 제작으로 배워보는 VBA 이야기, Excel

자료다운로드 : 오튜공구함004.xls, members.mdb

안녕하세요. 오튜가족 여러분!

한동안 뜸했던 곽승주입니다. 오늘의 주제는 데이터베이스에 관한 것입니다. 데이터베이스는 한번에 배울 수 없을 만큼 그 내용이 방대하고 어렵습니다. 그러나, 초보에서 중급으로 발돋움 하시려는 분들은 한번 보아주셨으면 하는 것이라 데이터베이스를 꺼내보았습니다.

사실 데이터베이스만큼 필요한 것은 없다고 봅니다. 취미로 프로그래밍을 하든 생업으로 하든 데이터베이스는 우리가 숨쉬는 공기만큼 중요하지만 눈에 보이질 않아 중요성을 잘 모를 수 있습니다. 여러분의 하드디스크도 일종의 데이터베이스입니다. 탐색기를 열어보시죠.각각의 파일과 폴더는 하나의 레코드이며 파일이름, 크기, 종류, 바뀐 날짜 등은 필드에 해당합니다. (이번 연재는 데이터베이스에 대한 사전 지식 즉 레코드, 필드, SQL 정도는 아시리라 생각하고 시작하겠습니다.)

오늘부터 시작하여 3회에 걸쳐 데이터베이스 중 마이크로소프트사의 걸작 중에 하나인 ADO를 배워봅시다. ADO를 처음부터 설명하려면 UDA(Universal Data Access), OLEDB, DAO, RDO 등등을 차례차례 설명하는 것이 순서이지만 이런 것에 대해서는 이미 많은 데이터베이스관련 웹사이트에서 설명하고 있어 생략합니다. 다만 여기서는 ADO사용이 데이터베이스를 다루는 기술이며 ADO 개체와 데이터베이스사용에 대한 간단한 설명과 예제를 보여드리겠습니다. ADO에 대해 자세히 알고 싶으시다면 다음의 두 권의 책을 소개해드립니다.
taeyo's Advanced ASP to be Professional(정보문화사) , @DO & MTS Programming

1. ADO 개체
ADO 개체모델은 다음 그림과 같습니다.

여러 개체가 있지만 가장 자주 사용하고 중요한 3가지 개체로는 Conncetion 개체, Command 개체, Recordset 개체가 있습니다.

Connection 개체	데이터베이스의 연결에 사용됩니다. 연결할 서버와 데이터베이스를 결정하며, 연결뿐 아니라 명령을 실행하고 결과값을 가져와서 볼 수도 있습니다.
Command 개체	데이터베이스에 연결 후 SQL문이나 명령을 실행할때 사용합니다.
Recordset 개체	데이터베이스에 연결, 명령 수행 후 결과 값을 받아 올 때 사용합니다.

● Connection 개체

Connection 개체는 데이터베이스와 연결하는 데 필요한 프로퍼티와 메서를 제공하고 있습니다. Connection 개체로 연결되어야만, 추가.수정.삭제등 데이터베이스상의 SQL명령과 필요한 데이터를 결과 값으로 가져올 수 있습니다.

- 프로퍼티
ConnectionString : 데이터소스에 연결을 하기위한 정보
ConnectionTimeOut : 연결 후 종료까지의 시간

- 메서드
Open() : 데이터소스를 연결
Close() : 연결을 종료Execute() : SQL 문 저장 프로시저를 실행

- 트랜잭션
BeginTrans() : 새로운 트랙잭션을 시작
CommitTrans() : 수정 내용을 저장하고 트랜잭션 종료
RollbackTrans() : 수정 내용을 취소하고 트랜잭션 종료

● Command 개체

Command 개체는 위에서 배운 Connection 개체와 마찬가지로 연결과 실행의 작업을 할 수 있습니다. Command 개체는 ActiveConnection 개체를 사용하여 연결을 하는데 데이터 소스 연결시 명령을 처리하는 동안만 연결하게 됩니다.

- 프로퍼티
ActiveConnection : Command 개체와 사용되는 연결을 위한 개체
CommandText : 실행될 명령어 저장
CommandType : CommandText 프로퍼티에 사용하는 명령어 타입지정
Prepared : 실행전에 문서를 컴파일시킬 지를 지정
CommandTimeout : 실행을 기다리는 시간지정. 디폴트 : 30초

- 메서드
CreateParameter : Parameter 콜렉션에서 새로운 Parameter 개체 생성
Execute : SQL문 과 저장 프로시저를 실행

● RecordSet개체

Connection 개체와 Command 개체에서 데이터 소스와 연결하고 실행하면 그 결과는 하나의 명령만 처리될수 도 있지만 대개는 RecordSet에 결과가 저장됩니다.
RecordSet은 여러개의 레코드들로 구성되어 있으며 RecordSet에 대한 각종 명령을 수행합니다.

- 프로퍼티
ActiveConnection : 레코드셋에 있는 연결 개체
BOF : 현재 레코드의 위치가 첫번째 레코드 위에 있으면 True
EOF : 마지막 레코드 다음에 레코드의 위치가 있으면 True
CursorType : 레코드셋에 사용되는 커서 타입
LockType : 편집중 레코드에 지정된 락 타입
PageCount : 레코드셋의 페이지수
PageSize : 하나의 페이지를 구성하는 레코드 수 지정
RecordCount : 현재레코드셋의 레코드 수

- 메서드
Open : 레코드셋에서 커서 열기
Close : 레코드셋 닫기
AddNew : 수정 가능한 레코드셋에서 새로운 레코드 생성
Delete : 레코드셋의 현재 레코드를 닫음Update : 현재 레코드 변경
Move : 현재레코드에서 지정된 수만큼 이동
MoveFirst : 레코드의 처음으로 이동
MoveLast : 마지막 레코드로 이동
MoveNext : 다음 레코드로 이동
MovePrevious : 이전 레코드로 이동

위의 내용이 지루하시죠? 그러면 지금부터는 간단히 MDB파일을 가지고 데이터베이스에 접속하여 데이터를 워크시트로 가져오는 것을 보여드리겠습니다.
먼저 MDB파일을 하나 구해보죠 다음의 Members.MDB파일은 MAIN테이블 하나로 구성되어 있고 그 테이블에는 간단히 회원이름, 회원아이디, 전화번호가 기록되어 있습니다.

이제 데이터베이스는 준비되었으니 엑셀에서 새 통합문서를 하나 준비합니다.
새 통합문서를 만들면 바로 VBE로 가셔서 ADO개체를 현재 새 통합문서에 포함시켜주어야 합니다(도구(T)-참조(R)를 클릭한다.)

프로그램의 코딩은 다음과 같습니다.
Option Explicit

'데이터베이스 접속을 위한 connection개체변수입니다.
Public adoConn As ADODB.Connection
'레코드셋을 가져오기 위한 recordset개체변수입니다.
Public adoRst As ADODB.Recordset
'연결을 위한 연결문자열입니다. Data Source=members.mdb부분을 보시면 접속할 데이터베이스파일을 지정하고 있습니다.
Public Const CONN_ADO As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=members.mdb;Jet OLEDB:Database Password="

'데이터베이스 연결을 위한 프로시져입니다.
Public Sub ADOConnect(strConn As String)
     '개체변수는 Set ~ = New ~ 형식으로 할당해야 하는 거 아시죠!
     Set adoConn = New ADODB.Connection
     '30초가량 연결을 시도합니다. 물론 연결시도가 30초가 넘으면 에러를 냅니다.
     adoConn.CommandTimeout = 30
     '연결을 시도합니다.
     adoConn.Open strConn
End Sub

'데이버베이스 연결을 끊은 프로시져입니다.
Public Sub ADODisconnect()
     '데이터베이스와의 연결을 끊습니다.
     adoConn.Close
     '개체변수에 할당한 메모리를 해제합니다.
     Set adoConn = Nothing
End Sub

'데이터베이스로부터 데이터를 가져오는 프로시져입니다.
Public Sub GetRecord()
     Dim strSQL As String
     Dim row As Integer
     '지정한 연결문자열을 이용하여 데이터베이스 연결을 시도힙니다.
     ADOConnect CONN_ADO
     '여기서는 간단히 선택쿼리를 하였습니다.
     strSQL = "SELECT * FROM main"

     Set adoRst = New ADODB.Recordset
     With adoRst
          .CursorLocation = adUseClient
          .LockType = adLockOptimistic
          .CursorType = adOpenStatic
          .Open strSQL, adoConn
          '만일 레코드가 없다면 BOF(레코드의 시작)과 EOF(레코드의 끝)은 같습니다
           If Not (.BOF And .EOF) Then
              row = 2
              '레코드의 끝이 나올때까지 레코드포인트를 옮깁니다.
               Do Until .EOF
                   'NAME필드의 내용을 1번째 열에 기록합니다.
                    Sheet1.Cells(row, 1) = .Fields("NAME")
                    'ID필드의 내용을 2번째 열에 기록합니다.
                    Sheet1.Cells(row, 2) = .Fields("ID")
                    'PHONE필드의 내용을 3번째 열에 기록합니다.
                    Sheet1.Cells(row, 3) = .Fields("PHONE")
                    '레코드 포인터를 다음으로 옮깁니다.
                     .MoveNext
                    '다음 행으로 기록하기 위해 행번호를 하나 더 늘립니다.
                    row = row + 1
               Loop
           End If
           '레코드셋을 닫습니다.
           .Close
     End With

     Set adoRst = Nothing
     '데이터베이스 연결을 끊습니다.
     ADODisconnect
End Sub

이제 간단한 ADO사용을 해보았습니다. 다음 번에는 중복데이터를 ADO를 사용하여 처리하는 방법을 시작하겠습니다. 프로그램은 아래그림과 같습니다. 미리 살짝 말씀드리면 워크시트의 목록을 하나의 가상테이블로 만들어 이 테이블에서 필터링하여 중복데이터를 처리하는 것입니다.

목차 | 이전 | 다음