6강 – VBA로 엑셀Sheet에 연결하기(Data삭제-ADO이용안함)

5강에서는 ADO를 이용하여 엑셀Sheet에 Data를 추가하는 작업을 해 보았다. 이번 강좌에서는 엑셀Sheet에서 Data를 삭제하는 기능을 구현해 보고자 한다.

5강에서도 언급했지만, 엑셀을 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. 삭제버튼 코드 추가

위 폼의 “삭제”버튼을 누르면 조회된 Data가 삭제되도록 다음의 코드를  삭제버튼 클릭이벤트에 입력한다.

Private Sub cmdDelete_Click()

Dim argUser As User
Dim result As JobResult
    
    If Me.txtId.Value = "" Then
        MsgBox "조회 후 삭제하세요."
        Exit Sub
    End If
    
    argUser.id = CLng(Me.txtId.Value)
    
    '//삭제처리하고 결과값을 받는다.
    result = deleteUser(argUser:=argUser)

    
    '//처리 후 결과코드로 정상처리 여부를 판단한다.
    If result.code = 0 Then
        MsgBox result.message
    Else
        MsgBox "작업중 에러가 발생했습니다. 아래의 메시지를 확인바랍니다." & vbNewLine & vbNewLine & result.message
    End If
    
    '//처리후 변경된 내용을 조회하여 Form에 반영한다.
    loadUserToForm
    

End Sub

 

2. Data 삭제(Delete) Function

Data를 Delete하는 Function이다. 위의 코드에서 ”deleteUser(argUser:=argUser)” 부분이다. 강좌 첫부분에서 언급한 "엑셀에서 Delete문은 사용할 수 없는 제약"를 극복하기 위해 VBA를 이용하여 Key값에 해당하는 행을 찾아서 직접 삭제하도록 구현하였다. 향후 RDBMS를 사용할때는 다른 로직은 수정할 필요 없고 deleteUser Function에서 SQL을 이용하도록 수정하면 된다(인수 및 리턴값은 동일하게 설계함).

Function deleteUser(argUser As User) As JobResult
'// 엑셀에서는 SQL을 이용하여 Delete를 할 수 없다.
'// Delete를 시도하면 다음과 같이 에러가 발생한다.
'// 이 ISAM에서는 연결된 테이블의 데이터를 삭제할 수 없습니다.
'// 따라서 VBA를 이용하여 Key값에 해당하는 행을 찾아서 직접 삭제한다.
    
Dim result As JobResult
Dim affectedCount As Long
Dim rowCount As Long
Dim i As Long
Dim CalcMode As Long


On Error GoTo ErrHandler

    With Application
        CalcMode = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With


    ActiveWorkbook.Sheets("사원정보").Select
    
    '//사번이 있는 열로 끝행의 위치를 구한다.
    Range("C1").Select
    rowCount = Range(Selection, Selection.End(xlDown)).Rows.Count
    
    '//loop를 돌면서 key값이 일치하는 행이 나오면 행을 삭제한다.
    For i = 2 To rowCount
        If CLng(Cells(i, 3).Value) = argUser.id Then
            Rows(i & ":" & i).Delete shift:=xlUp
            affectedCount = 1
            Exit For
        End If
    Next i
    
    If affectedCount = 0 Then
        result.code = -1
        result.message = "입력한 사번에 해당하는 Data가 없어서 삭제되지 않았습니다."
    Else
        result.code = 0
        result.message = "자료가 삭제되었습니다."
    End If
    
CommonEnd:
    
    deleteUser = result
    
    With Application
        .ScreenUpdating = True
        .Calculation = CalcMode
    End With
    
    
Exit Function

'//에러처리
ErrHandler:

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

 

첨부(실습용 파일) : 5강_6강_VBA를_이용하여DB에Data추가_삭제_20170616수정.xlsm

3강부터 6강까지 엑셀을 Database처럼 사용하여 자료의 조회,저장,추가,삭제하는 기능을 구현해 보았다. 간단한 자료관리 기능을 구현할 때는 괜찮은 방법이지만 여러 사람이 자료를 공유하거나 자료가 복잡한 경우에는 이 방법이 그다지 효율적이지 못하다. 다음 강좌 부터는 MS SQL Server, Oracle, MySQL, Maria DB 등 관계형 DB를 이용하여 자료관리기능을 구현해 보고자 한다.

 

[ 엑셀에서 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를 다루는가?