8강 - VBA로 엑셀에서 Database 연결 및 처리를 위한 ADO이해

7강에서는 엑셀에서 VBA를 이용하여 DB연결 및 Data처리를 위해 이해해야 할 기본개념과 연결방법을 알아 보았고 ADO에 대해서도 간단히 언급했는데 이번 강에서는 ADO를 좀 더 구체적으로 설명한다.

DB연결 및 처리를 위한 ADO이해

Microsoft의 ADO Introduction(https://msdn.microsoft.com/en-us/library/ms678262(v=vs.85).aspx)에 보면 ADO를 다음과 같이 설명해 놓았다.

ADO(ActiveX Data Objects)는 OLE DB에 대한 높은 수준의 사용하기 쉬운 인터페이스이다. OLE DB는 다양한 데이터 저장소에 대한 낮은 수준의 고성능 인터페이스이다. ADO와 OLE DB는 모두 관계형 및 비관계형데이터로 작업 할 수 있다. ADO는 클라이언트 또는 중간 계층 응용 프로그램과 하위 수준 OLE DB 인터페이스간에 추상 계층을 제공한다. ADO는 작은 수의 자동화개체(Automation objects)를 사용하여 OLE DB에 대해 간단하고 효율적인 인터페이스를 제공한다. COM 및 OLE DB의 복잡성을 배울 필요 없이 Data에 접속하고자 하는 Visual Basic 및 VBScript와 같은 고급언어의 개발자에게는 ADO Interface는 적합한 선택이 될 수 있다.

* 이번 강의의 대부분의 내용은 Microsoft의 MSDN사이트의 ADO관련 내용을 가져와서 필요한 부분만 다시 엮은 것임을 밝혀둔다.
MS ADO 관련 페이지 : https://msdn.microsoft.com/en-us/library/ms675532(v=vs.85).aspx

다음 그림은 ADO Object Model이다. Connection, Command, Recordset Object가 많이 사용되므로 3개의 Object위주로 설명한다.

ADO를 Excel VBA에서 사용하기 위해서는 우선 아래와 같이 ADO Library를 참조해야 한다.

1) 새 Visual Basic 프로젝트를 만들거나 기존 Visual Basic 프로젝트를 연다(Excel에서 Alt+F11).

2) 사용 가능한 참조에서 Microsoft ActiveX Data Objects n.n Library에 대한 상자를 선택. n.n은 최신 버전 번호를 나타낸다.

3) 확인을 클릭하여 참조 설정을 완료. 이제 VBA에서 ADO를 사용할 수 있다.

ADO Object

1) Connection Object

Connection object는 데이터 소스에 대한 열린 연결을 담당한다. Connection object는 클라이언트 / 서버 데이터베이스 시스템에서는 서버에 대한 실제 네트워크 연결과 동일 할 수 있다. 공급자가 지원하는 기능에 따라 Connection object의 일부 컬렉션, 메서드, 속성을 사용하지 못할 수 있다.

Connection개체의 Property(속성)

이름

설명

상세내용, Sample code 등

Attributes

Connection 객체의 트랜잭션 속성을 지정한다.

CommandTimeout

명령을 실행하는 동안 대기하는 시간을 나타낸다. 대기하는 시간을 초단위로 설정하며 기본값은 30이다.

ConnectionString

데이터 소스에 연결하는 데 사용되는 문자열정보를 나타낸다.

ConnectionTimeout

DB접속시 대기하는 시간을 말한다. 대기하는 시간을 초단위로 설정하며 기본값은 15이다

CursorLocation

커서의 위치를 나타낸다.

Constant	Value	Description
adUseClient	3	로컬 커서 라이브러리가 제공하는 클라이언트 측 커서를 사용
adUseServer	2	기본값. 데이터 공급자 또는 드라이버가 제공 한 커서를 사용.

DefaultDatabase

Connection 객체의 기본 데이터베이스를 나타낸다..

IsolationLevel

Connection 객체의 isolation level

Mode

Connection,Record,Stream object 에서 데이터를 수정할 수 있는 사용권한을 지정할 때 사용.

Provider

Connection object의 공급자 이름

State

현재의 Connection의 상태

Version

ADO version number.

Connection개체의 collections

이름	설명	상세내용, Sample code 등
Errors	단일공급자 관련 오류에 대한 응답으로 만들어진 모든 Error 객체를 포함한다
Properties	개체의 특정 인스턴스에 대한 모든 Property 개체를 포함한다.

Connection개체의 Methods

이름	설명	상세내용, Sample code 등
BeginTrans	새로운 트랜젝션을 시작한다.	object.BeginTrans
CommitTrans	변경 사항을 저장하고 현재 트랜잭션을 종료한다..	object.CommitTrans
RollbackTrans	현재 트랜잭션 동안의 변경된 내용을 취소하고 트랜잭션을 종료한다	object.RollbackTrans
Open	데이터 소스에 대한 연결을 연다.	connection.Open ConnectionString, UserID, Password, Options
Close	열려있는 객체 및 종속 객체를 닫는다.	object.Close
Execute	지정된 쿼리, SQL문, Stored procedure(저장프로시저)를 실행한다.	Set recordset = connection.Execute (CommandText, RecordsAffected, Options)

2) Command Object

Data source에 실행하려는 Command(SQL등 실행가능한 텍스트)를 정의한다. Command 개체를 사용하여 데이터베이스를 쿼리하고 Recordset 개체의 레코드를 반환하거나 대량 작업을 실행하거나 데이터베이스의 구조를 조작할 수 있다. 공급자의 기능에 따라 일부 명령 컬렉션, 메서드 또는 속성은 참조 될 때 오류가 발생할 수 있다.

Property

이름	설명	상세내용, Sample code 등
ActiveConnection	Command, Recordset 또는 Record 개체가 현재 속해 있는 Connection
CommandStream	Command 객체의 입력으로 사용되는 스트림
CommandText	실행될 명령
CommandTimeout	명령을 실행하는 동안 대기하는 시간, 기본값은 30초
CommandType	Command 개체의 유형
Name	개체의 이름
Parameters	Command 개체의 모든 Parameter(Collection)
Prepared	실행 전에 명령의 컴파일 된 버전을 저장할지 여부(true또는 false로 지정)

Methods

이름	설명	상세내용, Sample code 등
Cancel	보류중인 비동기메서드를 취소한다..	object.Cancel
CreateParameter	새 Parameter 객체를 만든다.	Set parameter = command.CreateParameter (Name, Type, Direction, Size, Value)
Execute	Command 개체의 CommandText 또는 CommandStream 속성에 지정된 쿼리, SQL문이나 저장프로시저를 실행한다.	Set recordset = command.Execute( RecordsAffected, Parameters, Options )

3) Recordset Object

Recordset개체는 기본 테이블의 레코드 세트 전체 또는 실행된 명령의 결과를 나타낸다. Recordset개체를 사용하여 공급자의 데이터를 다룰 수 있다. 모든 Recordset 개체는 레코드(행)와 필드(열)로 구성된다. 공급자가 지원하는 기능에 따라 일부 Recordset 메서드나 속성을 사용하지 못할 수도 있다.

Property

이름

설명

상세내용, Sample code 등

AbsolutePage

현재의 레코드가 어느 페이지에 있는 지를 나타낸다.

AbsolutePosition

Recordset 개체의 현재 레코드의 위치를 나타낸다.

ActiveCommand

연결된 Recordset 개체를 만든 Command 개체를 나타낸다.

ActiveConnection

Command, Recordset 또는 Record 개체가 현재 속해 있는 Connection

연결이 닫혀있는 경우 연결에 대한 정의가 포함 된 String 값을 반환하거나 연결이 열려 있으면 현재 Connection 개체가 들어있는 Variant를 반환하거나 설정한다. 기본값은 null 객체 참조이다.

BOF, EOF

BOF는 현재 레코드 위치가 Recordset 개체의 첫 번째 레코드 앞에 있음을 나타낸다.

EOF는 현재 레코드 위치가 Recordset 개체의 마지막 레코드 뒤에 있음을 나타낸다.

Bookmark

현재 레코드의 위치를 설정하거나 반환한다.

CacheSize

Local메모리에 저장할 Recordset개체의 레코드 개수를 나타낸다.

한 번에 몇 개의 레코드를 공급자로부터 로컬 메모리로 가져올 지 제어할 때 CacheSize속성을 사용한다. 예를 들어, CacheSize가 10이면 Recordset 개체를 처음 열면 처음 10 개의 레코드를 로컬 메모리로 가져온다

CursorLocation

커서 서비스의 위치를 나타낸다.

adUseClient - 로컬 커서 라이브러리가 제공하는 클라이언트측 커서를 사용한다.

adUseServer - 기본값. 데이터 공급자 또는 드라이버가 제공한 커서를 사용한다

CursorType

Recordset 개체에 사용된 커서유형을 나타낸다.

CursorTypeEnum

Recordset 개체에 사용되는 커서 유형을 지정한다.

Constant	Value	Description
adOpenDynamic	2	동적 커서를 사용한다. 다중사용자 환경에서 다른 사용자가 추가, 변경,삭제한 사항이 바로 반영되며 공급자가 책갈피를 지원하지 않으면 북마크를 제외하고 Recordset을 통한 모든 유형의 이동이 허용된다.
adOpenForwardOnly	0	기본값. 앞으로만 이동하는 커서를 사용한다.
adOpenKeyset	1	다른 사용자가 변경한 레코드는 볼 수 있으나, 삭제, 추가한 레코드는 볼 수 없다.
adOpenStatic	3	데이터를 찾거나 보고서를 생성하는 데 사용할 수 있는 레코드 집합의 정적 복사본인 정적커서를 사용한다. 다른 사용자가 추가, 변경, 삭제한 레코드는 볼 수 없다.
adOpenUnspecified	-1	커서 유형을 지정하지 않는다.

DataMember

DataSource 속성이 참조하는 Recordset에서 검색 할 데이터 멤버의 이름을 나타낸다.

DataSource

Recordset 개체로 표시할 데이터가 들어있는 개체를 나타낸다.

EditMode

현재 레코드의 편집 상태를 나타낸다.

Filter

Recordset의 데이터 필터를 나타낸다.

Index

Recordset 개체에 대해 현재 적용되는 인덱스의 이름을 나타낸다.

LockType

편집 중 레코드에 설정된 잠금 유형을 나타낸다.

MaxRecords

쿼리에서 Recordset으로 반환할 최대 레코드 수를 나타낸다.

PageCount

Recordset 개체에 포함된 데이터 페이지수

PageSize

Recordset에서 한 페이지를 구성하는 레코드수

RecordCount

Recordset 개체의 레코드 수

Sort

Recordset를 정렬할 하나 이상의 필드 이름과 각 필드가 오름차순 또는 내림차순으로 정렬되는지 여부

Source Property (ADO Recordset)

Recordset 개체의 데이터 원본

State

모든 적용 가능한 개체에 대해 개체의 상태가 열려 있거나 닫혀 있는지 여부를 나타낸다. 객체가 비동기 메소드를 실행중인 경우 객체의 현재 상태가 연결, 실행 또는 검색 중임을 나타낸다.

Status

일괄업데이트 또는 기타 대량 작업과 관련한 현재 레코드의 상태를 나타낸다.

StayInSync

계층구조 Recordset 개체에서 부모 행 위치가 변경 될 때 기본 자식 레코드에 대한 참조가 변경되는지 여부를 나타낸다.

collections

이름

설명

상세내용, Sample code 등

Fields

Recordset 또는 Record 개체의 모든 Field 개체를 포함한다.

Recordset 개체에는 Field 개체로 구성된 Fields 컬렉션이 있다. 각 Field 개체는 Recordset의 열에 해당한다. Recordset을 열기 전에 컬렉션에서 Refresh 메서드를 호출하여 Fields 컬렉션을 채울 수 있다.

Properties

개체의 특정 인스턴스에 대한 모든 Property개체를 포함한다.

Methods

이름	설명	상세내용, Sample code 등
AddNew	새로운 레코드를 만든다(추가)	create table aa1 (intf int, charf char(10)) insert into aa1 values (2, 'aa') Dim cn As New adodb.Connection Dim rs As New adodb.Recordset Dim cmd As New adodb.Command cn.ConnectionString = "Provider=SQLOLEDB;Data Source=alexverb2;uid=sa;pwd=foo$bar00;" cn.Open rs.Open "select * from xxx..aa1", cn, adOpenKeyset, adLockOptimistic Dim fieldsArray(1) As Variant fieldsArray(0) = "intf" fieldsArray(1) = "charf" Dim values(1) As Variant values(0) = 4 values(1) = "as" rs.AddNew fieldsArray, values rs.Update
Cancel	보류중인 비동기 메서드 호출의 실행을 취소한다.
CancelBatch	보류중인 일괄 업데이트(batch update)를 취소한다.
CancelUpdate	Update 메서드를 호출하기 전에 Recordset 개체 또는 Record 개체의 Fields 컬렉션의 현재 행이나 새 행에 대한 변경을 취소한다.
Clone	기존 Recordset 개체에서 중복 Recordset 개체를 만든다.
Close	열린 객체 및 종속 객체를 닫는다. Close 메서드를 사용하여 Connection, Record, Recordset 또는 Stream 개체를 닫아 관련 시스템 리소스를 모두 해제한다. 객체를 닫아도 객체가 메모리에서 제거되지는 않는다. 속성 설정을 변경하고 나중에 다시 열 수 있다. 메모리에서 개체를 완전히 제거하려면 개체를 닫은 다음 개체 변수를 Nothing (Visual Basic의 경우)으로 설정한다.
CompareBookmarks	두 개의 북마크를 비교하여 상대 값 표시를 반환한다.
Delete	현재 레코드 또는 레코드 그룹을 삭제한다
Find	지정된 조건을 충족하는 행을 Recordset에서 검색한다. 선택적으로, 검색 방향, 시작 행 및 시작 행으로부터의 오프셋을 지정할 수 있다. 기준이 충족되면 현재 행 위치는 발견된 레코드에 설정된다. 그렇지 않으면 위치는 레코드 집합의 끝 (또는 시작 위치)으로 설정된다.
GetRows	Recordset 개체의 복수의 레코드를 배열로 가져온다.
GetString	Recordset을 문자열로 반환한다.
Move	Recordset 개체에서 현재 레코드의 위치를 이동시킨다.
MoveFirst, MoveLast, MoveNext, and MovePrevious	Recordset 개체의 첫 번째, 마지막, 다음,이전 레코드로 이동	참고 : https://msdn.microsoft.com/en-us/library/ms677527(v=vs.85).aspx
NextRecordset	현재 Recordset 개체를 지우고 다음 Recordset을 반환한다.
Open	Recordset개체의 커서를 연다.
Requery	쿼리를 다시 실행하여 Recordset 개체의 데이터를 업데이트한다.
Resync	Resync 메서드를 사용하여 현재 Recordset의 레코드를 기본 데이터베이스와 다시 동기화한다.
Save	Recordset을 파일 또는 Stream 개체에 저장한다.
Seek	레코드 집합의 인덱스를 검색하여 지정된 값과 일치하는 행을 빠르게 찾고 현재 행 위치를 해당 행으로 변경한다.
Supports	지정된 Recordset 개체가 특정 유형의 기능을 지원하는지 여부를 결정한다.
Update	Recordset 개체의 현재 행이나 Record 개체의 Fields 컬렉션에 대한 변경 내용을 저장한다.
UpdateBatch	모든 보류중인 일괄업데이트(batch update)를 디스크에 기록한다.

이상으로 8강을 마친다.

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

엑셀웍스 책 출간 안내

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

자세히 보기

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