13강에서는 VBA로 Microsoft SQL Server DB에 연결하여 Data를 조회,추가,수정,삭제하는 작업을 해 보고자 한다.
9강부터 12강까지는 Microsoft의 Access를 Database로 사용하여 Excel에서 Data를 조회,추가,수정,삭제하는 작업을 해 보았는데 이번 강에서는 Access대신 같은 회사의 제품인 SQL Server를 이용하여 Excel에서 Data를 조회,추가,수정,삭제하는 기능을 구현해 본다. 따라서 DB연결 등 일부를 제외하고는 9강~12강과 내용이 거의 동일하다.
1. 사전에 확인할 사항
SQL Server로 기능을 구현하기 위해서는 다음 사항을 먼저 확인하기 바란다. 제대로 되어 있지 않다면 다양한 에러가 발생하여 시작부터 좌절할 수 있으니.... 미리 확인을 해 두기를 바란다.
- VBA에서 ADO를 사용하도록 설정 : ADO와 관련된 에러가 나면("사용자 정의형식이 정의되더 있지 않습니다"라고 난다) 3강을 참고하여 ADO(Microsoft ActiveX Data Objects 6.1 Library - 6.1은 버전인데 6.1이 버전이 없을 수도 있다. 없다면 낮은 버전을 선택해도 대부분 문제없이 작동한다)가 참조되도록 설정한다.
- ODBC확인 - ODBC에 SQL Server가 있어야 한다. Windows OS를 잘못 설치하지 않았다면 당연히 있을 것이다. 다음 그림과 같이 있는 지 확인해 보자. 제어판>관리도구>데이터원본(ODBC) 를 선택하여 시스템 DSN에서 추가 버튼을 눌러보면 확인할 수 있다. Window OS 버전에 따라 찾는 방법이 약간 다를 수 있다. 아래 그림에서 "SQL Server"라고 되어 있느데 VBA에서 DB연결문자열 만들때 동일하게 적어주어야 한다.
- SQL Server Port 번호 확인 - SQL Server는 기본 통신 Port가 1433인데 SQL Server 설치 후 별도로 지정하지 않는다면 기본 Port인 1433으로 지정되어 있다. 그런데 보안상의 이유로 시스템 관리자가 Port번호를 변경할 수 있다. 만약에 Port번호가 1433이 아니라 20100이라면 VBA에서 연결 문자열을 만들때 다음과 같이 서버의 IP주소 다음에 comma를 찍고 Port번호를 적어준다. Port번호가 기본 Port인 1433인 경우에는 적어주지 않아도 된다.
conn.ConnectionString = "Driver={SQL Server};Server=192.168.65.132,20100;Database=test_db;uid=sa;pwd=x1234"
2. 간단한 조회기능으로 먼저 개념 익히기
DB의 Data를 조회,추가,수정,삭제 하는 기능을 구현해보기 전에 DB를 연결하고 처리하는 기본 개념을 익히기 위해 다음과 같이 Excel sheet에 Data를 출력하는 간단한 기능을 먼저 구현해 보자.
1) Table 생성
SQL Server가 설치되어 있다고 가정하고 다음의 SQL로 테스트용 Table을 생성하자.
CREATE TABLE users (
deptname varchar(50) NOT NULL,
username varchar(50) NOT NULL,
id int NOT NULL PRIMARY KEY,
salary float NOT NULL
);
조회에 필요한 데이터는 INSERT SQL을 수행하여 입력하거나 SQL Server Management Studio를 이용하여 입력한다.
2) VBA에서 소스입력
VBA Module에 다음의 소스코드를 입력한다. SQL Server에서 Data를 가져와서 Excel sheet에 필드명과 Data를 출력하는 기능이다. 소스코드에 대한 설명은 주석에 달아 놓았다.
'//Microsoft SQL Server에서 Data를 가져와서 Excel sheet에 출력하기
Sub test()
Dim conn As ADODB.Connection
Dim rs As New ADODB.Recordset
Dim strSQL As String
Dim i As Integer
'//엑셀 Sheet에서 기존 조회내용 지우기
Sheets("출력").Select
Rows("1:1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete shift:=xlUp
Set conn = New ADODB.Connection
'//연결문자열을 만든다. 다음과 같이 Drive, Server명을 적고, Database명, DB에 로그인을 위한 Id,Password를 적는다.
'//만약에 Port번호가 1433이 아니라 20100이라면 VBA에서 연결 문자열을 만들때 다음과 같이 서버의 IP주소 다음에 comma를 찍고
'// Port번호를 적어준다. Port번호가 기본 Port인 1433인 경우에는 적어주지 않아도 된다.
'// conn.ConnectionString = "Driver={SQL Server};Server=192.168.65.132,20100;Database=test_db;uid=sa;pwd=x1234"
conn.ConnectionString = "Driver={SQL Server};Server=192.168.65.132;Database=test_db;uid=sa;pwd=x1234"
'//Database Connection을 Open한다.
conn.Open
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
'//조회할SQL을 문자열로 만든다.
strSQL = "SELECT deptname,username,id,salary" & _
" FROM users " & _
" WHERE id > 0 "
'//필드명, 테이블명에 SQL Server용 keyword를 사용한 경우에는 에러가 난다.
'//이 경우 keyword를 다른 이름으로 바꾸거나 bracket []로 감싼다.
'//참고 : https://stackoverflow.com/questions/52898/what-is-the-use-of-the-square-brackets-in-sql-statements
'//Database에서 Data를 가져온다.
rs.Open Source:=strSQL, ActiveConnection:=conn, CursorType:=adOpenForwardOnly, LockType:=adLockReadOnly, Options:=adCmdText
'//Database에서 Data를 가져와서 Data가 있으면 Excel sheet에 출력한다.
If rs.EOF Then
MsgBox "조회조건에 해당하는 자료가 없습니다."
Else
'//users Table의 필드명을 가져와서 Excel sheet에 첫행에 표시한다.
For i = 0 To rs.Fields.Count - 1
Cells(1, i + 1).Value = rs.Fields(i).Name
Next
'//첫번째 레코드로 이동한다.
rs.MoveFirst
'//조회한 결과집합(rs)을 "출력"Sheet의 A2지점을 꼭지점으로 해서 출력한다.
With ActiveSheet
.Range("A2").CopyFromRecordset rs
End With
End If
'//Recordset개체와 Connection개체의 연결을 닫는다.
rs.Close
conn.Close
'//Recordset개체와 Connection개체를 소멸시킨다.
Set rs = Nothing
Set conn = Nothing
End Sub
3. Data 조회,추가,수정,삭제 기능 구현
아래 그림과 같이 VBA Form을 이용하여 DB의 Data를 조회,추가,수정,삭제 하는 기능을 구현해 보자. Excel sheet에 있는 버튼을 클릭하면 Form이 나타나고 조회,저장,추가,삭제 버튼을 클릭했을때 Data를 처리하는 기능이다.
1) 조회기능
Form에서 조회버튼을 누르면 SQL Server Database에서 Data를 가져와서 Form에 조회한다.
Private Sub cmdUserListInq_Click()
'//Form에 있는 조회버튼을 클릭했을 때 실행한다.
'//laodUserToForm procedure를 호출한다. Data를 추가,수정,삭제할때도
'//필요한 기능이므로 procedure를 별도로 만듬.
On Error GoTo ErrHandler
loadUserToForm
'//에러처리
ErrHandler:
If (Err.Number <> 0) Then
MsgBox "에러가 발생하였습니다" & vbNewLine & vbNewLine & "Error Number : " & Err.Number & vbNewLine & "Error Description : " & Err.Description
End If
End Sub
'//-------------------------------------------------------------------
'// Database에서 data를 가져와서 Form에 출력하는 Procedure
Sub loadUserToForm(Optional ByVal queryKey As Variant)
'//----------------------------------------------------------------
'// Database에서 data를 가져와서 Form에 출력하는 Procedure
Dim userArray() As User
Dim recordCount As Integer
Dim listData() As String
Dim i As Integer
Dim c As Control
'//사용자가 입력한 조건을 selectUsers function에 넘겨주고 결과를 Array로 받는다.
userArray = selectUsers(argDeptName:=Me.argTxtDeptName.Value, argUserName:=Me.argTxtUserName.Value)
'//조회된 자료가 없으면 Ubound를 이용해서 userArray의 갯수를 check하면 에러가 나므로, 에러 무시하도록 On Error Resume Next를 사용한다.
'//Ubound이후에 에러가 나면 처리해야 하므로 Ubound밑에서 바로 On Error GoTo 0를 사용한다.
On Error Resume Next
recordCount = UBound(userArray)
On Error GoTo 0
If recordCount = 0 Then
MsgBox "입력한 조건에 해당하는 Data가 없습니다"
Exit Sub
End If
'//리스트에 넣을 배열을 조회된 레코드 갯수만큼의 크기로 초기화한다.
ReDim listData(0 To recordCount - 1, 0 To 3)
'//조회된 레코드를 배열에 채운다.
For i = 0 To recordCount - 1
listData(i, 0) = userArray(i).deptName
listData(i, 1) = userArray(i).userName
listData(i, 2) = userArray(i).id
listData(i, 3) = userArray(i).salary
Next i
'//배열을 리스트에 넣으면 리스트가 조회된다.
Me.lstUser.List = listData
'//리스트 조회 후에는 첫번째 항목으로 이동하도록 하고, 수정 및 추가시에서 수정/추가된 항목으로 이동하게 한다.
If IsMissing(queryKey) Then
frmUser.lstUser.ListIndex = 0
Else
setPositionInList frmUser, "lstUser", CStr(queryKey)
End If
End Sub
'//-------------------------------------------------------------------
Public Function selectUsers(ByVal argDeptName As String, ByVal argUserName As String) As User()
'//Microsoft SQL Server에서 Data를 가져와서 구조체(User Type, Common module에 정의되어 있음)형태로 반환하기
Dim conn As ADODB.Connection
Dim rs As New ADODB.Recordset
Dim strSQL As String
Dim i As Integer
Dim j As String
Dim userArray() As User
'//조회조건을 설정할 문자열 변수
Dim strDeptName As String
Dim strUserName As String
Set conn = New ADODB.Connection
'//연결문자열을 만든다. 다음과 같이 Drive, Server명을 적고, Database명, DB에 로그인을 위한 Id,Password를 적는다.
conn.ConnectionString = "Driver={SQL Server};Server=192.168.65.132;Database=test_db;uid=sa;pwd=x1234"
'//Database Connection을 Open한다.
conn.Open
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
'// 조회조건 설정 - 입력된 값이 있을 때만 SQL문의 조건절을 만든다.
If argDeptName = "" Then
strDeptName = ""
Else
strDeptName = " AND deptname LIKE '%" & esc(argDeptName) & "%'"
End If
If argUserName = "" Then
strUserName = ""
Else
strUserName = " AND username LIKE '%" & esc(argUserName) & "%'"
End If
strSQL = "SELECT deptname,username,id,salary" & _
" FROM users " & _
" WHERE id > 0 " & strDeptName & strUserName
'//Database에서 Data를 가져온다.
rs.Open Source:=strSQL, ActiveConnection:=conn, CursorType:=adOpenForwardOnly, LockType:=adLockReadOnly, Options:=adCmdText
If Not rs.EOF Then '//자료가 존재하면 loop를 돌면서 userArray에 결과를 넣는다.
'//첫번째 레코드로 이동한다.
rs.MoveFirst
ReDim userArray(CLng(rs.recordCount))
For i = 0 To rs.recordCount - 1
With userArray(i)
.deptName = rs("deptname").Value
.userName = rs("username").Value
.id = rs("id").Value
.salary = IIf(IsNull(rs("salary").Value), 0, rs("salary").Value)
End With
rs.MoveNext
Next i
End If
'//Recordset개체와 Connection개체의 연결을 닫는다.
rs.Close
conn.Close
'//Recordset개체와 Connection개체를 소멸시킨다.
Set rs = Nothing
Set conn = Nothing
'//userArray(user Type의 Array형태)를 반환한다.
selectUsers = userArray
End Function
2) 저장기능
Form에서 저장버튼을 누르면 SQL Server Database에 Data를 추가(Insert)하거나 수정(Update)한다.
Private Sub cmdSave_Click()
Dim argUser As User
Dim result As JobResult
On Error GoTo ErrHandler
'//입력값 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
Exit Sub
'//에러처리
ErrHandler:
If (Err.Number <> 0) Then
MsgBox "에러가 발생하였습니다" & vbNewLine & vbNewLine & "Error Number : " & Err.Number & vbNewLine & "Error Description : " & Err.Description
End If
End Sub
'//-------------------------------------------------------------------
Function insertUser(argUser As User) As JobResult
Dim conn As New ADODB.Connection
Dim strSQL As String
Dim result As JobResult
Dim affectedCount As Long
Set conn = New ADODB.Connection
'//연결문자열을 만든다. 다음과 같이 Drive, Server명을 적고, Database명, DB에 로그인을 위한 Id,Password를 적는다.
conn.ConnectionString = "Driver={SQL Server};Server=192.168.65.132;Database=test_db;uid=sa;pwd=x1234"
'//Database Connection을 Open한다.
conn.Open
'//Data를 Insert할 SQL을 문자열로 만든다.
strSQL = "INSERT INTO users (deptname,username,id,salary) " & _
"VALUES ( '" & esc(argUser.deptName) & "','" & _
esc(argUser.userName) & "'," & _
argUser.id & "," & _
argUser.salary & ")"
'//위에서 만든 SQL문을 실행한다.
'//recordsaffected(Optional parameter)는 처리된 Data의 갯수를 가져온다. 여기서는 1행만 Insert하므로 1을 가져온다.
conn.Execute CommandText:=strSQL, recordsaffected:=affectedCount
If affectedCount = 0 Then
result.code = -1
result.message = "입력한 자료에 해당하는 Data가 없어서 추가되지 않았습니다."
Else
result.code = 0
result.message = "처리되었습니다."
End If
conn.Close
Set conn = Nothing
insertUser = result
End Function
'//-------------------------------------------------------------------
Function updateUser(argUser As User) As JobResult
Dim conn As New ADODB.Connection
Dim strSQL As String
Dim result As JobResult
Dim affectedCount As Long
Set conn = New ADODB.Connection
'//연결문자열을 만든다. 다음과 같이 Drive, Server명을 적고, Database명, DB에 로그인을 위한 Id,Password를 적는다.
conn.ConnectionString = "Driver={SQL Server};Server=192.168.65.132;Database=test_db;uid=sa;pwd=x1234"
'//Database Connection을 Open한다.
conn.Open
'//Data를 Update할 SQL을 문자열로 만든다.
strSQL = "UPDATE users " & _
" SET deptname = '" & esc(argUser.deptName) & "'," & _
" username = '" & esc(argUser.userName) & "'," & _
" salary = " & argUser.salary & _
" WHERE id = " & argUser.id
'//위에서 만든 SQL문을 실행한다.
'//recordsaffected(Optional parameter)는 SQL을 실행하고 처리된 Data의 갯수를 가져온다.
conn.Execute CommandText:=strSQL, recordsaffected:=affectedCount
conn.Close
Set conn = Nothing
If affectedCount = 0 Then
result.code = -1
result.message = "입력한 사번에 해당하는 Data가 없어서 업데이트 되지 않았습니다."
Else
If affectedCount > 1 Then
result.code = -2
result.message = "사번이 중복된 자료가 " & affectedCount & "개 있습니다."
Else
result.code = 0
result.message = "자료가 수정되었습니다."
End If
End If
updateUser = result
End Function
3) 삭제기능
Form에서 삭제버튼을 누르면 SQL Server Database에서 Data를 삭제한다.
Private Sub cmdDelete_Click()
Dim argUser As User
Dim result As JobResult
On Error GoTo ErrHandler
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
'//에러처리
ErrHandler:
If (Err.Number <> 0) Then
MsgBox "에러가 발생하였습니다" & vbNewLine & vbNewLine & "Error Number : " & Err.Number & vbNewLine & "Error Description : " & Err.Description
End If
End Sub
'//-------------------------------------------------------------------
Function deleteUser(argUser As User) As JobResult
Dim conn As New ADODB.Connection
Dim result As JobResult
Dim affectedCount As Long
Dim strSQL As String
Set conn = New ADODB.Connection
'//연결문자열을 만든다. 다음과 같이 Drive, Server명을 적고, Database명, DB에 로그인을 위한 Id,Password를 적는다.
conn.ConnectionString = "Driver={SQL Server};Server=192.168.65.132;Database=test_db;uid=sa;pwd=x1234"
'//Database Connection을 Open한다.
conn.Open
'//Data를 Delete할 SQL을 문자열로 만든다.
strSQL = "DELETE FROM users " & _
" WHERE id = " & argUser.id
'//위에서 만든 SQL문을 실행한다.
'//recordsaffected(Optional parameter)는 SQL을 실행하고 처리된 Data의 갯수를 가져온다.
conn.Execute CommandText:=strSQL, recordsaffected:=affectedCount
conn.Close
Set conn = Nothing
If affectedCount = 0 Then
result.code = -1
result.message = "입력한 사번 해당하는 Data가 없어서 삭제되지 않았습니다."
Else
result.code = 0
result.message = "처리되었습니다."
End If
deleteUser = result
End Function
이상으로 13강을 마친다. 구체적인 기능은 첨부의 실습용 파일을 참고하기 바라며, 이 프로그램은 Database프로그램의 구조를 쉽게 이해할 수 있도록 하기 위해서 만든 것이므로 실제로 사용하기에는 비효율적인 부분이 많다, 실제 업무에 적용시에는 입력Data의 Validation, DB연결을 공통기능으로 만든다든지 하는 추가적인 노력이 필요함을 알려둔다.
첨부(실습용 엑셀 매크로 파일) : 13강_VBA로 MS SQL Server에 연결하여 Data처리하기(ADO를 이용하여 조회_추가_수정_삭제).xlsm
[ 엑셀에서 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를 다루는가?
