엑셀VBA에서 MariaDB 연결방법 - Heeyoung-Ahn/MariaDB-with-Excel-VBA GitHub Wiki
Client(Excel) ↔ ADO ↔ ODBC ↔ MariaDB
ADO(ActiveX Data Object)
- DB에 접속하여 Data를 처리하는 역할 담당
- ADO를 Excel VBA에서 사용하기 위해서는 VBA에 ADO Library를 추가해야 함
- Excel VBA Editor > 도구 > 참조 > Library 선택: Microsoft ActiveX data Objects 6.1
- 모든 Client에 설치해야 함
ODBC(Open Database Connectivity)
- RDBMS에 접근할 수 있도록 MS에서 개발한 표준규격
- DB의 제조사별로 만들어진 ODBC Driver를 모든 Client에 설치해야 함
- MariaDB ODBC Driver 다운로드
- Windows Installers 선택
- PC에 설치되어 있는 Office 버전에 맞는 설치파일 다운로드(msi 확장자)
- Office 버전 확인 방법: 파일 > 계정 > Excel 정보 클릭하여 확인
- VBA 활용도를 높이려면 Office는 32bit로 설치되어 있는 것이 유리
Excel에서 Database에 연결하는 방법
ODBC - DSN 이용
- DSN(Data Source Name): DB 연결에 필요한 정보를 담고 있는 것
- 제어판 > 관리도구 > ODBC데이터원본(64비트)
- TCP/IP: MariaDB 서버의 IP, Port: 3306
- 계정정보: MariaDB의 username, password
- Database: 연결할 DB
- 모든 Client에 설정해야 함(불편함)
- 사용자DSN: DSN을 만든 사용자만 이용 가능
- 시스템DSN: PC의 모든 사용자가 이용 가능
ODBC - DSN 이용 안하는 경우
- VBA Code에 DB 연결에 필요한 정보를 입력해 둬야 함
- Database 연결 Code Sample
Option Explicit
Public Const ODBCDriver As String = "MariaDB ODBC 3.1 Driver"
Public conn As ADODB.Connection
Public rs As New ADODB.Recordset
'-----------------------------------------------
' DB연결 프로시저
' - connectDB(서버 IP, 스키마, ID, PW)
'-----------------------------------------------
Sub connectDB(argIP As String, argDB As String, argID As String, argPW As String)
Set conn = New ADODB.Connection
conn.ConnectionString = "Driver={" & ODBCDriver & "};Server=" & argIP & ";Port=3306;Database=" & argDB & ";User=" & argID & ";Password=" & argPW & ";Option=2;"
conn.Open
End Sub
'-----------------------
' Common DB연결
' - 로그인 확인
'-----------------------
Sub connectCommonDB()
connectDB "IP_address", "db_name", "username", "password"
End Sub
'---------------------------------------------------
' Task DB연결
' - 로그인 시 작업 DB 연결을 위한 정보 확인
'---------------------------------------------------
Sub connectTaskDB()
connectDB connIP, connDB, connUN, connPW
End Sub
- Recordset에 DB자료 반환 Code Sample
'---------------------------------------------------------------------
' 레코드셋 설정 및 데이터 반환
' - callDBtoRS(프로시저명, 테이블명, SQL문, 폼이름, 잡이름)
' - 오류발생 시 에러 핸들링 및 로그 기록
' - 오류발생 안하면 잡 수행 프로시저에서 로그 기록(필요 시)
'---------------------------------------------------------------------
Sub callDBtoRS(ProcedureNM As String, tableNM As String, SQLScript As String, Optional formNM As String = "NULL", Optional JobNM As String = "NULL")
On Error GoTo ErrHandler
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.Open Source:=SQLScript, ActiveConnection:=conn, CursorType:=adOpenForwardOnly, LockType:=adLockReadOnly, Options:=adCmdText
Exit Sub
ErrHandler:
ErrHandle ProcedureNM, tableNM, SQLScript, formNM, JobNM
writeLog ProcedureNM, tableNM, SQLScript, 1, formNM, JobNM '//오류코드 1
End Sub
- Database 및 Recordset 연결 해제 Code Sample
'--------------------------
' DB 및 RS 연결 해제
'--------------------------
Sub disconnectRS()
On Error Resume Next
rs.Close
Set rs = Nothing
On Error GoTo 0
End Sub
Sub disconnectDB()
On Error Resume Next
conn.Close
Set conn = Nothing
On Error GoTo 0
End Sub
Sub disconnectALL()
On Error Resume Next
rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing
On Error GoTo 0
End Sub