각 DataBase별 환경설정 부분은 dicws.tistory.com/62 앞에서 확인하시고
이번에는 엑셀에서 해당 테이블을 가져오기 위해 추가설정부분에 대한 얘기를 하고자 합니다.
엑셀만 그렇지는 않고 보통 개발하다 보면 라이브러리가 필요로 할때가 많습니다. 그리고 많은 라이브러리들을 추가해주고 설정해야됩니다. 이런과정이 VBA에도 있습니다.
데이터 연결방법 두가지
윈도우즈 각 드라이버를 설치하고,
첫번째는 윈도우드ODBC에서 연결을 하고 연결된 ODB를 통하여 데이터 연결하는 방법
두번째는 윈도우에 각 드라이버 직접접속 하여 데이터 연결하는 방법
두번째는 CreationObject를 통하여 각 DataBase 종류별로 Connection 스트링을 맞춰 직접 연결
첫번째는 OLBC에서 미리 접속연결하고 동일한 Connection 스트링으로 연결하면 됩니다.
일단 라이브러리 추가 -> 데이터 베이스 연결을 해보겠습니다.
▶ 라이브러리 추가
VBA에서 DataBase를 연결하기위해 또는 일반 파일을 엑세스 하기위해 라이브러리를 추가해야됩니다.
먼저 DataBase를 연결하기 위한 라이브러리를 추가합니다.
메뉴에서 도구->참조
Microsoft ActiveX Data Object Library를 선택 버전은 여러개 있는데 2.0이후부터인데 보통 2.7, 2.8 6.1 선택하시면 됩니다.
▶ DataBase 연결정보 확인
연결전 드라이버를 확인
검색->ODBC 입력후-> 64bit선택
▶ DataBase 직접연결 방법
Option Explicit '명시적 선언
Public dbconn As ADODB.Connection '추가한 라이브러리 선언
Public Sub DBTest()
'DataBase 연결 사용하기 위한 변수 설정
Dim strCon As String '연결String ※요게 DataBase마다 다름
Dim rs As ADODB.Recordset 'Variant 형으로 선언해도 됨
Dim sql
'1-1) Mysql기준으로 Connection String 작성
strCon = "Driver={MySQL ODBC 8.0 Unicode Driver};SERVER=127.0.0.1;PORT=3306;UID=root;PWD=해당패스워드;DATABASE=mysql;"
'1-2) 커넥션 객체 생성 (객체 연결방법 두가지)
'Set dbconn = CreateObject("ADODB.Connection") '라이브러리를 추가하지 않고 직접생성방법
Set dbconn = New ADODB.Connection '라이브러리를 참조로 추가했기 때문에 사용가능
dbconn.Open strCon '1-3)연결
'2-1) DataBase 접속한후 작업
'Set rs = CreateObject("ADODB.RecordSet") 'RecordSet이라고 조회후 내용을 담아둘 객체 수기생성
Set rs = New ADODB.Recordset '참조된 라이브러리로 생성
'2-2) 쿼리 작성
sql = "select * from db "
'2-3) 쿼리 수행(여기서는 조회쿼리로 RecordSet에 담아서 결과출력)
rs.Open sql, dbconn 'Connecton 객체로 sql 수행
While rs.EOF = False
Debug.Print rs.Fields(1) '조회결과를 index로 출력
'Debug.Print rs("컬럼명") '컬럼명을 가지고 출력
rs.MoveNext '다음Record로 전환
Wend
rs.close 'RecordSet 객체 닫기 : 자원반납
dbconn.close 'Connection 객체 닫기: 자원반납
End Sub기본적인 연결을 정의해보았고 DataBase별 다른부분을 설명하겠습니다.
다른부분은 DataBase별 StrCon이라고 Connection String이 달라집니다.
▶DataBase 종류Connection String
1) Excel
strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=파일경로;Extended Properties=Excel 12.0;"※ 엑셀도 일반 DataBase처럼 SQL로 조회 수정이 가능합니다.
2) Oracle
strCon = "DRIVER={드라이버명};Uid=사용자ID;Pwd=사용자암호;DBQ=오라클TNS명;DBA=W;"※ 드라이버명은 위에서 Oracle in instantclient_19_8 입니다.
3) Mysql
strCon = "Driver={드라이버명};SERVER=서버IP;PORT=서버포트;UID=사용자ID;PWD=사용자암호;DATABASE=데이터베이스명;"※ 드라이버명은 위에서 MySQL ODBC 8.0 Unicode Driver 입니다
4) IBM DB2
strCon = "Driver={드라이버명};Database=데이터베이스명;Hostname=서버IP;Port=서버포트;Protocol=TCPIP;Uid=사용자ID;Pwd=패스워드;CurrentSchema=스키마명;"※ 드라이버명은 위에서 IBM DB2 ODBC DRIVER 입니다
'IT > Excel & VB & VBA' 카테고리의 다른 글
| [Excel] 엑셀시트 버튼 매크로 연결실행 (0) | 2020.12.13 |
|---|---|
| [Excel] 이름정의 및 활용 (0) | 2020.12.08 |
| 다섯번째. ODBC 연결을 위한 설정(Mysql, Oracle, Excel, DB2,Sqllite, MongoDB) (0) | 2020.11.28 |
| 네번째. VBA 툴사용 팁 (0) | 2020.11.28 |
| 세번째. VB의 문법(변수선언, 조건문, 반복문) (0) | 2020.11.28 |
