[엑셀 VBA] 예약 DB - 5. makeSQL() 함수 - INSERT, UPDATE문 자동 생성

작성자
dra****
작성일
2022-10-18 19:47
조회
109

기본 SQL 문법입니다. 칼럼과 필드는 같은 말입니다.

INSERT statement

INSERT INTO 테이블명 (칼럼1, 칼럼2, ...) VALUES (1, 값2, ...);

UPDATE statement

UPDATE 테이블명 SET 칼럼1 =1, 칼럼2 =2, ... WHERE 조건식;

이전 포스팅에서 DB 필드에 해당하는 것을 배열에 담아 두고, arrFields()라 했고,
시트상의 표의 칼럼을 반복문으로 돌면서 값을 가져와 arrValues()에 담았습니다.

INSERT를 할 경우에는 KEY가 필요 없습니다.

UPDATE를 할 경우에는 해당 레코드를 DB에서 찾아야 하므로 조건식(WHERE)문이 필요 하기 때문에 KEY 필드나 다른 유니크한 필드가 필요합니다.

저는 여기서 PRIMARY KEY로 "Ser"을 설정했기 때문에 "Ser"값을 전역으로 세팅을 먼저 해 놓습니다. 이전 포스팅에

If arrFields(col) = "Ser" Then serial = val

이렇게 설정을 미리 해 놓았습니다.

 

Private Function makeSQL(instruct As String, tbName As String, arrFields, arrValues) As String
 
    Dim serial As Long
    Dim strSQL_update As String, strSQL_insert As String
    Dim i As Byte
    Dim tmp As String
    Dim field As String, fields As String, values As String, field_insert As String, value_insert As String
 
    strSQL_update = "UPDATE `" & tbName & "` SET "
    strSQL_insert = "INSERT INTO `" & tbName & "` ( "
    values = " VALUES ("
 
    For i = LBound(arrFields) To UBound(arrFields)
        tmp = arrValues(i)
        field_insert = "`" & arrFields(i) & "` "
 
        If arrFields(i) = "A_TIME" Or arrFields(i) = "R_TIME" Then
            tmp = VBA.Format(tmp, "hh:mm;@")
        ElseIf arrFields(i) = "VCHER_DATE" And VBA.Len(tmp) = 0 Then
            tmp = Empty
        End If
 
        If i = UBound(arrFields) Then
            field = "`" & arrFields(i) & "` = '" & tmp & "' "
            field_insert = "`" & arrFields(i) & "` "
            value_insert = "'" & tmp & "'"
        Else
            field = "`" & arrFields(i) & "` = '" & tmp & "', "
            field_insert = "`" & arrFields(i) & "`, "
            value_insert = "'" & tmp & "', "
        End If
 
        If arrFields(i) = "Ser" Then serial = tmp
 
        strSQL_update = strSQL_update & field
 
        fields = fields & field_insert
        values = values & value_insert
    Next i
 
    If instruct = "update" Then
        strSQL_update = strSQL_update & " WHERE Ser = '" & serial & "';"
'        Debug.Print strSQL_update
        makeSQL = strSQL_update
 
    Else
        strSQL_insert = strSQL_insert & fields & ") " & values & ")"
'        Debug.Print strSQL_insert
        makeSQL = strSQL_insert
    End If
End Function

위의 함수를 호출할때 아래와 같이 하면 strSQL 변수에 문자열 형태의 명령문이 생성 됩니다.

strSQL = makeSQL("update", "book", arrFields, arrValues)

"update" : UPDATE로 만들라는 명령

"book" " DB상의 테이블 이름

arrFields, arrValues : 필드명(칼럼명)과 값을 다음 배열

중간 중간 데이터 타입을 맞추기 위해 Fomat() 함수를 쓰기도 합니다.

 

이렇게 만들어진 strSQL을 제가 만든 MySQL클래스 인스턴스의 함수에 인자로 넘겨주면

result = mySQL.mysql_ExecSQL_strSQL(strSQL)
' SQL문으로 실행 : INSERT, UPDATE, DELETE
Public Function mysql_ExecSQL_strSQL(strSQL As String) As Boolean
    Dim aff As Long
 
    On Error GoTo Error_Section
        Call conn.Execute(strSQL, aff)
 
    mysql_ExecSQL_strSQL = IIf(aff > 0, True, False)
 
    Exit Function
 
Error_Section:
    mdMessage.err_MSG "clsMySQL: mysql_ExecSQL_strSQL", Err, False
    mysql_ExecSQL_strSQL = False
End Function

결과값을 true/false로 반환해 줍니다.

 

makeSQL()함수의 경우는 계획한 것이 아니라 필요에 의해서 즉흥적으로 만든것이라

코드가 좀 지저분합니다.

다음에는 폼 디자인에서 시트와 ListBox연동 및 필드 정보를 담는 컨트롤들의 설정에 대해서 포스팅 해 보겠습니다.

스크랩
공유
회원등급 : 29레벨
포인트 : 4251 EP
전체 4

  • 2022-10-21 23:17

    @dra**** 님 코드 내용 중

     If arrFields(i) = "A_TIME" Or arrFields(i) = "R_TIME" Then

    이 부분에서 A_TIME 이랑 R_TIME 은 수정 후에 사용하는 부분인가요?ㅎㅎ;

    코드를 하나하나 공부하고 있는데 궁금해서 여쭙니당..


    • 2022-10-21 23:21

      @더블유에이 님 폼커트롤명을 DB필드명과 일치 시켜 놓았습니다. 그래서 "A_TIME" 필드명을 만나면 해당 폼컨트롤에서 값을 가져와 가공을 하는 부분인데요,

      골프장 예약시간과 승인시간 관련입니다.

      시간을 입력하면, 엑세스의 경우, 무조건 "7:30" 이런식으로 들어갑니다. 엑세스는 그러서 시간 포맷을 따로 할 필요가 없지만,

      MySQL 같은 경우는 문자열로 받아 시간으로 저장하기 때문에 만약 "07:00" 이런식으로 넣어 주면 DB에 보기 좋기 들어 갑니다.

      그래서 그렇게... ^^


      • 2022-10-24 05:19

        @dra**** 님 아 그렇군요 ㅎㅎ 답변 감사합니다.

        이런 스니펫 하나하나가 정말 주옥같은 꿀자료인데, 직접 만드신것도 대단하지만 이렇게 나눠주셔서 정말 감사합니다


    • 2022-10-21 23:27

      @더블유에이 님 위의 함수 돌리면 아래와 같이 만들어 줍니다.

      UPDATE `book` SET `MEM_Ser` = '433', `GCODE` = '', `GUIDE` = '', `GUEST` = 'SEO HACHUL', `GC_Ser` = '3', `PAX` = '2', `TOFF_DATE` = '2022-09-30', `WEEK_DAY` = 'FRI', `HOLES` = '18', `SELL_RATE` = '3000', `SELL_TOTAL` = '6000', `R_TIME` = '12:30', `RECHECK_DATE` = '2022-09-23', `A_TIME` = '12:30', `GUEST_CONFIRM` = '1', `BOOK_DATE` = '2022-09-19', `CONFIRM_CODE` = '', `VCHER_DATE` = '2022-09-19', `MEMO` = 'GUEST CONFIRM 9/19
      NOT YET PAID', `PROGRESS_Ser` = '1', `SELL_PAY_DATE` = '', `NET_RATE` = '0', `INCOME` = '6000', `NET_TOTAL` = '0', `MODIFIED` = '2022-10-21 23:25:51', `ADMIN_Ser` = '3', `RECHECK` = '0', `LOC_INVOICE` = ''  WHERE Ser = '1886';

       


전체 69
번호 제목 작성자 작성일 추천 조회
알림
「🎉 올해의 책 투표」 '진짜쓰는 실무엑셀' 이 후보에 선정되었습니다! (👉깜짝 이벤트) (51)
오빠두엑셀 | 2022.11.07 | 추천 23 | 조회 7816
오빠두엑셀 2022.11.07 23 7816
공지사항
[VBA] 구하라 그러면 주어질 것이다. - VBA 마스터 E-Book (영문판) 첨부파일 (6)
dra**** | 2022.07.19 | 추천 4 | 조회 355
dra**** 2022.07.19 4 355
44293
[엑셀 VBA] 예약 DB - 9. 시트의 바우처&견적서 양식에 데이터 뿌리기 (3)
dra**** | 2022.11.11 | 추천 1 | 조회 136
dra**** 2022.11.11 1 136
44227
[엑셀 VBA] 예약 DB - 8. Listbox 와 폼 컨트롤 연동하고 DB로 보내기
dra**** | 2022.11.09 | 추천 2 | 조회 63
dra**** 2022.11.09 2 63
44219
[엑셀 VBA] 예약 DB - 7. MySQL 클래스와 사용법 (2)
dra**** | 2022.11.09 | 추천 2 | 조회 57
dra**** 2022.11.09 2 57
43438
[엑셀 VBA] 예약 DB - 6. 폼 콘트롤과 DB 필드명 일치시키기
dra**** | 2022.10.21 | 추천 2 | 조회 95
dra**** 2022.10.21 2 95
43292
[엑셀 VBA] 예약 DB - 5. makeSQL() 함수 - INSERT, UPDATE문 자동 생성 (4)
dra**** | 2022.10.18 | 추천 3 | 조회 109
dra**** 2022.10.18 3 109
43091
[엑셀 VBA] 예약 DB - 4. 표 가지고 놀기 (6)
dra**** | 2022.10.13 | 추천 1 | 조회 188
dra**** 2022.10.13 1 188
43017
[엑셀 VBA] 예약 DB -3. 표(테이블, ListObject)을 사용해야 하는 이유1 (11)
dra**** | 2022.10.11 | 추천 1 | 조회 145
dra**** 2022.10.11 1 145
42998
[잡설] 엑셀이 웹으로 가야하는 이유2 (9)
dra**** | 2022.10.10 | 추천 5 | 조회 258
dra**** 2022.10.10 5 258
42982
[엑셀 VBA] 예약 DB - 2. 다시 웹으로 - Access에서 MySQL로 데이터 이식
dra**** | 2022.10.10 | 추천 2 | 조회 120
dra**** 2022.10.10 2 120
40590
[엑셀 VBA] 예약 DB - 1. 엑세스, SQL, Recordset, ListObject 의 상관 관계 (4)
dra**** | 2022.07.29 | 추천 3 | 조회 339
dra**** 2022.07.29 3 339
40511
[잡설] 엑셀의 꽃 파워 쿼리를 버리다. (3)
dra**** | 2022.07.29 | 추천 4 | 조회 552
dra**** 2022.07.29 4 552
40321
[경험담..해결했습니다.] &H80004005(-2147467259)시스템오류 (3)
티엠프이 | 2022.07.25 | 추천 2 | 조회 279
티엠프이 2022.07.25 2 279
40074
M365 업데이트 이후 발생하는 H80004005 (-2147467259) 자동화 오류 해결방법
오빠두엑셀 | 2022.07.23 | 추천 0 | 조회 277
오빠두엑셀 2022.07.23 - 277
39608
엑셀 그래프에 관한 유튜브 채널정보 입니다. (7)
레몬네이드 | 2022.07.15 | 추천 -1 | 조회 211
레몬네이드 2022.07.15 -1 211
36473
[엑셀VBA] 시트 이름을 변수로 설정하고 싶을때 간단한 방법 (2)
알파고 | 2022.06.27 | 추천 2 | 조회 432
알파고 2022.06.27 2 432
35196
Print_Area 동적 인쇄 영역설정 첨부파일 (1)
sean | 2022.06.21 | 추천 4 | 조회 453
sean 2022.06.21 4 453
34607
셀레니움으로 개별공시지가 조회 첨부파일 (3)
나야 | 2022.06.02 | 추천 1 | 조회 291
나야 2022.06.02 1 291
26273
엑셀 2021, M365 가로스크롤 기능 (28)
더블유에이 | 2022.01.23 | 추천 17 | 조회 842
더블유에이 2022.01.23 17 842
Re:엑셀 2021, M365 가로스크롤 기능
김학동 | 2022.01.23 | 추천 1 | 조회 275
김학동 2022.01.23 1 275
Re:엑셀 2021, M365 가로스크롤 기능
김동희 | 2022.05.30 | 추천 0 | 조회 128
김동희 2022.05.30 0 128
24189
[엑셀 VBA] Snippet - OCR: 이미지 파일을 텍스트 파일로 변환하기 (25)
dra**** | 2021.12.01 | 추천 8 | 조회 2080
dra**** 2021.12.01 8 2080
23999
[엑셀 VBA] Snippet - Public IP, Local IP, Mac Address 구하기 (6)
dra**** | 2021.11.25 | 추천 2 | 조회 760
dra**** 2021.11.25 2 760