iT邦幫忙

2017 iT 邦幫忙鐵人賽
DAY 4
0
自我挑戰組

Access VBA的眉眉角角系列 第 4

Access VBA 的眉眉角角Day4: 將SQL語法放入VBA中使用

上一篇可以看到,當列出SQL語句於VBA中時,是很繁瑣的,因為如果要有條有理的列出,將SELECT、FROM、WHERE、ORDER BY等語句換行,撰寫時並不是很方便,這裡筆者建議可以建立自己的工具來處理這些瑣碎的事情。

對於文字處理而言,使用「正規表示式 Regular Expression(RegEx)」是蠻不錯的方式,這可以大大降低程式開發時間,對這沒概念的人,可以先閱讀一下陳鍾誠老師的網站:

正規表示式 Regular Expression
http://ccckmit.wikidot.com/regularexpression

這裡,我們僅需要用到每行字串的頭尾處理,以下RegexReplace子程式將有助於我們使用RegEx來替換字串:

Function RegexReplace(ByVal strText As String, _
                      ByVal strReplaceWhat As String, _
                      ByVal strReplaceWith As String, _
                      Optional ByVal bnMultiLine As Boolean = True, _
                      Optional ByVal bnIgnoreCase As Boolean = False) As String
'strText 處理文字
'strReplaceWhat 替換什麼
'strReplaceWith 用什麼來代替
'bnMultiLine 使用多行
'bnIgnoreCase 忽略大小寫

Dim RE As Object
Set RE = CreateObject("vbscript.regexp")

RE.IgnoreCase = bnIgnoreCase
RE.Pattern = strReplaceWhat
RE.Global = True
RE.MultiLine = bnMultiLine

RegexReplace = RE.Replace(strText, strReplaceWith)

End Function

接下來,我們可以使用Access的「表單」工具,來建立我們要的輸入與輸出界面,這部份我第一次講解會寫的詳細些,之後則會略過此部份的講解,需要者再回來找此篇查閱,或者查詢Access操作手冊。

於「表單」處按下「新增」按鈕
http://ithelp.ithome.com.tw/upload/images/20161205/20007221wJPEm1yKDY.png

使用預設的「設計檢視」,按下「確定」
http://ithelp.ithome.com.tw/upload/images/20161205/20007221SXBUokBOxc.png

出現空白表單後,將表單畫面拉大
http://ithelp.ithome.com.tw/upload/images/20161205/20007221ALmwOUiZXL.png

按下「文字方塊」功能選單按鈕,於表單左上角處點選,產生「文字方塊」
http://ithelp.ithome.com.tw/upload/images/20161205/20007221RRsBCphErg.png

跑出文字方塊精靈,選擇取消,我們將自己設定
http://ithelp.ithome.com.tw/upload/images/20161205/20007221C63NOctAFL.png

使用滑鼠於文字方塊與標籤的左上角壓住進行拖曳,將標籤放置於文字方塊上方,並且把標籤改為SQL或者自己定義。
http://ithelp.ithome.com.tw/upload/images/20161205/20007221VxLGxmPVCa.png

將文字方塊左下角壓住拖曳,將文字方塊放大成可貼上SQL語法字串後還可以清楚查詢的大小,完成後,點選文字框,按下鍵盤Ctrl+C複製,然後Ctrl+P貼上。
http://ithelp.ithome.com.tw/upload/images/20161205/200072215gefmgWU4V.png

貼上後會產生另一個文字方塊,此時再修新的文字方塊標籤為SQL FOR VBA,以便於區分
http://ithelp.ithome.com.tw/upload/images/20161205/20007221lUCe8CBKcD.png

點選功能表上的「指令按鈕」,然後於表單右側空白處點選,產生一個按鈕使用
http://ithelp.ithome.com.tw/upload/images/20161205/20007221FIDyNFPuc6.png

出現精靈後,按下取消,我們將自己設定
http://ithelp.ithome.com.tw/upload/images/20161205/20007221HCIQDY5dia.png

將按鈕顯示文字改為「轉換」或者其他自訂名稱
http://ithelp.ithome.com.tw/upload/images/20161205/20007221gi2HPMEhzs.png

接下來,我們將設定這三個物件各自的名稱,於SQL文字方塊上按下滑鼠右鍵,彈出選單顯示後,點選「屬性」功能
http://ithelp.ithome.com.tw/upload/images/20161205/20007221NpDKUxpa90.png

切換到「全部」,拉到最上方,將名稱改為「Text_SQL」
http://ithelp.ithome.com.tw/upload/images/20161205/20007221M3eHCcSRU3.png

改完後不用關閉,直接再點選下方的SQL FOR VBA文字方塊,將名稱改為「Text_SQLForVBA」
http://ithelp.ithome.com.tw/upload/images/20161205/20007221M3p9DEj79z.png

我們依樣畫葫蘆,將小視窗拖曳到旁邊,將點選「轉換」按鈕,將名稱改為「cmd_Do」
http://ithelp.ithome.com.tw/upload/images/20161205/20007221CGXpAIHk0V.png

接下來,我們先存檔,將表單名稱設定為「F_SQL_to_VBA」
http://ithelp.ithome.com.tw/upload/images/20161205/20007221SvHs6Cvb6G.png

這些步驟都完成後,我們將開始處理程式的部份,點選SQL文字方塊,切換到事件,使用After Update的下拉選單,選擇[事件程序],然後再點擊「…」按鈕,開啟程式編輯視窗
http://ithelp.ithome.com.tw/upload/images/20161205/20007221jZGsppAVkW.png

第一次開啟時,可能有些小視窗預設都沒開啟,可以從檢視功能表中,將「程式碼、即時運算視窗、區域變數視窗、監看視窗、專案總管、屬性視窗」這些小視窗開啟,以便於後續程式撰寫與維護
http://ithelp.ithome.com.tw/upload/images/20161205/20007221rg0tovmyNs.png

接下來,我們要插入一個空白模組,以便放置程式,於右側樹狀結構上點選滑鼠右鍵,按下「插入」功能
http://ithelp.ithome.com.tw/upload/images/20161205/200072213DrdQLYrrQ.png

將模組名稱改為Public,然後將剛剛的RegexReplace程式碼貼上使用,這裡代表的是公用模組,可以讓不同表單的程式使用,名稱可以自己取,亦可用中文。
未來如果有更多子程式需要分類的話,可透過插入多個模組放置不同功能的子程式,以便於管理。
http://ithelp.ithome.com.tw/upload/images/20161205/20007221NTs3NdVEsb.png

以下為「F_SQL_to_VBA」表單的程式碼

Private Sub cmd_Do_Click()
    '如果Text_SQL有任何資料,則進行處理,沒有則跳過
    If Text_SQL <> "" Then
        
        '將資料放到strSQL變數
        strSQL = Text_SQL
        
        '替換換行符號由vbCrLf改為vbCr
        strSQL = Replace(strSQL, vbCrLf, vbCr)
        
        '替換「雙引號(")」改為「單引號(')」
        strSQL = Replace(strSQL, """", "'")
        
        '使用RegexReplace替每行開頭加上「雙引號(")」
        strSQL = RegexReplace(strSQL, "^", """")
        
        '使用RegexReplace替每行結尾加上空白、雙引號(")與換行符號
        strSQL = RegexReplace(strSQL, "$", " "" & vbCrLf & _ ")
        
        '於開頭加上一行「strSQL = "" & vbCrLf _」
        strSQL = "strSQL=" & """"" & _ " & vbCr & strSQL
        
        '使用RegexReplace將字串結尾的換行字串去除
        strSQL = RegexReplace(strSQL, " & vbCrLf & _ $", "", False)
        
        Text_SQLForVBA = strSQL
    
    End If
End Sub

Private Sub Text_SQL_AfterUpdate()
    
    '如果輸入的SQL語法的文字框沒資料,則清空結果文字框
    If Len(Text_SQL) = 0 Or IsNull(Text_SQL) Then
        Text_SQLForVBA = ""
        Exit Sub
    End If
    
    '執行Do按鈕的Click事件
    Call cmd_Do_Click
    
    '複製結果到剪貼簿
    Text_SQLForVBA.SetFocus
    DoCmd.RunCommand acCmdCopy
    
End Sub

於右側樹狀結構的「F_SQL_to_VBA」表單名稱處點滑鼠左鍵2下,切換到「F_SQL_to_VBA」表單進行程式碼編輯,將以上程式碼貼到該處,完成後按下鍵盤Ctrl+S進行存檔,存完後按下鍵盤Alt+F11切到Access畫面
http://ithelp.ithome.com.tw/upload/images/20161205/20007221BNtYGIvp1k.png

然後點選右邊的檢視按鈕,我們正式開啟使用
http://ithelp.ithome.com.tw/upload/images/20161205/20007221EHZT8JDC3B.png

將SQL文字方塊處貼上SQL語句,按下Tab按鈕跳離輸入框,就會於下方的SQL FOR VBA文字方塊處產生所需的資料,此時不需要再複製,因為VBA程式碼已將SQL FOR VBA文字方塊內的字串複製到剪貼簿
http://ithelp.ithome.com.tw/upload/images/20161205/20007221ATLzjWrRUV.png

此時,再使用Alt+F11切到VBE程式編輯視窗,我們點Public兩下,跳到此模組中,於下方空白處輸入Sub test,然後按下Enter,即可產生一個名為Test的子程式,此時於子程式內按下Ctrl+P即可貼上剛剛產生的文字內容。
http://ithelp.ithome.com.tw/upload/images/20161205/20007221bjGg1Hcinj.png

我們可以看到,貼上的內容,會產生一個strSQL文字字串,可用於未來處理資料時,於「查詢」功能先產生一組SQL語句,貼到剛剛我們做的「F_SQL_to_VBA」表單,即可再轉貼到VBA程式中使用,此時再依照需求,將WHERE、HAVE、ORDER BY等條件改為需要的變數,即可快速於VBA程式中建立所需的功能。
http://ithelp.ithome.com.tw/upload/images/20161205/20007221dLWE6XqUmz.png

以上介紹到這裡,之後再來介紹如何運用這些strSQL文字資料。


上一篇
Access VBA 的眉眉角角Day3: 關於流程控制與資料處理
下一篇
Access VBA 的眉眉角角Day5: SQL語法於VBA中的應用1
系列文
Access VBA的眉眉角角30

尚未有邦友留言

立即登入留言