엑셀과 엑세스 DB화 2. Access와 엑셀 연동의 장점, 구체적 방법(ADO란)

1. Access와 엑셀 연동의 장점, 구체적 방법(ADO란)

지난 포스팅에서는 Access란 무엇인지, 연동한 예제에 대해서 간략하게 다뤄봤습니다. (지난 포스팅은 하기 참조)

엑셀과 엑세스 DB화 1. Access 데이터베이스와 엑셀 연결방법 (tistory.com)

엑셀과 엑세스 DB화 1. Access 데이터베이스와 엑셀 연결방법

이번에는 구체적으로 ADO란 엑셀의 VBA라이브러리를 통해 어떻게 엑셀과 Access를 연동하는지와 연동함으로 인해 만들어지는 장점에 대해서 설명드리도록 하겠습니다.

 

2. ADO란 무엇인지? 엑셀에서 어떻게 사용가능한지?

ADO (ActiveX Data Objects)란 데이터 원본에 접근하기 위해 마이크로소프트에서 제작한 컴포넌트 오브젝트 모델 객체들의 모임입니다. 간단하게 말하자면 VBA등 프로그래밍에서 활용가능한 라이브러리의 일종인데요. 이 프로그래밍 라이브러리를 사용하면 엑셀 VBA에서 데이터베이스와 상호 작용할 수 있습니다.

 

실제 사용방법은 하기와 같습니다.

① ADO 라이브러리 참조 설정:

엑셀 VBA에서 ADO를 사용하려면 먼저 ADO 라이브러리를 참조해야 합니다. 하기 이미지를 봐주세요.

새 Visual Basic 프로젝트를 만들거나 기존 Visual Basic 프로젝트를 연 다음 (Excel에서 Alt+F11), 사용 가능한 참조에서 Microsoft ActiveX Data Objects n.n Library에 대한 상자를 선택합니다. 여기서 n.n은 최신 버전 번호를 나타냅니다.

확인을 클릭하여 참조 설정을 완료합니다. 

 

② 엑셀 VBA에서 ADO를 활용하는 방법

상기 작업으로 모든 준비는 완료되었습니다.

VBA 프로젝트의 현재 파일명의 옆에 ADO를 사용하여 데이터베이스에 연결하고 데이터를 조회하고 가공할 수 있습니다.

아래 예시를 통해 ADO를 이용하여 엑셀 시트에 연결하고 데이터를 가져오는 방법을 살펴보겠습니다.

먼저 현재 열려있는 VBA프로젝트 창에서 모듈을 하나 추가해서 하기 코드를 첨부해줍니다. (코드 설명은 하단에 참조)

여기서 SQL쿼리 부분과 StrConn의 Data Source =의 경로부분만 자신에게 맞는 것으로 바꿔주면 DB에서 Data를 가져올 수 있게됩니다.

Sub ADO_Example()
    Dim conn As Object
    Dim rs As Object
    Dim strSQL As String
    Dim strConn As String

    ' 데이터베이스 연결 문자열 설정 (예: Access DB)
    strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Path\To\Your\Database.accdb;"

    ' SQL 쿼리 작성
    strSQL = "SELECT * FROM YourTableName"

    ' ADO 연결 생성
    Set conn = CreateObject("ADODB.Connection")
    conn.Open strConn

    ' Recordset 생성 및 데이터 조회
    Set rs = CreateObject("ADODB.Recordset")
    rs.Open strSQL, conn

    ' 데이터 처리 (예: 엑셀 시트에 출력)
    Sheets("YourSheetName").Range("A1").CopyFromRecordset rs

    ' 연결 닫기
    rs.Close
    conn.Close
End Sub

3. ADO에서 가장 중요한 변동요소 및 코드 설명

ADO는 크게 2가지 사항만 잘 파악하시면 됩니다. 첫번째는 VBA코드에서 연결 문자열(커넥션 스트링이라 부릅니다)이라 불리는 어떤 종류의 DB를 열것인가의 지정과, 두번째로 실제 DB에 연결해서 어떤 Data를 어떻게 조작할 것인가 하는 SQL문의 구성인데요. 각 구성요소의 상세사항은 하기 참조 부탁드립니다.

 

① 커넥션 스트링의 설정에 대해서(상기 코드의 연결 문자열 부분)

ADO (ActiveX Data Objects)에서 커넥션 스트링(Connection String)은 데이터 원본에 연결하기 위한 설정 정보를 담고 있는 문자열입니다. 이 문자열은 데이터베이스 서버, 사용자 인증 정보, 데이터베이스 이름 등을 포함합니다. 간단히 말해, ADO를 통해 데이터베이스와 통신할 때 어떤 데이터베이스에 접속하고 어떤 방식으로 접속할지를 지정하는 역할을 합니다.

커넥션 스트링은 다음과 같은 요소로 구성됩니다:

(1). 데이터 소스(Data Source): 데이터베이스 서버의 주소 또는 파일 경로를 지정합니다. 예를 들면 SQL Server의 경우 Server=myServerAddress 또는 Access 데이터베이스의 경우 Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Path\To\Your\Database.accdb와 같이 설정합니다

(2). 기타 설정(Additional Settings): 데이터베이스에 대한 추가적인 설정을 지정할 수 있습니다. 예를 들면 초기 카탈로그 이름, 암호화 여부, 타임아웃 등이 있습니다.

커넥션 스트링은 데이터베이스 종류에 따라 다르며, 각 데이터 프로바이더(provider)마다 고유한 문법을 따릅니다. 따라서 사용하는 데이터베이스에 맞게 커넥션 스트링을 작성해야 합니다.

예를 들어, SQL Server 데이터베이스에 Windows 인증으로 연결하는 경우 다음과 같은 커넥션 스트링을 사용할 수 있습니다:

"Server=myServerAddress;Database=myDataBase;Integrated Security=true;"

데이터베이스 종류와 요구 사항에 따라 커넥션 스트링을 작성하고 필요한 설정을 추가하여 데이터베이스와 효율적으로 상호 작용할 수 있습니다.

 

② SQL이란 무엇인가

SQL (Structured Query Language)은 관계형 데이터베이스 관리 시스템 (RDBMS)의 데이터를 관리하기 위해 설계된 특수 목적의 프로그래밍 언어입니다. SQL은 데이터베이스에서 정보를 검색하고 조작하는 데 사용됩니다. 이 언어는 데이터 정의, 데이터 조작 및 데이터 제어를 위한 다양한 명령어로 구성됩니다.

주요한 SQL 명령어는 다음과 같습니다:

SELECT: 데이터 조회

UPDATE: 데이터 업데이트

DELETE: 데이터 삭제

INSERT INTO: 새 데이터 삽입

 

이 2가지 요소, 특히 SQL의 사용방법을 명확하게 이해한다면 하기 예제처럼 DB에 있는 Data를 필요한 만큼 엑셀로 가져오거나 반대로 엑셀의 Data를 DB에 넣어서 보관하는 등의 조작을 ADO로 수행할 수 있습니다. 다음 포스팅에서는 SQL에 대해서 구체적으로 예제포함 다뤄보도록 하겠습니다.