5강 – VBA로 엑셀Sheet에 연결하기(ADO를 이용하여 Data추가)

4강에서는 엑셀Sheet에서 ADO를 이용하여 Data를 조회하고 수정하는 작업을 해 보았다.

이번 강에서는 Data를 엑셀Sheet에 추가하는 기능을 구현해 보고자 한다.

그 전에 엑셀을 Database처럼 사용할때의 제약사항을 살펴보기로 하자. 엑셀을 Database처럼 사용할 수 있지만, RDBMS(관계형Database Management System)가 제공하는 기능을 다 이용할 수는 없다.

다음과 같은 몇 가지 제약이 있다.

1) Primary key를 설정할 수 없다. 따라서 Insert전에 중복check를 해야 한다.
2) Foreign key, consraints 등도 쓸수 없다.
3) Delete SQL문을 쓸 수없다. 지원하지 않음
4) Excel sheet는 칼럼의 data type을 지정할 수 없기 때문에 숫자 필드에 text값이 들어갈 수 있다.  data type issue는 다음 링크를 참고한다.
http://stackoverflow.com/questions/34667917/inserting-into-empty-excel-tables-with-sql-without-losing-data-type-property

위의 제약사항을 염두에 두고 다음과 같이 해보자.

 

1. Form필드 초기화

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

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. 저장 및 추가 기능 구현

사용자가 추가 버튼을 누르고 textbox를 초기화한 후 추가할 자료를 입력하고 난 후 "저장"버튼을 누면 자료가 새로이 저장되도록 다음 코드를 입력한다. 4강에서는 저장버튼을 누르면 조회된 Data를 저장(Update)만 했는데 이번 강에서는 저장(Update), 추가(Insert) 2가지 기능을 하도록 소스코드를 다음과 같이 수정한다.

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. Data 추가(Insert) Function

Data를 추가(Insert)하는 Function이다. 위의 코드에서 " insertUser(argUser:=argUser)" 부분이다. 강좌 첫부분에서 언급한 엑셀을 Database처럼 사용할 때의 제약을 극복하기 위해 Primary Key중복 check, Data type check가 안되는 것을 cover하기 위한 부분이 추가되었다.

Function insertUser(argUser As User) As JobResult

Dim db As New ADODB.Connection
Dim rs As New ADODB.Recordset '//조회용
Dim rs2 As New ADODB.Recordset '//조회용
Dim strSQL_EMPTY_CHECK As String
Dim strSQL As String
Dim strConn As String
Dim result As JobResult
Dim CalcMode As Long   
    
On Error GoTo ErrHandler
  
    '//Excel을 Database로 사용할때는 Primary key를 설정할 수 없으므로 Insert시 자동으로 중복Key check가 안된다. 따라서 Insert전에 중복인지 아래와 같이 check한다.
    strSQL = "SELECT COUNT(*) AS CNT FROM [사원정보$] WHERE [사번] = " & argUser.id
    strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\" & ActiveWorkbook.Name & ";" & "Extended Properties=Excel 12.0;"
    
    rs.Open strSQL, strConn, adOpenStatic, adLockReadOnly, adCmdText
    
    If rs("CNT") > 0 Then
        result.code = -1
        result.message = "사번이 동일한 자료가 이미 존재합니다."
    Else
        '//Data가 하나도 없을 경우에 처음으로 INSERT하면 Data type이 text로 들어가버림, 이 문제를 해결하기위해 첫번째 행이 INSERT될때는 VBA로 DATA를 넣는다.
        strSQL_EMPTY_CHECK = "SELECT COUNT(*) AS CNT FROM [사원정보$]"
        rs2.Open strSQL_EMPTY_CHECK, strConn, adOpenStatic, adLockReadOnly, adCmdText
        If rs2("CNT") = 0 Then
        
            With Application
                CalcMode = .Calculation
                .Calculation = xlCalculationManual
                .ScreenUpdating = False
            End With
            
            ActiveWorkbook.Sheets("사원정보").Select
            
            If IsNumeric(argUser.deptName) Then
                Cells(2, 1).Value = "'" & argUser.deptName
            Else
                Cells(2, 1).Value = argUser.deptName
            End If
            
            If IsNumeric(argUser.userName) Then
                Cells(2, 2).Value = "'" & argUser.userName
            Else
                Cells(2, 2).Value = argUser.userName
            End If

            Cells(2, 3).Value = argUser.id
            Cells(2, 4).Value = argUser.salary
            
            With Application
                .ScreenUpdating = True
                .Calculation = CalcMode
            End With

        Else
    
            '//DB open
            db.Open strConn
        
            strSQL = "INSERT INTO [사원정보$] ([부서],[이름],[사번],[급여]) " & _
                     "VALUES ( '" & esc(argUser.deptName) & "','" & _
                                    esc(argUser.userName) & "'," & _
                                    argUser.id & "," & _
                                    argUser.salary & ")"
                                    
            db.Execute CommandText:=strSQL
                
            db.Close
            Set db = Nothing
            
        End If
        
        rs2.Close
        Set rs2 = Nothing
        
        result.code = 0
        result.message = "자료가 추가되었습니다."

    End If
    
    rs.Close
    Set rs = 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

5강은 여기에서 마무리하고 6강에서는 Data를 삭제하는 기능을 구현하고자 한다.

전체코드가 다 설명된 것은 아니므로 상세한 내용은 첨부파일의 소스코드를 참고한다.

 

실습용 파일 다운로드 : 5강_6강_VBA를_이용하여DB에Data추가_삭제_20170616수정.xlsm

사번을 숫자에서 문자열로 변경한 경우의 실습용 파일  : 5강_6강_VBA를_이용하여DB에Data추가_삭제_20170616수정_20200915사번을문자열로변경한케이스.zip

 

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

• 18강 - VBA로 MS SQL Server에 연결하여 Data처리하기(Stored Procedure로 조회)
• 17강 - VBA로 PostgreSQL에 연결하여 Data처리하기(ADO를 이용하여 조회,추가,수정,삭제)
• 16강 - VBA로 MariaDB에 연결하여 Data처리하기(ADO를 이용하여 조회,추가,수정,삭제)
• 15강 - VBA로 MySQL에 연결하여 Data처리하기(ADO를 이용하여 조회,추가,수정,삭제)
• 14강 - VBA로 Oracle DB Server에 연결하여 Data처리하기(ADO를 이용하여 조회,추가,수정,삭제)
• 13강 - VBA로 MS SQL Server에 연결하여 Data처리하기(ADO를 이용하여 조회,추가,수정,삭제)
• 12강 – VBA로 Access DB에 연결하여 Data처리하기(ADO를 이용하여 Data삭제)
• 11강 – VBA로 Access DB에 연결하여 Data처리하기(ADO를 이용하여 Data추가)
• 10강 – VBA로 Access DB에 연결하여 Data처리하기(ADO를 이용하여 Data조회 및 저장)
• 9강 – VBA로 Access DB에 연결하여 Data처리하기(ADO를 이용하여 연결, 조회)
• 8강 - VBA로 엑셀에서 Database 연결 및 처리를 위한 ADO이해
• 7강 – VBA로 엑셀에서 Database 연결, Data처리 기본개념
• 6강 – VBA로 엑셀Sheet에 연결하기(Data삭제-ADO이용안함)
• 5강 – VBA로 엑셀Sheet에 연결하기(ADO를 이용하여 Data추가)
• 4강 - VBA로 엑셀Sheet에 연결하기(ADO를 이용하여 조회 및 저장)
• 3강 - VBA로 엑셀Sheet에 연결하기(ADO를 이용하여 연결, 조회)
• 2강 - Microsoft Query를 이용하여 Database에 연결하기
• 1강 - 들어가기 - Database는 무엇인가, 왜 엑셀에서 Database를 다루는가?