iT邦幫忙

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

Access VBA的眉眉角角系列 第 8

Access VBA 的眉眉角角Day8: 於VBA中的設定值存取方法

對於Excel的多工作表資料對照中,常用到的VLookup、HLookup函數,相信各位都很熟悉,但Access資料庫中也有類似的東西嗎?有的,就是DLookup函數,從D開頭就可以看得出指的是Database,透過此函數,我們可以於 VBA、甚至是SQL語法中,取得所須的數值。

我們用以下當作範例,此程式會顯示「員工」資料表中,「員工編號」為1的「姓名」資料

Sub DLookup範例()

    Debug.Print DLookup("姓名", "員工", "員工編號=1")

End Sub

這個函數,用於單一資料欄位讀取是蠻不錯的,但如果有多筆資料,或者多個欄位要讀取時,就不是那麼理想了。

除了DLookup函數外,尚有以下以D開頭的函數可以運用,其使用方法與DLookup函數類似:

  • DAvg 函數:計算均值
  • DCount 函數:計算記錄數量
  • DFirst、DLast 函數:帶出第一筆、最後一筆資料
  • DMin、DMax 函數:帶出最大值、最小值
  • DStDev、DStDevP 函數:計算標準差
  • DSum 函數:計算加總
  • DVar、DVarP 函數:計算變異數

好的,我們再回到DLookUp,當撰寫程式時,有時有些參數、設定值想紀錄下來,然後在其他的程式中取出使用,這時候您可能會想到,前面幾天有教過DAO方式存取,或者RunSQL方式執行SQL語句寫入資料,以及用剛剛提到的DLookup來讀取資料,這些都可行,也都是我們處理資料的方法,但當同一個流程寫了多次後就會發現,應該要寫個子程式來處理的,這裡我就提供自己的設定值存取子程式供各位參考:

ConfigSave:將設定值名稱、數值與註解寫入

Function ConfigSave(strConfigName As String, strValue, Optional strNote As String)
    '寫入設定
    
    '若Config表不在,則建立一個
    Dim strSQL As String
    If ifObjectExists("Config") = False Then
        CurrentDb.Execute "CREATE TABLE Config " _
                & "([Name] TEXT , [Value] TEXT ,[Note] MEMO , " _
                & "CONSTRAINT ConfigConstraint UNIQUE (Name) " _
                & ");"
    End If
    
    '如果數值與註解都存在
    If strValue <> "" And strNote <> "" Then
        strSET = "[Value]= '" & strValue & "', [Note]= '" & strNote & "' "
        strCol = "[Value],[Note], Name"
        strValues = "'" & strValue & "','" & strNote & "','" & strConfigName & "' "
        
    '如果只有數值
    ElseIf strValue <> "" Then
        strSET = "[Value]='" & strValue & "'"
        strCol = "[Value], Name"
        strValues = "'" & strValue & "','" & strConfigName & "' "
        
    '如果只有註解
    ElseIf strNote <> "" Then
        strSET = "[Note]= '" & strNote & "'"
        strCol = "[Note], Name"
        strValues = "'" & strNote & "','" & strConfigName & "' "
    
    '如果為空值
    Else
        strSET = "[Value]= ''"
        strCol = "[Value], Name"
        strValues = "'" & strNote & "','" & strConfigName & "' "
    End If
    
    '若資料名稱Name存在,則用更新,若無名稱,則新增
    If DLookup("[Name]", "Config", "[Name]='" & strConfigName & "'") <> "" Then
        strSQL = "UPDATE Config SET " & strSET & " WHERE [Name] = '" & strConfigName & "';"
    Else
        strSQL = "INSERT INTO Config (" & strCol & ") VALUES(" & strValues & ")"
    End If
    
    RunSQL strSQL, True
End Function

Config:讀取指定的設定值名稱,將數值取出,或讀取該設定值的註解

Function Config(strConfigName As String, Optional bnShowNote As Boolean = False)
    '讀取設定
    
    '若Config表不在,帶出空值
    If ifObjectExists("Config") = False Then
        Config = ""
        Exit Function
    End If
    
    '依照是否顯示Note來顯示Value或者Note
    If bnShowNote = False Then
        Config = DLookup("[Value]", "Config", "[Name]='" & strConfigName & "'")
    Else
        Config = DLookup("[Note]", "Config", "[Name]='" & strConfigName & "'")
    End If
    
    If IsNull(Config) = True Then Config = ""
    
End Function

這兩個程式中有用到ifObjectExists與RunSQL子程式,請確定這兩個子程式均已複製到Public模組中,這樣才能使用這兩個子程式。

ConfigSave程式中,會判斷Config資料表是否存在,如果不存在則建立一個,然後再偵測strConfigName變數,是否存在於Name欄位中,如果存在,則使用更新方式處理,如果不存在,則建立一筆新的資料。
Config程式則是給它strConfigName變數,會帶出該變數之前儲存的數值,或可可以查詢該變數的註解。

以下兩則範例可複製到範例模組中測試:

寫入Config

Sub ConfigSave範例()
    Dim strConfig As String, strValue As String, strNote As String
    
    strConfig = "身高"
    strValue = 180
    strNote = "測試用!"
    
    Call ConfigSave(strConfig, strValue, strNote)
    
End Sub

執行後會建立Config資料表
http://ithelp.ithome.com.tw/upload/images/20161209/20007221LhkrpNbFXH.png

內容為剛剛寫入的資料:
http://ithelp.ithome.com.tw/upload/images/20161209/20007221nZyrpkarg1.png

讀取Config

Sub Config範例()
    Dim strConfig As String
    
    strConfig = "身高"
    
    Debug.Print "名稱:" & strConfig
    Debug.Print "數值:" & Config(strConfig)
    Debug.Print "註解:" & Config(strConfig, True)
    
End Sub

http://ithelp.ithome.com.tw/upload/images/20161209/20007221T88Kh6aoNL.png

這兩者的搭配應用,各位可以再依照個人需求擴充功能,以達節省程式開發的目的。


上一篇
Access VBA 的眉眉角角Day7: SQL語法於VBA中的應用3
下一篇
Access VBA 的眉眉角角Day9: 關於日期挑選這檔事
系列文
Access VBA的眉眉角角30
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

尚未有邦友留言

立即登入留言