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

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 = &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   교보문고   알라딘 

“11강 – VBA로 Access DB에 연결하여 Data처리하기(ADO를 이용하여 Data추가)”의 6개의 댓글

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

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

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

  2. 안녕하세요.

    올려주신 파일에서 사번이 숫자가 아닌 문자열이 들어가도록 코드를 수정해보았는데,
    (5강의 사번을 문자열로 바꾸는 예제 파일을 참고하였습니다)
    사번과 급여를 입력하면 '쿼리식의 연산자가 없는 구문 오류입니다'라는 에러가 발생합니다.

    모듈의 Function 중 InsertUser, AddUser의 strSQL문에서 오류가 발생하는 것으로 추정하고 있습니다.

    혹시 답변을 남겨주실까하여 이렇게 댓글을 남깁니다.

    * 오류 찾는데 참고가 될까하여 수정한 코드도 같이 남깁니다.
    [Function InsertUser]
    strSQL = "INSERT INTO [사원정보] ([부서],[이름],[사번],[급여]) " & _
    "VALUES ('" & esc(argUser.deptName) & "','" & _
    esc(argUser.userName) & "'," & _
    esc(argUser.id) & "'," & _
    argUser.salary & "')"

    [Function UpdateUser]
    strSQL = "UPDATE [사원정보] " & _
    " SET [부서] = '" & esc(argUser.deptName) & "'," & _
    " [이름] = '" & esc(argUser.userName) & "'," & _
    " [급여] = " & argUser.salary& _
    " WHERE [사번] = '" & esc(argUser.id) & "'"

    1. 다음 코드를 사용해보세요. 홑따옴표의 위치가 일부 틀렸네요.
      만약에 안되시면 파일을 admin@xlworks.net으로 보내주시면 확인해 드리겠습니다.

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

      [Function UpdateUser]
      strSQL = "UPDATE [사원정보] " & _
      " SET [부서] = '" & esc(argUser.deptName) & "'," & _
      " [이름] = '" & esc(argUser.userName) & "'," & _
      " [급여] = " & argUser.salary & _
      " WHERE [사번] = '" & esc(argUser.id) & "'"

댓글 남기기

Scroll to Top