제목. 단위데이터검증을 위한 VBA
목적. 전환데이터 vs 통시데이터 비교 검증
전환데이터 : ASIS에 전환규칙 적용한 TOBE 데이터 (oracle to oracle)
통시데이터 : 전환데이터를 TOBE 데이터베이스로 이관한 데이터 (oracle to aurora)
1. 환경구성
1.1 엑셀vba 64비트 사용으로 oracle client 64bit 설치
- oracle 접속시 사용
1.2 mysql-connector-odbc-8.0.23-winx64.msi (64bit 설치)
- mysql, aurora, maria db 접속시 사용
2. 엑셀 Sheets 구성
[VBA기능]
- 전환/통시(개발) 데이터 조회
- PK컬럼명 앞에 * 표시(*PK컬럼명)
- PK조건 조회
- 조건 추가 조회
- 데이터 비교 체크 결과 저장
2.1 vba 단위테스트데이터 검증 화면 시트
2.2 DB영역별 정의 시트
2.3 Masking 처리 시트
3. 결과 저장을 위한 프로시저
3.1 테이블 정의
CREATE TABLE XXXX.LD_UNIT_TAB_VRFY_D
(
SYS_CD VARCHAR2(4) NOT NULL,
, OPEN_STEP_ID NUMBER NOT NULL,
, TOBE_TAB_NM VARCHAR2(500 CHAR) NOT NULL
, TOBE_COL_NM VARCHAR2(500 CHAR) NOT NULL
, VRFY_ORCT NUMBER(10) NOT NULL,
, VRFY_RSLT_CD VARCHAR2(1) DEFAULT 'N' NOT NULL
, VRFY_DTTM DATE NOT NULL
, MIG_DB_NM VARCHAR2(20) NOT NULL
, APP_DB_NM VARCHAR2(20) NOT NULL
, REMARK VARCHAR2(4000)
, MIG_DATA_VAL VARCHAR2(4000)
, MVO_DATA_VAL VARCHAR2(4000)
, APP_DATA_VAL VARCHAR2(4000)
)
;
CREATE UNIQUE INDEX XXXX.PK_LD_UNIT_TAB_VRFY_D
ON XXXX.LD_UNIT_TAB_VRFY_D (SYS_CD, OPEN_STEP_ID, TOBE_TAB_NM, TOBE_COL_NM, VRFY_ORCT);
ALTER TABLE XXXX.LD_UNIT_TAB_VRFY_D
ADD CONSTRAINT PK_LD_UNIT_TAB_VRFY_D PRIMARY KEY (SYS_CD, OPEN_STEP_ID, TOBE_TAB_NM, TOBE_COL_NM, VRFY_ORCT);
COMMENT ON TABLE XXXX.LD_UNIT_TAB_VRFY_D IS '단위테이블검증상세';
COMMENT ON COLUMN XXXX.LD_UNIT_TAB_VRFY_D.SYS_CD IS '시스템코드';
COMMENT ON COLUMN XXXX.LD_UNIT_TAB_VRFY_D.OPEN_STEP_ID IS '오픈단계';
COMMENT ON COLUMN XXXX.LD_UNIT_TAB_VRFY_D.TOBE_TAB_NM IS 'TOBE테이블명';
COMMENT ON COLUMN XXXX.LD_UNIT_TAB_VRFY_D.TOBE_COL_NM IS 'TOBE컬럼명';
COMMENT ON COLUMN XXXX.LD_UNIT_TAB_VRFY_D.VRFY_ORCT IS '검증차수';
COMMENT ON COLUMN XXXX.LD_UNIT_TAB_VRFY_D.VRFY_RSLT_CD IS '검증결과코드';
COMMENT ON COLUMN XXXX.LD_UNIT_TAB_VRFY_D.VRFY_DTTM IS '검증일시';
COMMENT ON COLUMN XXXX.LD_UNIT_TAB_VRFY_D.MIG_DB_NM IS '전환DB명';
COMMENT ON COLUMN XXXX.LD_UNIT_TAB_VRFY_D.APP_DB_NM IS '응용DB명';
COMMENT ON COLUMN XXXX.LD_UNIT_TAB_VRFY_D.REMARK IS '검증의견';
COMMENT ON COLUMN XXXX.LD_UNIT_TAB_VRFY_D.MIG_DATA_VAL IS '전환데이터값';
COMMENT ON COLUMN XXXX.LD_UNIT_TAB_VRFY_D.MVO_DATA_VAL IS '이관데이터값';
COMMENT ON COLUMN XXXX.LD_UNIT_TAB_VRFY_D.APP_DATA_VAL IS '응용데이터값';
3.2 프로시저 정의
CREATE OR REPLACE PROCEDURE XXXX.SP_MIG_UNIT_TAB_VRFY_GATH(
/*검증결과 저장을 위해서 컬럼 단위로 호출되는 프로시저*/
P_TOBE_TAB_NM IN VARCHAR2 /*TOBE테이블명*/
, P_TOBE_COL_NM IN VARCHAR2 /*TOBE컬럼명*/
, P_VRFY_RSLT_CD IN VARCHAR2 /*검증결과코드(Y:OK, A:확인필요(응용), M:확인필요(전환), N:NULL(검증미수행을 의미))*/
, P_MIG_DB_NM IN VARCHAR2 /*전환DB명*/
, P_APP_DB_NM IN VARCHAR2 /*응용DB명*/
, P_PRMT1 IN VARCHAR2 /*임시파라미터1*/
, P_PRMT2 IN VARCHAR2 /*임시파라미터2*/
, P_PRMT3 IN VARCHAR2 /*임시파라미터3*/
, P_MIG_DATA_VAL IN VARCHAR2 /*전환데이터값(전환서버)*/
, P_MVO_DATA_VAL IN VARCHAR2 /*이관데이터값(통시OR개발)*/
, P_APP_DATA_VAL IN VARCHAR2 /*응용데이터값(응용팀생성)*/
, P_REMARK IN VARCHAR2 /*검증의견*/
)
IS
V_REG_RSLT VARCHAR2(100);
V_ERR_MSG VARCHAR2(1000);
BEGIN
IF P_TOBE_TAB_NM IS NULL OR P_TOBE_COL_NM IS NULL OR P_MIG_DB_NM IS NULL OR P_APP_DB_NM IS NULL OR P_PRMT1 IS NULL THEN
RAISE_APPLICATION_ERROR(-20102, 'CHECK THE INPUT VALUE');
ELSE
INSERT INOT XXXX.LD_UNIT_TAB_VRFY_D (
SYS_CD, OPEN_STEP_ID, TOBE_TAB_NM, TOBE_COL_NM, VRFY_ORCT
, VRFY_RSLT_CD, VRFY_DTTM, MIG_DB_NM, APP_DB_NM, REMARK
, MIG_DATA_VAL, MVO_DATA_VAL, APP_DATA_VAL)
SELECT SYS_CD, OPEN_STEP_ID, TOBE_TAB_NM, TOBE_COL_NM, VRFY_ORCT, VRGY_RSLT_CD
, VRFY_DTTM, MIG_DB_NM, APP_DB_NM, REMARK, MIG_DATA_VAL
FROM (
SELECT '1' AS RK /*우선순위*/
, A.SYS_ID AS SYS_CD
, REGEXP_SUBSTR(REGEXP_SUBSTR(A.SUBJECT_AREA_PATH,'[1-3]단계오픈'), '[1-3]') AS OPEN_STEP_ID
, A.TAB_NM AS TOBE_TAB_NM
, REGEXP_REPLACE(A.COL_ID,'^[*]','') AS TOBE_COL_NM
, (SELECT NVL(MAX(D.VRFY_ORCT),0)+1
FROM XXXX.LD_UNIT_TAB_VRFY_D D
WHERE D.TOBE_TAB_NM = A.TAB_NM
AND D.SYS_CD = A.SYS_ID
AND D.OPEN_STEP_ID = REGEXP_SUBSTR(REGEXP_SUBSTR(A.SUBJECT_AREA_PATH,'[1-3]단계오픈'), '[1-3]')
AND D.VRFY_DTTM <> TO_DATE(P_PRMT1, 'YYYYMMDDHH24MISS')
)
, NVL(DECODE(P_VRFY_RSLT_CD,'OK','Y','확인필요(응용)','A','확인필요(전환)','M',''),'N') AS VRGY_RSLT_CD
, TO_DATE(P_PRMT1,'YYYYMMDDHH24MISS') AS VRFY_DTTM
, P_MIG_DB_NM AS MIG_DB_NM
, P_APP_DB_NM AS APP_DB_NM
, SUBSTRB(TO_CHAR(P_REMARK),1,4000) AS REMARK
, SUBSTRB(TO_CHAR(P_MIG_DATA_VAL),1,4000) AS MIG_DATA_VAL
, SUBSTRB(TO_CHAR(P_MVO_DATA_VAL),1,4000) AS MVO_DATA_VAL
, SUBSTRB(TO_CHAR(P_APP_DATA_VAL),1,4000) AS APP_DATA_VAL
FROM XXXX.VW_TOBE_COL_SVC_MAP A /*테이블 명세서 (오픈단계정보 포함)*/
WHERE A.TOBE_ACCT_ID = P_MIG_DB_NM AND A.TAB_NM = P_TOBE_TAB_NM AND A.COL_ID = REGEXP_REPLACE(P_TOBE_COL_NM,'^[*]','') /*컬럼정보 존재시*/
UNION ALL
SELECT '2' AS RK
, A.SYS_ID AS SYS_CD
, REGEXP_SUBSTR(REGEXP_SUBSTR(A.SUBJECT_AREA_PATH,'[1-3]단계오픈'), '[1-3]') AS OPEN_STEP_ID
, A.TAB_NM AS TOBE_TAB_NM
, REGEXP_REPLACE(A.COL_ID,'^[*]','') AS TOBE_COL_NM
, (SELECT NVL(MAX(D.VRFY_ORCT),0)+1
FROM UENCORE.LD_UNIT_TAB_VRFY_D D
WHERE D.TOBE_TAB_NM = A.TAB_NM
AND D.SYS_CD = A.SYS_ID
AND D.OPEN_STEP_ID = REGEXP_SUBSTR(REGEXP_SUBSTR(A.SUBJECT_AREA_PATH,'[1-3]단계오픈'), '[1-3]')
AND D.VRFY_DTTM <> TO_DATE(P_PRMT1, 'YYYYMMDDHH24MISS')
)
, NVL(DECODE(P_VRFY_RSLT_CD,'OK','Y','확인필요(응용)','A','확인필요(전환)','M',''),'N') AS VRGY_RSLT_CD
, TO_DATE(P_PRMT1,'YYYYMMDDHH24MISS') AS VRFY_DTTM
, P_MIG_DB_NM AS MIG_DB_NM
, P_APP_DB_NM AS APP_DB_NM
, SUBSTRB(TO_CHAR(P_REMARK),1,4000) AS REMARK
, SUBSTRB(TO_CHAR(P_MIG_DATA_VAL),1,4000) AS MIG_DATA_VAL
, SUBSTRB(TO_CHAR(P_MVO_DATA_VAL),1,4000) AS MVO_DATA_VAL
, SUBSTRB(TO_CHAR(P_APP_DATA_VAL),1,4000) AS APP_DATA_VAL
FROM XXXX.VW_TOBE_COL_SVC_MAP A
WHERE A.TOBE_ACCT_ID = P_MIG_DB_NM AND A.TAB_NM = P_TOBE_TAB_NM AND ROWNUM <= 1 /*컬럼정보 미존재시 => 테이블정보만 존재 */
ORDER BY 1 ASC
) WHERE ROWNUM <= 1
;
IF SQL%ROWCOUNT = 1 THEN
COMMIT;
ELSE
ROLLBACK;
RAISE_APPLICATION_ERROR(-20103, 'DUPLICATION ERROR');
END IF;
END IF;
EXCEPTION WHEN OTHERS THEN
ROLLBACK;
RAISE_APPLICATION_ERROR(-20101, 'OTHER ERROR : '||SQLERRM);
END;
/
GRANT EXECUTE ON XXXX.SP_MIG_UNIT_TAB_VRFY_GATH TO MIGXXX;
4. VBA
4.1 Oracle Connection
Function getMIGBFConnection() As ADODB.Connection
On Error GoTo failed:
Dim conn As ADODB.Connection
Dim sid, userid, passwd As String
Dim connSQL As String
'userid = "XXXX"
'passwd = "XXXX"
userid = Worksheets("단위테스트데이터검증").Range("01").Value
passwd = Worksheets("단위테스트데이터검증").Range("02").Value
Set conn = New ADODB.Connection
connSQL = "Provider=OraOLEDB.Oracle;"
connSQL = connSQL & "Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)"
connSQL = connSQL & "(HOST=172.xx.xx.xxx)(PORT=1525)))(CONNECT_DATA=(SID=ORCL)(SERVER=DEDICATED)));"
connSQL = connSQL & "User Id=" & userid & ";Password=" & passwd & ";"
conn.ConnectionString = connSQL
conn.Open
Set getMIGBFConnection = conn
Exit Function
falied:
Set conn = Nothing
MsgBox Err.Description
Err.Raise Err.Number, Err.Source, Err.Description
End Function
4.2 MySQL Connection
Function getMySQLConnection(p_dbid As String) As ADODB.Connection
On Error GoTo failed:
Dim conn As ADODB.Connection
Dim dbid As String, userid As String, passwd As String, server_ip As String
Dim connSQL As String
Dim RNG As Range
dbid = p_dbid
'userid = "XXXX"
'passwd = "XXXX"
userid = Worksheets("단위테스트데이터검증").Range("03").Value
passwd = Worksheets("단위테스트데이터검증").Range("04").Value
'DB정보 가져오기
Set RNG = Worksheets("주제영역").Range("E2:K200")
server_ip = WorksheetFunction.VLookup(dbid, RNG, 3, False)
'If dbid = "xxxx" Or dbid = "xxxxx" Then
' server_ip = WorksheetFunction.VLookup(dbid, rng, 3, False)
'Else
' Exit Function
'End If
Set conn = New ADODB.Connection
connSQL = "Driver={MySQL ODBC 8.0 Unicode Driver};Server=" & server_ip & ";Port=3306;Database=" & dbid & ";User=" & userid & ";Password=" & passwd & ";Option=2; "
conn.ConnectionString = connSQL
conn.Open
Set getMySQLConnection = conn
Exit Function
falied:
Set conn = Nothing
MsgBox Err.Description
Err.Raise Err.Number, Err.Source, Err.Description
End Function
4.3 데이터 조회
Sub MySQL_DEV_SELECT()
On Error GoTo onErr:
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim rowidx As Integer, tot_cnt As Integer
Dim fieldCnt As Long
Dim sql2 As String, sql As String, v_SCHEMA As String, v_TABLE_NM As String, v_COL_NM As String, v_SEARCH_CNT As String, v_DBid As String, v_COL_NM1 As String
Dim MigTargetDB As String, TONGDB As String, OrderBy As String
Dim RNG As Range
Dim OraOwner As String
v_DBid = Range("D1").Value
If v_DBid = "" Then
MsgBox "DB를 선택해주세요!"
Exit Sub
End If
If Worksheets("단위테스트데이터검증").Range("01").Value = "" Or Worksheets("단위테스트데이터검증").Range("02").Value = "" Then
MsgBox "전환DB에 접속할 ID, PW를 입력해주세요."
Exit Sub
End If
Call MASK_SELECT
'개발/통시 서버 정보
Set RNG = Worksheets("주제영역").Range("E2:K200")
MigTargetDB = WorsheetFunction.VLookup(v_DBid, RNG, 5, False) 'DB종류
TONGDB = WorsheetFunction.VLookup(v_DBid, RNG, 6, False) '구분
OraOwner = WorsheetFunction.VLookup(v_DBid, RNG, 10, False) 'Owner
'MsgBox MigTargetDB
'MsgBox "커넥션시작!!"
If MigTargetDB = "Aurora" Then
Set conn = getAuroraConnection(v_DBid)
ElseIf MigTargetDB = "MySQL" Or MigTargetDB = "Maria" Then
Set conn = getMySQLConnection(v_DBid)
ElseIf MigTargetDB = "Oracle" Then
Set conn = getORADevConnection(v_DBid)
Else
Set conn = getORADevConnection(v_DBid)
End If
Set rs = New ADODB.Recordset
Set rs2 = New ADODB.Recordset
'타겟이 오라클인 경우 Owner 명을 별도로 지정할 수 있도록 수정
If MigTargetDB = "Oracle" Then
If Len(Trim(OraOwner)) > 0 Then
v_SCHEMA = OraOwner
Else
v_SCHEMA = Range("D1").Value
End If
Else
v_SCHEMA = Range("D1").Value
End If
'v_SCHEMA = Range("D1").Value
v_TABLE_NM = Range("F1").Value
v_COL_NM = Replace(Range("F2").Value, "'", "''") '조회조건 WHERE COL1 = 'A' --> WHERE COL1 = ''A''
OrderBy = Range("G3").Value
If OrderBy = "PK" Then
v_COL_NM1 = Replace(Range("A5").Value, "*", "") '첫 번째 컬럼
Else
v_COL_NM1 = "DATA_INPT_DTTM"
End If
v_SEARCH_CNT = Range("F3").Value
If v_SCHEMA = "" Then
MsgBox "스키마를 선택하세요!"
Exit Sub
ElseIf v_TABLE_NM = "" Then
MsgBox "테이블명을 입력하세요!"
Exit Sub
ElseIf v_SEARCH_CNT = "" Then
MsgBox "조회수를 입력하세요!"
Exit Sub
Else
If MigTargetDB = "Oracle" Then
If v_COL_NM <> "" Then
v_COL_NM = " AND (" & v_COL_NM & ")"
End If
sql = sql & " SELECT * "
sql = sql & " FROM ( "
sql = sql & " SELECT CASE WHEN COLUMN_ID = 1 THEN 'SELECT ' ELSE ',' END ||CASE WHEN DATA_TYPE = 'DATE' THEN '''''||'||COLUMN_NAME||' AS '||COLUMN_NAME ELSE COLUMN_NAME END AS COL_INFO "
sql = sql & " FROM ALL_TAB_COLS "
sql = sql & " WHERE OWNER = '" & v_SCHEMA & "' "
sql = sql & " AND TABLE_NAME = '" & v_TABLE_NM & "' "
sql = sql & " ORDER BY COLUMN_ID "
sql = sql & " ) "
sql = sql & " UNION ALL "
sql = sql & " SELECT ' FROM '||'" & v_SCHEMA & "'||'.'||'" & v_TABLE_NM & "'||' A WHERE ROWNUM <= " & v_SEARCH_CNT & v_COL_NM & "'||' ORDER BY 1 DESC ' "
sql = sql & " FROM DUAL "
Else
If v_COL_NM <> "" Then
v_COL_NM = " AND (" & v_COL_NM & ")"
End If
sql = sql & " SELECT * "
sql = sql & " FROM ( "
sql = sql & " SELECT CASE WHEN ORDINAL_POSITION = 1 THEN 'SELECT ' ELSE ',' END ||CASE WHEN DATA_TYPE = 'datetime' THEN '''''||'||COLUMN_NAME||' AS '||COLUMN_NAME ELSE COLUMN_NAME||' AS ' END ||CASE WHEN COLUMN_KEY = 'PRI' THEN '""*'||COLUMN_NAME||'""' ELSE COLUMN_NAME END AS COL_INFO "
sql = sql & " FROM information_schema.columns "
sql = sql & " WHERE TABLE_SCHEMA = '" & v_SCHEMA & "' "
sql = sql & " AND TABLE_NAME = '" & v_TABLE_NM & "' "
sql = sql & " ORDER BY ORDINAL_POSITION "
sql = sql & " ) A "
sql = sql & " UNION ALL "
'PK기준 desc
sql = sql & " SELECT ' FROM '||'" & v_SCHEMA & "'||'.'||'" & v_TABLE_NM & " A " & v_COL_NM & "'||' ORDER BY " & v_COL_NM1 & " DESC LIMIT " & v_SEARCH_CNT & "' "
sql = sql & " FROM DUAL "
End If
End IfApplication.ScreenUpdating = False
rs.Open sql, conn
Do Until rs.EOF
sql2 = sql & rs.Fields(0) & Chr(13)
i = i + 1
rs.MoveNext
Loop
Range("H2").Value = sql2
rs2.Open sql2, conn
'기존 내용을 삭제 후 새로운 내용 입력
Range("A20").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.ClearContents
Selection.Interior.Color = xlNone
'Selection.ClearFormats
Range("A6:Z1000").NumberFormat = "@"
Range("A6:Z1000").WrapText = False
Range("A6:Z1000").RowHeight = 14
'Range("H6").TextToColumns = ""
'If MigTargetDB = "Aurora" Then
' Range("A20").Value = "설계자: 통시서버조회결과"
'Else
' Range("A20").Value = "설계자: 개발서버조회결과"
'End If
Range("A12").Value = "전환담당: " & TONGDB & " 서버조회결과"
Range("A20").Font.Bold = True
Range("A20:C20").Interior.ColorIndex = 45
'결과 출력
'Range("A22").CopyFromRecordset rs2
If rs2.EOF Then
MsgBox "자료가 없습니다."
Else
With ActiveSheet
.Range("A22").CopyFromRecordset rs2
End With
'데이터셋의 헤더출력
For i = 0 To rs2.Fields.Count - 1
Cells(21, i + 1).Value = rs2.Fields(i).Name
Cells(21, i + 1).Interior.ColorIndex = 45
Next
End If
Worksheets("단위테스트데이터검증").Columns("K:Z").AutoFit
Application.ScreenUpdating = True
rs.Close 'Recordset 개체의 연결을 닫는다.
rs2.Close
Set rs = Nothing 'Recordset 개체를 소멸시킨다.
Set rs2 = Nothing
conn.Close 'Connection 개체의 연결을 닫는다.
Set conn = Nothing 'Connection 개체를 소멸시킨다.
pnif_masking ("A21")
Range("A6").Select
Exit Sub
onErr:
'MsgBox "(B3셀) 테이블에 맞는 주제영역인지 확인하세요!"
Set rs = Nothing
Set conn = Nothing
MsgBox Err.Description
'Err.Raise Err.Number, Err.Source, Err.Description
End Sub
4.4 체크결과 저장
'체크결과 저장
Sub ETT_CHECK_RESULT()
On Error GoTo onErr:
Dim conn As ADODB.Connection
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset
Dim rowidx As Integer, tot_cnt As Integer
Dim i As Long
Dim sql As String, v_SCHEMA As String, v_TABLE_NM As String, v_COL_NM As String, v_DEV_SCHEMA As String
Dim VAR As Variant
Dim RNG As Range
Dim dt As String
'MsgBox "커넥션시작!!"
Set conn = getETTConnection
v_SCHEMA = Range("B1").Value
v_DEV_SCHEMA = Range("D1").Value
v_TABLE_NM = Range("F1").Value
Set RNG = Range("A5")
VAR = RNG.End(xlToRight).Column
For i = 0 To VAR - 1
If Cells(29, i + 1).Value = "" Then
MsgBox Cells(5, i + 1).Value & " 컬럼의 체크 결과를 입력하여 주세요!"
Cells(29, i + 1).Select
Exit Sub
End If
Next
dt = Format(Now(), "yyyymmddHHMMSS")
'MsgBox dt
'데이터저장
For i = 0 To VAR - 1
'MsgBox Cells(5, i + 1).Value & ":" & Cells(29, i + 1).Value
Set cmd = New ADODB.Command
With cmd
.ActiveConnection = conn
.CommandText = "UENCORE.SP_MIG_UNIT_TAB_VRFY_GATH"
.CommandType = 4 'adCmdStoredProc
param1 = v_TABLE_NM '테이블명
param2 = Cells(5, i + 1).Value '컬럼영
param3 = Cells(29, i + 1).Value '체크결과
param4 = v_SCHEMA '전환DB
param5 = v_DEV_SCHEMA '개발/통시 DB
param6 = dt '넘길변수값
param7 = "" '넘길변수값
param8 = "" '넘길변수값
.Parameters.Append .CreateParameter("P_TOBE_TAB_NM", adVarWChar, adParamInput, 100, param1) '입력 파라메터
.Parameters.Append .CreateParameter("P_TOBE_COL_NM", adVarWChar, adParamInput, 100, param2) '입력 파라메터
.Parameters.Append .CreateParameter("P_VRFY_RSLT_CD", adVarWChar, adParamInput, 100, param3) '입력 파라메터
.Parameters.Append .CreateParameter("P_MIG_DB_NM", adVarWChar, adParamInput, 100, param4) '입력 파라메터
.Parameters.Append .CreateParameter("P_APP_DB_NM", adVarWChar, adParamInput, 100, param5) '입력 파라메터
.Parameters.Append .CreateParameter("P_PRMT1", adVarWChar, adParamInput, 100, param6) '입력 파라메터
.Parameters.Append .CreateParameter("P_PRMT2", adVarWChar, adParamInput, 100, param7) '입력 파라메터
.Parameters.Append .CreateParameter("P_PRMT3", adVarWChar, adParamInput, 100, param8) '입력 파라메터
Set rs = .Execute
End With
Set cmd = Nothing
Next
MsgBox "저장완료!"
Set rs = Nothing
Application.ScreenUpdating = True
conn.Close
Set conn = Nothing
Exit Sub
onErr:
Set rs = Nothing
Set conn = Nothing
MsgBox Err.Description
'Err.Raise Err.Number, Err.Source, Err.Description
End Sub
5. 파일
'VBA' 카테고리의 다른 글
| 문서닫기[X] 전에 셀비우고 저장 (0) | 2024.12.04 |
|---|---|
| INSTR 사용법 (0) | 2024.11.12 |
| VBA로 다른 VBA 파일제어 (0) | 2024.11.12 |
| 매핑정의서 vba (0) | 2024.11.12 |