14강에서는 VBA로 Oracle DB Server DB에 연결하여 Data를 조회,추가,수정,삭제하는 작업을 해 보고자 한다. Oracle은 다른 DB에 비해서 연결이 까다롭다.Oracle Client설치를 잘못하거나 설정을 잘못하면 연결이 안되어 연결방법을 찾느라 고생할 수도 있으므로 아래 순서대로 차근차근 따라해 보자. 그러나 각자 PC의 OS 종류 및 버전, Oracle의 서버의 버전, 구성방식 등이 차이가 있을 수 있고 이 때문에 연결방법이 약간씩 다를 수 있다. 만약 연결이 안될때는 아래에 따로 적은 Trouble shooting부분을 참고하기 바란다. DB를 연결하고 처리하는 기본 개념을 익히기 위해 다음과 같이 Excel sheet에 Data를 출력하는 간단한 기능을 먼저 구현해 보자.  1) Table 생성Oracle DB Server가 설치되어 있다고 가정하고 다음의 SQL로 테스트용 Table을 생성하자. CREATE TABLE users (
deptname varchar2(50) NOT NULL,
username varchar2(50) NOT NULL,
id int NOT NULL PRIMARY KEY,
salary float NOT NULL
);
조회에 필요한 데이터는 INSERT SQL을 수행하여 각자 입력한다. 2) VBA에서 소스입력VBA Module에 다음의 소스코드를 입력한다. Oracle DB Server에서 Data를 가져와서 Excel sheet에 필드명과 Data를 출력하는 기능이다. 소스코드에 대한 설명은 주석에 달아 놓았다. '//Oracle Database 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
'//Oracle DB에 연결하기 위해서는 여러가지 방법이 있을 수 있으나 여기에서는 ODBC 또는 OLEDB를 이용하여 연결문자열을 만든다.
'====================================================================================================
'//1. ODBC를 이용하여 연결
'//각자 사정에 맞는 방식을 사용하면 된다.
'///참고 사이트 - https://docs.oracle.com/database/122/ADFNS/odbc-driver.htm#ADFNS1188
'// 방식1.1 : DSN을 이용하여 연결
conn.ConnectionString = "DSN=TEST_DSN_ORA;Uid=test;Pwd=x1234;"
'// 방식1.2 : DSN이용안함, TNS NAME은 있어야 함
'conn.ConnectionString = "DRIVER={Oracle in OraClient11g_home2};UID=test;PWD=x1234;DBQ=XE;DBA=W;"
'//ODBC 연결문자열에 들어가는 각각의 Keyword의 의미는 다음과 같다. 각자 환경에 맞도록 값을 수정한다.
'//--------------------------------------------------------------------------------------------------
'// DRIVER : ODBC Driver이름, Windows에서 ODBC를 실행하여 사용자DSN이나 시스템DSN 탭에서 추가버튼을 누르면 Driver의 이름을 확인할 수 있다.
'// DSN : ODBC설정에서 등록하는 Data source명(Data Source Name)
'// UID : DB에 Login하는 User ID
'// PWD : Password
'// DBQ : TNS Service Name(Oracel Client를 설치하고 Net Configuration Assistant를 실행하여 등록하는 로컬네트 서비스이름)
'// DBA : Database Attribute(W=write access, R=read-only access)
'====================================================================================================
'//2. OLEDB를 이용하여 연결
'// 방식2.1 : OLEDB를 이용하여 연결, TNS NAME은 있어야 함
'conn.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=XE;User Id=test;Password=x1234;"
'// 방식2.2 : OLEDB를 이용하여 연결, TNS NAME 사용안함.
'conn.ConnectionString = "Provider=OraOLEDB.Oracle;" & _
"Data Source=(DESCRIPTION=(CID=GTU_APP)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)" & _
"(HOST=192.168.99.199)(PORT=1521)))(CONNECT_DATA=(SID=XE)(SERVER=DEDICATED)));" & _
"User Id=test;Password=x1234;"
'//OLEDB 연결문자열에 들어가는 각각의 Keyword의 의미는 다음과 같다. 각자 환경에 맞도록 값을 수정한다.
'//--------------------------------------------------------------------------------------------------
'// Provider : OLEDB 공급자명, Oracle은 "OraOLEDB.Oracle"을 사용
'// Data Source : TNS를 이용하는 경우에는 TNS Service Name(Oracel Client를 설치하고 Net Configuration
'// Assistant를 실행하여 등록하는 로컬네트 서비스이름)을 기입하고 TNS를 이용하지 않으면
'// HOST에 Oracle DB 서버IP, PORT에 DB PORT번호(default:1521) SID에 Oracle SID를 입력한다.
'// User Id : DB에 Login하는 User ID
'// Password : Password
'//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 "
'//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
아래 그림과 같이 VBA Form을 이용하여 DB의 Data를 조회,추가,수정,삭제 하는 기능이다. Excel sheet에 있는 버튼을 클릭하면 Form이 나타나고 각각의 버튼을 클릭했을때 Data를 처리하는 기능이다. VBA프로그램코드는 첨부의 파일을 참고하기 바란다.
※ Trouble Shooting첨부의 VBA프로그램을 다운로드 받아서 필요한 부분을 작업하고 각자 환경에 맞게 VBA을 수정하여 실행하여도 에러가 나면 아래의 케이스를 확인해 보자.
위의 방법으로 해결이 되지 않는다면 Oracle에서 제공하는 아래의 ODBC Driver 개발가이드를 참고한다. 이상으로 14강을 마친다. 구체적인 기능은 첨부의 실습용 파일을 참고하기 바라며, 이 프로그램은 Database프로그램의 구조를 쉽게 이해할 수 있도록 하기 위해서 만든 것이므로 실제로 사용하기에는 비효율적인 부분이 많다, 실제 업무에 적용시에는 입력Data의 Validation, DB연결을 공통기능으로 만든다든지 하는 추가적인 노력이 필요함을 알려둔다. [ 엑셀에서 Database사용하기 강좌 목록 ]
|
엑셀 오라클 연동 - egsel olakeul yeondong
관련 게시물
저작권 © 2024 nguoilontuoi Inc.
