11강 – VBA로 Access DB에 연결하여 Data처리하기(ADO를 이용하여 Data추가)

11강에서는 VBA로 Access DB에 연결하여 Data를 추가하는 작업을 해 보고자 한다.

5강에서는 Excel의 Sheet를 Database처럼 사용하여 Data를 추가해 보았는데 이번 강에는 Excel Sheet대신에 Access를 Database로 사용하여 아래와 같이 구현해 본다. 따라서 DB연결 및 Data추가 부분을 제외하고는 5강과 내용이 거의 동일하다.

1. 폼 필드 초기화

Data를 새로이 추가하기 위해서 폼의 필드를 초기화해야 한다. 그래야 새로이 Data를 입력할 수 있다. 위 폼의 "추가"버튼을 누르면 필드가 초기화 되도록 "추가"버튼의 Click Event에 다음의 코드를 입력한다.

Private Sub cmdAdd_Click()
    
    With Me
        .txtId.Enabled = True
        .txtId.BackColor = &amp;HFFFFFF
        
        .txtId.Value = ""
        .txtUserName.Value = ""
        .txtDeptName.Value = ""
        .txtSalary.Value = ""
    End With
    
End Sub

2. Data입력값 Validation 및 추가(Insert)를 위한 처리

사용자가 추가 버튼을 누르고 textbox를 초기화한 후 추가할 자료를 입력하고 난 후 "저장"버튼을 누르면 자료가 새로이 추가되도록 "저장"버튼의 Click Event에 다음 코드를 입력한다. Data추가(Insert) 뿐만 아니라 Data를 저장(Update)하는 기능도 구현이 되어 있다. "추가"버튼을 클릭했는지를 check하여 Data를 추가할 지 저장할 지 결정하는 방식으로 구현이 되어 있다.

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

3. Access DB에 Data추가(Insert)하기

Data를 추가하는 Function이다. 저장버튼을 누르면 이 Function이 실행되어 Data가 추가(Insert)된다. modUser module에 붙여 넣는다.

Function insertUser(argUser As User) As JobResult

Dim dbConn As New ADODB.Connection
Dim strSQL_EMPTY_CHECK As String
Dim strSQL As String
Dim strConn As String
Dim result As JobResult
Dim affectedCount As Long
   
'//Access를 Database로 사용할때는 Primary key사용가능하고
'//Data가 하나도 없을 경우에 처음으로 INSERT하면 Data type이 text로 들어가버리는 문제가 없으므로 기존로직은 삭제한다
'//기존 로직이 궁금하면 6강의 실습파일 참고바람.

On Error GoTo ErrHandler

    strSQL = "INSERT INTO [사원정보] ([부서],[이름],[사번],[급여]) " & _
             "VALUES ( '" & esc(argUser.deptName) & "','" & _
                            esc(argUser.userName) & "'," & _
                            argUser.id & "," & _
                            argUser.salary & ")"
                                
'MsgBox strSQL

'//Access를 Database로 사용할때  Database연결문자열, Access DB명은 common module의 global변수 "gAccessDBName"에 지정한다.
    strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\" & gAccessDBName & ";"
    dbConn.Open strConn
    dbConn.Execute CommandText:=strSQL, recordsaffected:=affectedCount
    
    If affectedCount = 0 Then
        result.code = -1
        result.message = "입력한 자료에 해당하는 Data가 없어서 추가되지 않았습니다."
    Else
        result.code = 0
        result.message = "처리되었습니다."
    End If
    
    
    dbConn.Close
    Set dbConn = Nothing

CommonEnd:
    
    insertUser = result
    
   
Exit Function

'//에러처리
ErrHandler:

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

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

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

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

엑셀웍스 책 출간 안내

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

자세히 보기

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