강좌 > 엑셀에서 Database사용하기 > 9강 – VBA로 Access DB에 연결하여 Data처리하기(ADO를 이용하여 연결, 조회)

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을 나누어서 알아보기 쉬운 이름을 쓰면 개발시 혼란이 줄어드는 잇점이 있다.

vba_query01

 

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. 코드 컴파일

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

vba_query02

vba_query03

 

4. 라이브러리 추가

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

vba_query04

vba_query05

 

5. 프로그램 실행

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

vba_query06

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

vba_query08

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

vba_query07

 

6. Data출력

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

vba_query09


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

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

 

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

 

엑셀웍스 책 출간 안내

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

엑셀웍스 책 출간안내  자세히 보기

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

“9강 – VBA로 Access DB에 연결하여 Data처리하기(ADO를 이용하여 연결, 조회)”의 7개의 댓글

  1. 감사합니다

    엑세스 데이터 불러오기가 필요했었는데, 소스 긁어다가 경로정도만 수정하니 문제없이 깔끔하게 실행되네요 며칠 고생할 줄 알았는데 정말 감사합니다 굉장히 큰도움이 되었습니다

  2. 감사합니다

    참, 저는 accdb인데 이것도 문제없이 돌아갑니다!

    씨샵으로 불러와서 작업할 땐 연결부터 문제가 많아서 며칠 고생했는데 엑셀은 깔끔하네요!

  3. 안녕하세요 질문 하나만 올려도 될까요?

    분명 잘 되다가 어느날 갑자기 아래와 같은 오류가 뜹니다 ㅠ
    아무리 구글링 해봐도 해결을 방법을 찾을 수 없네요.

    '3706' 런타임 오류가 발생하였습니다:
    공급자를 찾을 수 없습니다. 올바르게 설치되지 않았을 수 있습니다.

  4. 김익명

    Excel Sheet에 간단히 조회 버튼 클릭시
    영업팀만 필터링 되서 출력되는데
    원래 동작이 조회, 수정 버튼에서 조회 된 내용이 저장 된 후
    그 저장된 내용이 출력시트에 나오는게 맞나요?
    어떤 동작을 하던간에 관계없이 영업팀 3명만 출력됩니다.

    1. 소스코드 보시면 아래와 같이 sql where절에 영업팀만 나오도록 하드코딩되어 있습니다. 샘플로 그렇게 해 놓은 것이구요, 필요한 형태로 수정하시면 됩니다.
      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 부서 = '영업팀'"

  5. 나익명

    안녕하세요~!
    질문 드리고 싶습니다.

    [Excel sheet에 간단히 조회]로 데이터를 불러 온 후에

    동일한 버튼을 생성 후 엑셀상 수정된 내용을 database에 저장하고 싶습니다.
    (form으로 listbox나 listview를 사용 하지 않고...)

    작성해주신 모든 예제가 listbox나 listview로 되어있어

    간단히 조회 후 간단히 저장하는 예제를 추가해주실 수 있을까요?

댓글 남기기

Scroll to Top