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

공개됨 글쓴이 Admin댓글 4개

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 = &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
   
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사용하기 강좌 목록 ]

 

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

  1. Function insertUser(argUser As User) 부분에 recordsaffected 을 이용해
    추가,삭제가 몇개가 되었는지 알려면 어떻게 해야할까요?
    답변 주시면 감사하겠습니다.

    • 안녕하세요. 본문의 소스에 보시면 affectedCount라는 변수가 있습니다. 이 변수에 처리(추가,삭제 등)된 갯수가 저장됩니다.
      감사합니다.

      • affectedCount(recordsaffected ) 변수에 레코드 행의 추가나 삭제된 갯수가 저장 된다는
        이론은 알겠는데 몇개가 추가 되었는지 msgbox 에 출력을 하고 싶습니다.
        위 본문의 어느 부분을 수정해야 할까요?
        저의 막연한 생각으로는 먼저 현재 레코드수를 파악해야 나중에 추가된 레코드 갯수가
        변수에 저장될것 같은데…

댓글 남기기

이메일은 공개되지 않습니다.