Excel VBA - 데이터베이스 연동

Excel

 
 

엑셀 함수 시리즈 #53: VBA 데이터베이스 연동 - 엑셀 데이터 분석의 확장

VBA(Visual Basic for Applications)를 사용하면 엑셀에서 데이터베이스(Access, SQL Server, MySQL 등)와 연동하여 데이터를 가져오고 처리할 수 있습니다. 데이터베이스 연동은 대용량 데이터 처리 및 분석, 데이터베이스 기반 애플리케이션 개발 등 엑셀 데이터 분석의 범위를 확장하는 데 필수적인 기능입니다. 이번 글에서는 VBA 데이터베이스 연동의 기본 개념과 주요 방법, 활용 예시를 알아보겠습니다.

1. 데이터베이스 연동의 필요성

• 대용량 데이터 처리: 엑셀의 데이터 처리 용량 제한을 극복하고 대용량 데이터를 효율적으로 처리합니다.
• 데이터베이스 기반 애플리케이션 개발: 엑셀을 사용자 인터페이스로 활용하여 데이터베이스 기반 애플리케이션을 개발합니다.
• 데이터 통합 및 자동화: 여러 데이터 소스의 데이터를 통합하고 데이터 처리 과정을 자동화합니다.

2. 주요 연동 방법

1. ADO (ActiveX Data Objects): 다양한 데이터베이스(Access, SQL Server, MySQL 등)와 연동하여 데이터를 가져오고 처리합니다.
2. ODBC (Open Database Connectivity): ODBC 드라이버를 사용하여 다양한 데이터베이스와 연동합니다.

3. ADO를 이용한 데이터베이스 연동 예시

1. Access 데이터베이스 연동: Access 데이터베이스에서 데이터를 가져와 엑셀 워크시트에 입력합니다.

VBA

 

Sub AccessDatabaseConnection()
    Dim conn As Object
    Dim rs As Object
    Dim sql As String

    Set conn = CreateObject("ADODB.Connection")
    conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Database\Data.accdb;" ' Access 데이터베이스 연결
    Set rs = CreateObject("ADODB.Recordset")
    sql = "SELECT * FROM Table1"
    rs.Open sql, conn

    Sheets("Sheet1").Range("A1").CopyFromRecordset rs ' 워크시트에 데이터 복사

    rs.Close
    conn.Close
    Set rs = Nothing
    Set conn = Nothing
End Sub

1. SQL Server 데이터베이스 연동: SQL Server 데이터베이스에서 데이터를 가져와 엑셀 워크시트에 입력합니다.

VBA

 

Sub SQLServerDatabaseConnection()
    Dim conn As Object
    Dim rs As Object
    Dim sql As String

    Set conn = CreateObject("ADODB.Connection")
    conn.Open "Provider=SQLOLEDB;Server=서버이름;Database=데이터베이스이름;Uid=사용자ID;Pwd=비밀번호;" ' SQL Server 데이터베이스 연결
    Set rs = CreateObject("ADODB.Recordset")
    sql = "SELECT * FROM Table1"
    rs.Open sql, conn

    Sheets("Sheet1").Range("A1").CopyFromRecordset rs ' 워크시트에 데이터 복사

    rs.Close
    conn.Close
    Set rs = Nothing
    Set conn = Nothing
End Sub

4. ODBC를 이용한 데이터베이스 연동 예시

1. ODBC 데이터 원본 설정: 제어판에서 ODBC 데이터 원본을 설정합니다.
2. VBA 코드 작성: ODBC 데이터 원본을 사용하여 데이터베이스에 연결하고 데이터를 가져옵니다.

VBA

 

Sub ODBCDatabaseConnection()
    Dim conn As Object
    Dim rs As Object
    Dim sql As String

    Set conn = CreateObject("ADODB.Connection")
    conn.Open "DSN=ODBC데이터원본이름;Uid=사용자ID;Pwd=비밀번호;" ' ODBC 데이터 원본 연결
    Set rs = CreateObject("ADODB.Recordset")
    sql = "SELECT * FROM Table1"
    rs.Open sql, conn

    Sheets("Sheet1").Range("A1").CopyFromRecordset rs ' 워크시트에 데이터 복사

    rs.Close
    conn.Close
    Set rs = Nothing
    Set conn = Nothing
End Sub

5. 데이터베이스 연동 시 주의 사항

• 데이터베이스 연결 문자열을 정확하게 입력해야 합니다.
• 데이터베이스 사용자 ID 및 비밀번호를 안전하게 관리해야 합니다.
• 데이터베이스 연동 코드를 작성하기 전에 충분한 테스트를 거쳐 오류를 최소화해야 합니다.

6. 마무리

VBA 데이터베이스 연동을 통해 엑셀 데이터 분석의 범위를 확장하고 데이터베이스 기반 애플리케이션을 개발할 수 있습니다. 다양한 데이터베이스 연동 방법을 활용하여 대용량 데이터 처리 및 분석 능력을 향상시키세요.