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

10강 – VBA로 Access DB에 연결하여 Data처리하기(ADO를 이용하여 Data조회 및 저장)

2021-05-03 / 9 댓글

9강에서는 ADO를 이용하여 Access DB에 연결하고 Data를 엑셀Sheet로 가져와 보았다.

이번 강에서는Access DB에 연결하여 Data를 엑셀Sheet가 아닌 아래 그림과 같이 Form으로 가져와서 수정하고 저장하는 기능을 구현해 보기로 한다.

따라서  Database연결 부분 이외는 4강과 동일한 내용이다. 3~6강에서 설명했지만 Excel을 Database로 사용하면 Data의 입력, 삭제 기능을 개발하는데 어려움이 있다. Access를 이용하면 이런 문제를 해결할 수 있다. Access뿐만 아니라 MySQL, MS SQL Server, Oracle등의 Database를 이용해도 이런 문제를 해결할 수 있지만 Database System을 설치해야 하는 등 어려운 점이 있다. Access를 이용하면 Database System을 설치할 필요 없이 mdb파일 하나만 폴더에 넣어두고 사용하면 된다.

이번 강좌에서 Access를 Database로 사용하기 위해서는 첨부의 XLWORKS.mdb파일을 엑셀매크로파일과 동일한 폴더에 저장하면 된다. table의 구조를 변경하거나 data를 보거나 수정하려면 MS Office에 포함된 Access프로그램을 설치해야 한다.

< Form으로 data를 가져와서 수정하고 저장하는 화면 >

lec04_save01

아래와 같이 따라 해보자.

1. Form만들기

VBA모드에서 Form을 추가하고 아래 그림과 같이 listbox, textbox, command button을 추가한다.

lec04_save02

 

2. 조회버튼 코드 추가

“조회”버튼을 더블클릭하여 다음 코드를 추가한다. 조회버튼을 클릭하면 다음의 코드가 실행된다는 의미이다.

Private Sub cmdUserListInq_Click()

    loadUserToForm
    
End Sub

'//loadUserToForm은 여러군데서 사용되므로 아래와 같이 Procedure로 만든다.
Sub loadUserToForm(Optional ByVal queryKey As Variant)

Dim userArray() As User
Dim recordCount As Integer
Dim listData() As String
Dim i As Integer
Dim c As Control

    '//조회전 이전자료 Clear
    For Each c In Me.Controls
        If TypeName(c) = "TextBox" Then
            c.Value = ""
        End If
        
        If TypeName(c) = "ListBox" Then
            c.Clear
        End If
        
    Next c


    '//사용자가 입력한 조건을 selectUsers function에 넘겨주고 결과를 Array로 받는다.
    userArray = selectUsers(argDeptName:=Me.argTxtDeptName.Value, argUserName:=Me.argTxtUserName.Value)
    
    
    '//조회된 자료가 없으면 Ubound를 이용해서 userArray의 갯수를 check하면 에러가 나므로, 에러 무시하도록 On Error Resume Next를 사용한다.
    '//Ubound이후에 에러가 나면 처리해야 하므로 Ubound밑에서 바로 On Error GoTo 0를 사용한다.
    On Error Resume Next
    recordCount = UBound(userArray)
    On Error GoTo 0
    
    If recordCount = 0 Then
        MsgBox "입력한 조건에 해당하는 Data가 없습니다"
        Exit Sub
    End If
    
    '//리스트에 넣을 배열을 조회된 레코드 갯수만큼의 크기로 초기화한다.
    ReDim listData(0 To recordCount - 1, 0 To 3)
    
    '//조회된 레코드를 배열에 채운다.
    For i = 0 To recordCount - 1
        listData(i, 0) = userArray(i).deptName
        listData(i, 1) = userArray(i).userName
        listData(i, 2) = userArray(i).id
        listData(i, 3) = userArray(i).salary
    Next i
    
    '//배열을 리스트에 넣으면 리스트가 조회된다.
    Me.lstUser.List = listData
    
    '//리스트 조회 후에는 첫번째 항목으로 이동하도록 하고, 수정 및 추가시에서 수정/추가된 항목으로 이동하게 한다.
    If IsMissing(queryKey) Then
        frmUser.lstUser.ListIndex = 0
    Else
        setPositionInList frmUser, "lstUser", CStr(queryKey)
    End If

End Sub

 

3. 저장버튼 코드 추가

“저장”버튼을 더블클릭하여 다음 코드를 추가한다.

Private Sub cmdSave_Click()

Dim argUser As User
Dim result As JobResult
    
    
    '//입력값 Validation 로직 추가 필요(값이 비었는지, 숫자필드에 문자가 들어오는지 등...)
    If checkTextBox(Me.txtId, "사번", True, , "NUMERIC", False) = False Then Exit Sub
    If checkTextBox(Me.txtDeptName, "부서", True, , , True) = False Then Exit Sub
    If checkTextBox(Me.txtUserName, "이름", True, , , True) = False Then Exit Sub
    If checkTextBox(Me.txtSalary, "급여", True, , "NUMERIC", True) = False Then Exit Sub

    
    '//저장할 값을 구조체에 넣는다.
    argUser.deptName = Me.txtDeptName.Value
    argUser.userName = Me.txtUserName
    argUser.id = CLng(Me.txtId.Value)
    argUser.salary = CDbl(Me.txtSalary.Value)
    
    If Me.txtId.Enabled Then '//"추가"버튼을 누를때 Enable=True했기 때문에 Data Insert Mode이다.
        '//insert 후 결과값을 받는다.
        result = insertUser(argUser:=argUser)
    Else
        '//update 후 결과값을 받는다.
        result = updateUser(argUser:=argUser)
    End If
    
    '//처리 후 결과코드로 정상처리 여부를 판단한다.
    If result.code = 0 Then
        MsgBox result.message
    Else
        MsgBox "처리 중 에러가 발생했습니다. 아래의 메시지를 확인바랍니다." & vbNewLine & vbNewLine & result.message
    End If
    
    
    '//처리 후 변경된 내용을 조회하여 Form에 반영한다.
    loadUserToForm queryKey:=argUser.id
        

End Sub

 

4. listbox 코드 추가

Form의 listbox 더블클릭하여 다음 코드를 추가한다. listbox를 클릭하면 아래 코드가 실행된다는 의미


Private Sub lstUser_Click()

    With Me
        .txtId.Enabled = False
        .txtId.BackColor = H8000000F
    
        .txtDeptName = .lstUser.Column(0)
        .txtUserName = .lstUser.Column(1)
        .txtId = .lstUser.Column(2)
        .txtSalary = .lstUser.Column(3)
    End With

End Sub

 

5. 모듈에 코드 추가

아래 소스코드를 common Module을 만들고 붙여 넣는다.

Public Const gAccessDBName As String = "XLWORKS.mdb"

'//작업결과를 받기 위해 구조체를 선언한다.
Type JobResult
    code As Long
    message As String
End Type

'사용자정보를 구조체로 선언한다.
Type User
    deptName As String
    userName As String
    id As Long
    salary As Double
    result As JobResult
End Type
'//DB에 값이 들어갈때 홑따옴표가 있으면 안되므로 아래와 같이 처리한다.
Function esc(argTxt As Variant) As String
    esc = Trim(Replace(argTxt, "'", "''"))
End Function

Sub setPositionInList(ByRef argForm As UserForm, ByVal argList As String, ByVal argKey As String)
'//원래의 list로 돌아가도록 처리
Dim i As Long
Dim keyCol As Integer '//list에서 key값의 컬럼위치

    keyCol = argForm.Controls(argList).BoundColumn - 1

    '//listbox에서 지정된 item위치로 이동
    With argForm.Controls(argList)
        For i = 0 To .ListCount - 1
            If CStr(.List(i, keyCol)) = CStr(argKey) Then
               .ListIndex = i
                Exit For
            End If
        Next i
    End With

End Sub

'/***********************************************************************************
' 함수명    : CheckTextBox
' 작성자    : 출처 http://jwoojeong.tistory.com/entry/VBA%EC%97%90%EC%84%9C-control%EC%9D%84-parameter%EB%A1%9C-%EB%84%98%EA%B8%B0%EB%8A%94-%EB%B2%95
' 설명      : TextBox 입력값이 존재하는 지 여부를 판단해 오류가 있을 경우, msgbox를 띄우고 false를 반환
' 리턴값    : [false] 오류 발생
' 매개변수
'           txt     : 대상 TextBox
'           title   : 대상 TextBox 갖고 있는 내용
'
' 사용법 : If common.CheckTextBox(txtBox1, "입력 텍스트 상자 1", True, 8) = False Then Exit Sub
' 이력사항
'           2011.12.20: 생성
'           2016.07.24: https://xlworks.net에서 기능추가(Numeric,Date type check)
'
'/***********************************************************************************
Public Function checkTextBox( _
    ByRef txt As MSForms.TextBox, _
    ByVal title As String, _
    Optional ByVal isEssencial As Boolean = False, _
    Optional ByVal Length As Integer = 1000, _
    Optional ByVal dataType As String = "STRING", _
    Optional ByVal isSetFocus As Boolean = True _
) As Boolean

    Dim checkResult As Boolean
    checkResult = True
   
    If isEssencial = True And (IsNull(txt.Text) Or txt.Text = "") Then
        MsgBox title & "을(를) 입력하세요." & Space(6), 48, "입력 오류"
        If isSetFocus = True Then
            txt.SetFocus
        End If
        checkResult = False
    End If
     
    If (Not IsNull(txt.Text)) And Len(txt.Text) > Length Then
        MsgBox title & "의 입력 최대 길이는 " & CStr(Length) & "를 넘을 수 없습니다." & Space(6), 48, "입력 오류"
        If isSetFocus = True Then
            txt.SetFocus
        End If
        checkResult = False
    End If
    
    '//xlworks.net에서 기능추가
    If (dataType = "NUMERIC") Then
        If Not IsNumeric(txt.Text) Then
            MsgBox title & "에는 숫자만 입력할 수 있습니다." & Space(6), 48, "입력 오류"
            If isSetFocus = True Then
                txt.SetFocus
            End If
            checkResult = False
        End If
    Else
        If (dataType = "DATE") Then
            If Not IsDate(txt.Text) Then
                MsgBox title & "에는 날짜만 입력할 수 있습니다." & vbNewLine & "YYYY-MM-DD 형태로 입력하세요." & Space(6), 48, "입력 오류"
                If isSetFocus = True Then
                    txt.SetFocus
                End If
                checkResult = False
            End If
        End If
    End If
     
    checkTextBox = checkResult

End Function

 

6. Data 조회(Select) Function

아래는 Data를 가져오는 부분이다. modUser Module을 만들고 붙여 넣는다.

Public Function selectUsers(ByVal argDeptName As String, ByVal argUserName As String) As User()
    
Dim rs As New ADODB.Recordset
Dim strSQL As String
Dim strConn As String
Dim i As Integer
Dim j As String
Dim userArray() As User
'//조회조건을 설정할 문자열 변수
Dim strDeptName As String
Dim strUserName As String


On Error GoTo ErrHandler
    
    '// 조회조건 설정 - 입력된 값이 있을 때만 SQL문의 조건절을 만든다.
    If argDeptName = "" Then
        strDeptName = ""
    Else
        strDeptName = " AND [부서] LIKE '%" & esc(argDeptName) & "%'"
    End If
    
    If argUserName = "" Then
        strUserName = ""
    Else
        strUserName = " AND [이름] LIKE '%" & esc(argUserName) & "%'"
    End If
    
'//Excel을 Database로 사용할때 SQL문
'    strSQL = "SELECT [부서],[이름],[사번],[급여]" & _
'           "  FROM [사원정보$] " & _
'           " WHERE [사번] > 0 " & strDeptName & strUserName


'//Access를 Database로 사용할때 SQL문
    '//위의 SQL을 아래와 같이 바꾼다. Excel sheet를 table로 이용하는 대신 Access를 이용하여 "사원정보" table을 만들었으므로 [사원정보$]라는 이름에서 "$"만 빼면 된다.
    strSQL = "SELECT [부서],[이름],[사번],[급여]" & _
           "  FROM [사원정보] " & _
           " WHERE [사번] > 0 " & strDeptName & strUserName
           
    
'//Excel을 Database로 사용할때 Database연결문자열
'    strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\" & ActiveWorkbook.Name & ";" & "Extended Properties=Excel 12.0;"
  
'//Access를 Database로 사용할때  Database연결문자열, Access DB명은 common module의 global변수 "gAccessDBName"에 지정한다.
    strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\" & gAccessDBName & ";"
    
    '//Database연결 및 Recoredset Open
    rs.Open strSQL, strConn, adOpenStatic, adLockReadOnly, adCmdText
  
    
    If Not rs.EOF Then '//자료가 존재하면 loop를 돌면서 userArray에 결과를 넣는다.
        ReDim userArray(CLng(rs.recordCount))
            
        i = 0

        Do Until (rs.EOF = True)
            
            userArray(i).deptName = rs("부서").Value
            userArray(i).userName = rs("이름").Value
            userArray(i).id = rs("사번").Value
            userArray(i).salary = IIf(IsNull(rs("급여").Value), 0, rs("급여").Value)
            
            rs.MoveNext
            i = i + 1
        Loop
    End If
        
    rs.Close
    Set rs = Nothing

    selectUsers = userArray
    
    
Exit Function

'//에러처리
ErrHandler:

    If (Err.Number <> 0) Then
        ReDim userArray(0)
        userArray(0).result.code = Err.Number
        userArray(0).result.message = Err.Description
        selectUsers = userArray
    End If
    
End Function

 

7. Data 저장(Update) Function

Data를 저장하는 Function이다. 저장버튼을 누르면 이 Function이 실행되어 Data가 저장(Update)된다.

Function updateUser(argUser As User) As JobResult
    
Dim db As New ADODB.Connection
Dim strSQL As String
Dim strConn As String
Dim result As JobResult
Dim affectedCount As Long

On Error GoTo ErrHandler

'//Excel을 Database로 사용할때 연결문자열
'    strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\" & ActiveWorkbook.Name & ";" & "Extended Properties=Excel 12.0;"
    
'//Access를 Database로 사용할때  Database연결문자열, Access DB명은 common module의 global변수 "gAccessDBName"에 지정한다.
    strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\" & gAccessDBName & ";"
    
    db.Open strConn
    
    '//Access를 Database로 사용할때 table이름에 "$"를 붙이지 않는다.
    strSQL = "UPDATE [사원정보] " & _
           "   SET [부서] = '" & esc(argUser.deptName) & "'," & _
           "       [이름] = '" & esc(argUser.userName) & "'," & _
           "       [급여] = " & argUser.salary & _
           " WHERE [사번] = " & argUser.id
    
           
    db.Execute CommandText:=strSQL, recordsaffected:=affectedCount
    
    db.Close
    Set db = Nothing
    
    If affectedCount = 0 Then
        result.code = -1
        result.message = "입력한 사번에 해당하는 Data가 없어서 업데이트 되지 않았습니다."
    Else
        If affectedCount > 1 Then
            result.code = -2
            result.message = "사번이 중복된 자료가 " & affectedCount & "개 있습니다."
        Else
            result.code = 0
            result.message = "자료가 수정되었습니다."
        End If
    End If
    
    updateUser = result
    
    
Exit Function

'//에러처리
ErrHandler:

    If (Err.Number <> 0) Then
        result.code = Err.Number
        result.message = Err.Description
        updateUser = result
    End If
    
End Function

 

8. 조회 버튼에 모듈코드 연결

마지막으로 아래 코드를 Module에 붙여 넣고 Excel sheet위에 추가한 “조회”버튼에 이 매크로가 실행되도록 연결한다.

Public Sub callFrmUser()

    frmUser.Show
        
End Sub

위의 내용만으로 완전한 기능을 구현할 수 없다. Form에 추가된 textbox등은 이름이 위의 코드와 일치해야하고, 또 위에 언급되지 않은 부분이 있을 수 있으므로 자세한 내용은 첨부를 다운로드 받아서 확인하자.

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

 

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

 

 

엑셀웍스 책 출간 안내

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

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

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

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

  1. 박군

    정말 잘 배우고 있습니다. 감사드립니다.
    그런데, 혹시 엑셀 vba 를 통해서 accdb 레코드에 첨부파일을 넣을수는 없을까요?
    예를들면, 이 예시에서 각 사원별로 입사 서류를 제출했을때
    어떤 사원은 이력서만, 어떤 사원은 이력서와 영어성적서 사본 등이 있을 수 있는데,
    각 레코드 별로 첨부파일의 개수는 정해지지 않았지만, 필요시 첨부할 수 있었으면 좋겠는데,
    가능할까요? 액세스 컬럼중에 첨부파일 컬럼이 있는것으로 아는데,
    vba 상에서 어떻게 구현해야 할 지를 모르겠네요 ㅠㅠ

    답글
  3. 이군

    위의 그림처럼 부서별 검색이 안되는데("영업팀")
    무엇이 잘못 된것일까요?

    덕분에 공부하는데 많은 도움이 되고 있습니다.
    감사합니다.

    답글
  4. 이군

    잘못된 부분을 찾아냈습니다.
    frmUser 폼 loadUserToForm 코딩중
    For Each c In Me.Controls
    If TypeName(c) = "TextBox" Then
    c.Value = "" '<- 검색 이전에 초기화됨
    End If

    If TypeName(c) = "ListBox" Then
    c.Clear
    End If

    Next c

    수정하면
    ' If TypeName(c) = "TextBox" Then
    ' c.Value = ""
    ' End If
    이 부분을 삭제하고
    For Each c In Me.Controls
    If TypeName(c) = "ListBox" Then
    c.Clear
    End If
    Next c

    하거나
    If TypeName(c) = "ListBox" Then
    c.Clear
    End If
    이부분만 남겨 놓으면 될것 같군요.

    앞으로도 좋은 강의 잘 부탁드립니다.

    답글
    1. Admin

      안녕하세요. 올려 놓은 소스에 버그가 있었군요. 찾아 주셔서 감사합니다^^ 수정해서 올려 놓겠습니다.

      답글
  5. 송재윤

    질문드려도 될까요??

    dim userarray as user 변수선언할때 user라는 형식은 엑세스에만 존재하는건가요??

    처음보는거라서,,

    setPositionInList frmUser, "lstUser", CStr(queryKey) setpositioninlist 요 명령문도

    엑세스에만 존재하는지 구분 해석좀 부탁드립니다..db공부하는데 많은도움되었습니다

    감사합니다

    답글
    1. Admin

      안녕하세요. user는 사용자정의자료형입니다. Common모듈에 다음과 같이 정의되어 있습니다.
      Type User
      deptName As String
      userName As String
      id As Long
      salary As Double
      result As JobResult
      End Type

      setPositionInList 는 Common모듈에 정의된 Procedure입니다. list에서 조회 후 원래의 위치로 돌아가도록 합니다.

      감사합니다.

      답글
  6. 익명

    여기 코드를 보고 많이 배우고 갑니다. 고맙습니다.
    type 사용자 정의도 예시를 보니 왜 사용하는지 알겠습니다.
    최고!!

    답글

댓글 남기기

Scroll to Top