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

공개됨 글쓴이 Admin댓글 2개
      

7강에서는 엑셀에서 VBA를 이용하여 DB연결 및 Data처리를 위해 이해해야 할 기본개념과 연결방법을 알아 보았고 ADO에 대해서도 간단히 언급했는데 이번 강에서는 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를 사용할 수 있다.

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사용하기 강좌 목록 ]

 

 

8강 : VBA로 엑셀에서 Database 연결 및 처리를 위한 ADO이해에 1개의 응답

  1. VBA에서(xlsm 파일)에서 [Data] 쉬트의 데이터를 쿼리형태로 집계하려고 하던 중…
    잘 되던 connect와 Recordset 생성이 갑자기 안되면서, 에러 메시지는…

    “-2147467259 외부 데이터베이스 드라이버 ()에서 예기치 않은 오류가 발생하였습니다”

    동일 파일을 타 PC에서 실행하면 잘 됩니다…제 PC의 어떤 설정이 잘못되었는지…
    도무지 인터넷에서도 찾을 길이 없어서…

    • 안녕하세요. 답글이 많이 늦었습니다.
      잘되던 것이 갑자기 안되고, 다른 PC에서 하면 잘 된다면, PC 문제일 가능성이 클것 같군요. -2147467259 라는 오류코드로 인터넷에서 찾아보면 여러 인스턴스에서 같은 MDB파일을 열어서 그렇다는 이야기도 있고.. 다른 이유때문에
      그렇다는 이야기도 있는데… 정확히 무슨 이유인지는 제가 PC를 보지 않아서 잘 모르겠군요.
      도움을 드리지 못해서 죄송합니다. 그럼…

댓글 남기기

이메일은 공개되지 않습니다.