9강 – VBA로 Access DB에 연결하여 Data처리하기(ADO를 이용하여 연결, 조회)

3강부터~6강까지는 다음 표와 같이 VBA로 엑셀Sheet에 연결(Excel을 Database로 사용)해서 Data를 조회,수정,추가,삭제하는 방법을 알아보았다.

강좌	내용
3강	VBA로 엑셀Sheet에 연결하기(ADO를 이용하여 연결, 조회)
4강	VBA로 엑셀Sheet에 연결하여 ADO를 이용하여 조회 및 저장
5강	VBA로 엑셀Sheet에 연결하여 ADO를 이용하여 Data추가
6강	VBA로 엑셀Sheet에 연결하여 Data삭제 - ADO이용안함
9강	VBA로 Access DB에 연결하여 Data처리하기(ADO를 이용하여 연결, 조회), Excel 대신 Access를 Database로 이용하는 부분 이외는 3강과 동일
10강	VBA로 Access DB에 연결하여 Data처리하기(ADO를 이용하여 Data조회 및 저장), Excel 대신 Access를 Database로 이용하는 부분 이외는 4강과 동일
11강	VBA로 Access DB에 연결하여 Data처리하기(ADO를 이용하여 Data추가), Excel 대신 Access를 Database로 이용하는 부분 이외는 5강과 동일
12강	VBA로 Access DB에 연결하여 Data처리하기(ADO를 이용하여 Data삭제), Excel 대신 Access를 Database로 이용하는 부분 이외는 6강과 동일

9강에서는 Excel을 Database로 사용하는 대신 Access DB에 연결하여 ADO를 이용하여 Data를 조회하는 기능을 구현해 보고자 한다. 따라서  Database연결 부분 이외는 3강과 동일한 내용이다.  Access뿐만 아니라 MySQL, MS SQL Server, Oracle등의 Database도 같은 방식으로 이용할 수 있지만 Database System을 설치해야 하는 등 어려운 점이 있다. Access를 이용하면 Database System을 설치할 필요 없이 mdb파일 하나만 폴더에 넣어두고 사용하면 된다.

이번 강좌에서 Access를 Database로 사용하기 위해서는 첨부의 XLWORKS.mdb파일을 엑셀매크로파일과 동일한 폴더에 저장하면 된다. table의 구조를 변경하거나 data를 보거나 수정하려면 MS Office에 포함된 Access프로그램을 설치해야 한다. Access에 관한 자세한 내용은 Googling하거나 서점에 가면 좋은 책이 많으므로 한권 사서 보시길 권한다^^

1. 모듈 삽입

엑셀에서 Alt+F11을 눌러서 VBA모드로 이동한다. 아래 그림과 같이 삽입>모듈을 클릭한다. 모듈을 만들고 Module이라는 이름을 modUser로 변경한다. 변경하지 않아도 되지만 시스템이 기본적으로 만들어주는 이름인 Module, Module1, Module2.. 보다는 기능별로 Module을 나누어서 알아보기 쉬운 이름을 쓰면 개발시 혼란이 줄어드는 잇점이 있다.

 

2. VBA코드 작성

modUser 모듈에 아래의 코드를 붙여 넣는다.

Option Explicit
Sub test()

Dim rs As New ADODB.Recordset
Dim strSQL As String
Dim strConn As String
Dim i As Integer
    
    
    '기존 조회내용 지우기
    Sheets("출력").Select
    Rows("1:1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Delete shift:=xlUp
    
    '조회할SQL을 만들어서 String변수에 넣는다.
    strSQL = "SELECT * FROM [사원정보] " & _
             " WHERE 부서 = '영업팀'"
           
           
    'Excel을 Database로 사용
'    strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\" & ActiveWorkbook.Name & ";" & "Extended Properties=Excel 12.0;"
'    rs.Open strSQL, strConn, adOpenForwardOnly, adLockReadOnly, adCmdText
    
'//Access를 Database로 사용할때  Database연결문자열
    strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\XLWORKS.mdb;"
    rs.Open strSQL, strConn, adOpenStatic, adLockReadOnly, adCmdText
    

    If rs.EOF Then
        MsgBox "조회조건에 해당하는 자료가 없습니다."
    Else
        '타이틀을 표시한다.
        For i = 1 To rs.Fields.Count
          Cells(1, i).Value = rs.Fields(i - 1).Name
        Next
        
        
        With ActiveSheet
            '조회한 결과집합(rs)을 "출력"Sheet의 A2지점을 꼭지점으로 해서 출력한다.
           .Range("A2").CopyFromRecordset rs
        End With
    End If
    
   
    rs.Close
    Set rs = Nothing
    
End Sub

 

3. 코드 컴파일

코드가 제대로 작성되었는지 확인하기 위해서 아래와 같이 컴파일을 한다. 제대로 입력했는데도 오류가 날 것인데... 엑셀에서 데이터베이스 작업을 하기 위해서 라이브러리가 필요한데 이 엑셀파일에 추가가 안되었다는 이야기이다.

 

4. 라이브러리 추가

아래 순서대로 라이브러리를 추가하고 다시 컴파일을 해 보면 에러가 나지 않을 것이다.

 

5. 프로그램 실행

이제 프로그램 코드는 완성되었고 엑셀 Sheet에 버튼을 달아서 완성된 프로그램을 실행해 보자. 먼저 "출력"Sheet를 선택한 후  개발도구 메뉴에서 삽입>양식컨트롤에서 단추를 선택하고 마우스 커서를 엑셀 시트에 놓고 클릭하면 버튼이 생긴다.

버튼이 만들어 질 때 다음과 같은 화면이 나타나면 좀 전에 작성한 프로시저의 이름(test)를 선택한다. 버튼을 누르면 test라는 프로시저(프로그램 기능이라고 생각하면 된다)가 실행되도록 하는 것이다.

* 사용자에 따라서는 "개발도구 메뉴"가 보이지 않을 수도 있다. 이 때는 엑셀 옵션에서 다음과 같이 "개발도구"를 클릭상태로 설정한다(오른쪽 메뉴에서 밑에서 세번째).

 

6. Data출력

사원정보 Sheet에 만들어진 버튼을 클릭하면 "출력"Sheet에 아래와 같이 Data가 출력된다. SQL의 조건대로 Data를 가져온 것이다.

첨부(실습용 파일) : 9강부터12강_VBA를_이용하여DB에Data추가_삭제(Access이용).zip
(압축파일을 풀면 xlsm, mdb파일이 보인다. xlsm파일은 매크로실행파일이고 mdb파일은 Access DB파일이다. mdb파일은 매크로실행파일과 동일한 폴더내에 있어야 한다)

이번 강좌에서는 단순히 Data를 가져오기만 했다. 다음 강좌부터는 VBA를 이용하여 Data 수정,삭제 등의 작업을 해 보기로 한다.

 

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

• 18강 - VBA로 MS SQL Server에 연결하여 Data처리하기(Stored Procedure로 조회)
• 17강 - VBA로 PostgreSQL에 연결하여 Data처리하기(ADO를 이용하여 조회,추가,수정,삭제)
• 16강 - VBA로 MariaDB에 연결하여 Data처리하기(ADO를 이용하여 조회,추가,수정,삭제)
• 15강 - VBA로 MySQL에 연결하여 Data처리하기(ADO를 이용하여 조회,추가,수정,삭제)
• 14강 - VBA로 Oracle DB Server에 연결하여 Data처리하기(ADO를 이용하여 조회,추가,수정,삭제)
• 13강 - VBA로 MS SQL Server에 연결하여 Data처리하기(ADO를 이용하여 조회,추가,수정,삭제)
• 12강 – VBA로 Access DB에 연결하여 Data처리하기(ADO를 이용하여 Data삭제)
• 11강 – VBA로 Access DB에 연결하여 Data처리하기(ADO를 이용하여 Data추가)
• 10강 – VBA로 Access DB에 연결하여 Data처리하기(ADO를 이용하여 Data조회 및 저장)
• 9강 – VBA로 Access DB에 연결하여 Data처리하기(ADO를 이용하여 연결, 조회)
• 8강 - VBA로 엑셀에서 Database 연결 및 처리를 위한 ADO이해
• 7강 – VBA로 엑셀에서 Database 연결, Data처리 기본개념
• 6강 – VBA로 엑셀Sheet에 연결하기(Data삭제-ADO이용안함)
• 5강 – VBA로 엑셀Sheet에 연결하기(ADO를 이용하여 Data추가)
• 4강 - VBA로 엑셀Sheet에 연결하기(ADO를 이용하여 조회 및 저장)
• 3강 - VBA로 엑셀Sheet에 연결하기(ADO를 이용하여 연결, 조회)
• 2강 - Microsoft Query를 이용하여 Database에 연결하기
• 1강 - 들어가기 - Database는 무엇인가, 왜 엑셀에서 Database를 다루는가?