1 / 53

第 8 章 DAO 物件應用和程式設計

第 8 章 DAO 物件應用和程式設計. 本章宗旨. 簡介 DAO 物件之引用方式 簡介 DAO 物件之各項資料操作指令 介紹資料表自動連入與移除之處理方式 介紹 VBA 資料庫操作指令與程序檔建檔範例 介紹事件導向之程式設計風格 介紹多功能整合式表單之設計方式 簡介 DAO 物件設計 API 之進階應用－ SQL 指令集管理. 大 綱. 8-1 前置處理 8-2 DAO 指令簡介 8-3 進階應用. 8-1 前置處理. DAO 為 Data Access Object 之縮血寫

edythe
Download Presentation

第 8 章 DAO 物件應用和程式設計

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 第8章 DAO 物件應用和程式設計 主從式資料庫系統 - CH8

  2. 本章宗旨 • 簡介DAO物件之引用方式 • 簡介DAO物件之各項資料操作指令 • 介紹資料表自動連入與移除之處理方式 • 介紹VBA資料庫操作指令與程序檔建檔範例 • 介紹事件導向之程式設計風格 • 介紹多功能整合式表單之設計方式 • 簡介DAO物件設計API之進階應用－SQL指令集管理 主從式資料庫系統 - CH8

  3. 大 綱 8-1 前置處理 8-2 DAO指令簡介 8-3 進階應用 主從式資料庫系統 - CH8

  4. 8-1 前置處理 • DAO為Data Access Object之縮血寫 • DAO物件大都以VBA語言來設計程序或函數，再以這些程序和函數來處理資料。完成程序設計後，還需要將DAO物件引入，程序檔方能正常執行 主從式資料庫系統 - CH8

  5. 8-1 前置處理(續) • 引用過程 • 開啟任一表單在設計模式 • 點選主功能表【檢視】、【程式碼】 • 點選程式碼視窗【工具】、【設定引用項目(R)】 • 出現設定引用項目視窗後，捲動可引用項目，請勾選【Microsoft DAO 3.6 Object Library】 • 按下優先權方向鍵，將前述選項往上移至頂端 • 按【確定】鈕，結束引用過程 主從式資料庫系統 - CH8

  6. 8-1 前置處理(續) • 引用結果 主從式資料庫系統 - CH8

  7. 8-2 DAO指令簡介 8-2-1 資料庫連結 8-2-2 紀錄集開啟指令語法 8-2-3 查詢介面建立與執行 8-2-4 資料處理程序 8-2-5 紀錄及欄位操作指令說明 主從式資料庫系統 - CH8

  8. 8-2-1 資料庫連結 • DAO與其他資料庫連結方式 • 直接以連線物件連結，資料在伺服端處理 • 直接以連線物件連結，資料在主控端處理 • 透過資料庫引擎，搭配ODBC將資料表連入Access 主從式資料庫系統 - CH8

  9. 資料庫連結比較 主從式資料庫系統 - CH8

  10. 1. 以TransferDatabase 指令連結資料表 1.1 TransferDatabase指令 • 語法：Docmd.TransferDatabase 參數一，參數二，參數三，參數四，參數五，參數六，參數七 • 參數一：連結型態，分別是acLink, acImport, acExport，請選用【acLink】 • 參數二：資料庫類型，請選用【ODBC 資料庫】 • 參數三：資料庫名稱，須包含路徑或連結字串 主從式資料庫系統 - CH8

  11. 1. 以TransferDatabase 指令連結資料表(續) • 參數四：資料類型，請選用【acTable】 • 參數五：來源資料名稱，請輸入【資料表名稱】 • 參數六：目的資料名稱，請輸入【資料表名稱】 • 參數七：只含資料結構，請輸入【False】 主從式資料庫系統 - CH8

  12. 1. 以TransferDatabase 指令連結資料表(續) • 應用範例 • DoCmd.TransferDatabase acLink, "ODBC 資料庫", "ODBC; Driver={MySQL ODBC 3.51 Driver}; Server=Localhost; Database=mysal; UID=帳號; PWD=密碼; Option=3 ", acTable, “cuinfo”, “cuinfo”, False • 或改用 • DoCmd.TransferDatabase acLink, "ODBC 資料庫", "ODBC; DSN=資料來源名稱; Database=mysal; UID=帳號; PWD=密碼; Option=3 ", acTable, “cuinfo”, “cuinfo”, False 主從式資料庫系統 - CH8

  13. 1. 以TransferDatabase 指令連結資料表(續) • 範例說明 • 帳號和密碼，輸入要登錄MySQL之對應帳號和密碼 • 資料來源名稱則為ODBC資料管理員之設定名稱 • 設定DSN時，可以單獨指定Name和Host參數，帳號、密碼和資料庫名稱三項參述則在前述指令內加入亦可，如此可提高系統安全性 • 上述指令可加入在表單介面內，然後搭配On Open事件程序使用 主從式資料庫系統 - CH8

  14. 1.1 以公用程序連結資料表 • 首先點取【模組物件】、【新增】，新增一個名稱叫conModule之模組。 • 在模組內新增一個名稱叫【LinkTable】之公用程序，完整指令如下： Public Sub LinkTable(wdb as string, tbn as string) DoCmd.TransferDatabase acLink, "ODBC 資料庫", "ODBC; Driver= “ & _ “{MySQL ODBC 3.51 Driver}; Server=Localhost; Database=” & wdb & “;” & _ “UID=帳號; PWD=密碼; Option=3 ", tbn, tbn, False End Sub 主從式資料庫系統 - CH8

  15. 1.1 以公用程序連結資料表(續) • 在處理資料之表單介面適當位置(例如 On Open事件)加入下列指令，即可連結資料表： Call LinkTable(“資料庫名稱”,”資料表名稱”) • 例如：Call LinkTable(“mysal”,”cuinfo”) 主從式資料庫系統 - CH8

  16. 1.2 移除連結資料表 • 可直接在資料表物件容器點取對應資料表，然後按刪除鍵，或是 • 以程序檔在應用程式介面內將連結資料表移除 • 應用範例：在conModule模組內新增一個名稱叫dlTmptbl之公用程序 主從式資料庫系統 - CH8

  17. dlTmptbl程式內容 1. Public Sub dlTmptbl(tbn As String) 2. Dim tbl As TableDef 3. Dim db As Database 4. Set db = CurrentDb 5. With db 6. For Each tbl In db.TableDefs 7. If tbl.Name = tbn Then 8. db.TableDefs.Delete tbl.Name 9. Exit For 10. End If 11. Next tbl 12. End With 13. End Sub 主從式資料庫系統 - CH8

  18. dlTmptbl程式應用 • 在主功能表之On Close事件程序加入下列指令，就可以自動移除連結資料表 1. Dim db as database 2. Dim rs as recordset 3. Set db = currentdb 4. Set rs=db.OpenRecordset(“Select * From sysTable Where database= ’mysal’”) 主從式資料庫系統 - CH8

  19. dlTmptbl程式應用(續) 5. With rs 6. Do while not .EOF 7. Call dlTmptbl(rs!Table) 8. .movenext 9. Loop 10. End With 11. rs.close 主從式資料庫系統 - CH8

  20. 1.3 開啟資料表 • 指令 方法一： Set rs = db.OpenRecordset("cuinfo") 方法二： Set rs = db.OpenRecordset("Select * From cuinfo") 方法三： Set qry = db.CreateQueryDef("查詢名稱", "SQL指令") Set rs = qry.OpenRecordset() 主從式資料庫系統 - CH8

  21. 1.3 開啟資料表(續) • 應用說明 • 此方式開啟之紀錄集，不能直接連結至Access之其他介面，只能以DAO物件提供之方法操作紀錄 • 指定名稱建立查詢時，該查詢介面可儲存在Access查詢物件內，且該查詢介面可以直接連結至表單介面 主從式資料庫系統 - CH8

  22. 1.4 操作資料庫及處理資料 • 使用SQL指令操作資料庫及處理資料 Dim qry As QueryDef Set qry = db.CreateQueryDef("", "SQL指令") qry.Execute 主從式資料庫系統 - CH8

  23. 2. 使用OpenDatabase方法 • 此方法須建立一個工作區，作為主從端對話空間 • 連結指令： • 語法： Setworkspace = CreateWorkspace(name, user, password, type) Setdatabase = workspace.OpenDatabase (dbname,options,read-only,connect) 主從式資料庫系統 - CH8

  24. 2. 使用OpenDatabase方法(續) • 其中各項變數之意義: • Workspace：工作區物件變數 • name：工作區名稱，只建立一個工作區時，可不指名 • user：工作區帳號，通常使用預設值”Admin”，非MySQL之登入帳號。 • password：密碼，可缺設 • type：使用dbUseJet(主控端)|或是dbUseODBC(伺服端) • dbname：資料庫名稱(Jet)或是資料來源名稱(ODBC) • Options：選項常數 主從式資料庫系統 - CH8

  25. 2. 使用OpenDatabase方法(續) • Options：可使用下列常數值 • dbDriverNoPrompt：預設以dbname和connection string與伺服器連線，若連線資訊不足時將提示錯誤 • dbDriverPrompt：連線時將提示DSN對話框，若處理人員未選取DSN，則以預設之DSN資訊來連結 • dbDriverComplete：此為預設值，假設連線字串已包含足夠的連線資訊 • dbDriverCompleteRequired：與dbDriverComplete類似，除了ODBC驅動程式無法提示足夠的連線資訊時 • read-only：唯讀屬性 • connect：連線字串 主從式資料庫系統 - CH8

  26. 2.1 應用範例 • 在模組內新增一公用程序 - openDB，指令如下： 1. Public ws As Workspace 2. Public db As Database 3. Public As Recordset 4. Public Sub openDB() 5. Dim str As String 6. Set ws = DBEngine.CreateWorkspace("", “Admin", "", dbUseJet) 7. str = "ODBC; DRIVER={MySQL ODBC 3.51 Driver}; SERVER = localhost; DATABASE=mysal; UID=帳號; PWD=密碼; OPTION=3“ 8. Set db = ws.OpenDataBase("資料來源", dbDriverNoPrompt, False, str) 9.End Sub 主從式資料庫系統 - CH8

  27. 2.1 應用範例(續) • 在主功能表之On Open事件程序加入下列指令，就可以自動建立連線物件 Call openDB • 資料表開啟與處理方法與第一種連結方式相同，但是建立查詢介面時，不能指定介面名稱 主從式資料庫系統 - CH8

  28. 8-2-2 紀錄集開啟指令 1. 語法 • 搭配連線物件或資料庫物件 語法： Set recordset = object.OpenRecordset (資料源, 型態, 選項, 讀寫模式) 範例： Set rs = con.OpenRecordset(引數串列) Set rs = db.OpenRecordset(引數串列) 主從式資料庫系統 - CH8

  29. 1. 語法(續) • 搭配查詢、紀錄集或資料表 語法： Set recordset = object.OpenRecordset (型態, 選項, 讀寫模式) 範例： Set rs = qry.OpenRecordset(引數串列) 主從式資料庫系統 - CH8

  30. 2. 各項參數之用法 • 資料源：可以是資料表或是SQL指令 • 型態：可選用下列常數 • dbOpenTable：開啟資料表型式之紀錄集(適用於dbUseJet) • dbOpenDynamic：開啟動態型之紀錄集(適用於dbUseODBC) • dbOpenDynaset：開啟動態集之紀錄集，類似ODBC keyset 紀錄指標 • dbOpenSnapshot：開啟快照型之紀錄集, 類似ODBC static 紀錄指標 • dbOpenForwardOnly：開啟前導型之紀錄集 主從式資料庫系統 - CH8

  31. 2. 各項參數之用法(續) 註： • 若未指定型態，dbUseJet預設為資料表型 • 若搭配物件為資料表或查詢，則預設為動態集 • dbUseODBC預設為前導型。 主從式資料庫系統 - CH8

  32. 2. 各項參數之用法(續) • 可選用下列常數選項 • dbAppendOnly：新增紀錄，但不能編輯和刪除(適用於dbUseJet之dynaset紀錄集) • dbSQLPassThrough：傳送一道SQL指令至伺服端(適用於dbUseJet之 snapshot-紀錄集) • dbSeeChanges：在多人使用環境下，有人試圖變更他人正在編輯的資料，立即產生錯誤信息(適用於dbUseJet之dynaset紀錄集) 主從式資料庫系統 - CH8

  33. 2. 各項參數之用法- 選項 • dbDenyWrite：預防他人修改或新增紀錄 (適用於dbUseJet紀錄集物件) • dbDenyRead：預防他人讀取紀錄(適用於dbUseJet之table紀錄集) • dbRunAsync：非同步查詢(適用於ODBCDirect工作區) 主從式資料庫系統 - CH8

  34. 2. 各項參數之用法- 選項 • dbExecDirect：執行查詢時，跳過SQLPrepare直接叫用SQLExecDirect (適用於ODBCDirect 工作區)，當紀錄未以參數式查詢開啟時，需搭配此項參數 • dbInconsistent：允許不一致更新資料(適用於dbUseJet之dynaset與snapshot紀錄集) • dbConsistent：允許一致更新資料(適用於dbUseJet之dynaset與snapshot紀錄集) 主從式資料庫系統 - CH8

  35. 2. 各項參數之用法 - 讀寫模式 • dbReadOnly：唯讀模式(ODBCDirect工作區預設模式)，此模式不能同時出現在選項和讀寫參數設定 • dbPessimistic：當下達Edit指令後，立即將包含該紀錄之資料頁鎖定(Jet 工作區預設模式) • dbOptimistic：當下達Update指令後，才將包含該紀錄之資料頁鎖定(Jet 工作區預設模式) • dbOptimisticValue：以記錄為單位樂觀型更新資料(適用於ODBCDirect工作區) • dbOptimisticBatch：允許以批次方式樂觀型更新資料(適用於ODBCDirect工作區) 主從式資料庫系統 - CH8

  36. 8-2-3 查詢介面建立與執行 1. 查詢介面建立語法 • 語法： Set qry = object.CreateQueryDef (name, sqltext) qry：查詢物件變數 object：資料來源物件，可以是連線物件或是資料庫物件 主從式資料庫系統 - CH8

  37. 1. 查詢介面建立語法(續) name：查詢介面名稱 • 若object為連線物件或是資料庫物件時（只適用於dbUseJet），不得指定名稱 • 若為連結型資料表（以TransferDatabase連線者），可以指定名稱，也可以缺設 • 指定名稱時，不可以和查詢物件內的其他介面同名 sqltext：SQL指令敘述，建立qry物件時可缺設，然後在他處設定qry.SQL之屬性值即可 主從式資料庫系統 - CH8

  38. 2. 查詢介面執行語法 • 若屬選取型(Select)之查詢介面，可直接連結至表單來查看資料 • 或是以下列指令來開啟一個紀錄集 Set rs = qry.OpenRecordset() • 若屬動作型(如Insert, Update, Delete等)之查詢介面，可以使用下列指令來處理資料qry.Execute options 主從式資料庫系統 - CH8

  39. 2. 查詢介面執行語法(續) • 其中options有下列常數選項： • dbDenyWrite：預防他人寫入資料(只適用於Jet工作區) • dbInconsistent：預設值，不一致的更新資料(只適用於Jet工作區) • dbConsistent：一致型更新資料(只適用於Jet工作區) • dbSQLPassThrough：將SQL傳送給伺服端去處理資料(只適用於Jet工作區) 主從式資料庫系統 - CH8

  40. 2. 查詢介面執行語法(續) • dbFailOnError：處理資料發生錯誤時，倒捲回原始狀態 (只適用於Jet工作區) • dbSeeChanges：在多人使用環境下，有人試圖變更他人正在編輯的資料，立即產生錯誤信息(只適用於Jet工作區) • dbRunAsync：非同步處理資料(適用於ODBCDirect工作區) • dbExecDirect：使用於未以SQLPrepare方式呼叫ODBC介面方法(適用於ODBCDirect連線物件之查詢介面) 主從式資料庫系統 - CH8

  41. 8-2-4 資料處理程序 • 新增紀錄 • 指令 ： • rs.AddNew ：新增一筆空白紀錄(在記憶體) • rs.Update ：將記憶體資料寫回磁碟 • rs!CU_Tel = Me!CU_Tel：將表單控制項資料置入紀錄集之對應欄位 • Me!CU_Tel = rs!CU_Tel ：將紀錄集之欄位資料置入表單控制項 主從式資料庫系統 - CH8

  42. 8-2-4 資料處理程序(續) • 查詢紀錄 • 搜尋紀錄指令：rs.FindFirst • 範例：rs.FindFirst "CU_No ='" & Me!CU_No & "'“ • 找次筆指令：rs.FindNext • 判斷未找到：rs.Nomatch 主從式資料庫系統 - CH8

  43. 8-2-4 資料處理程序(續) • 刪除記錄 • 指令： rs.Delete • 刪除資料時，最好能提示處理人員 Style = vbYesNo + vbCritical + vbDefaultButton2 rps = MsgBox("刪除紀錄嗎?", Style, "刪除紀錄") If rps = vbYes Then rs.Delete End If 主從式資料庫系統 - CH8

  44. 8-2-4 資料處理程序(續) • 儲存/編輯紀錄 • 指令： rs.Edit ：執行編輯資料(記憶體) rs.Update ：將記憶體資料寫回磁碟 主從式資料庫系統 - CH8

  45. 8-2-4 資料處理程序(續) • 更新資料、轉檔或批次刪除資料 • 使用動作查詢方法 • 範例： Str2=” Update rcpay, 銷貨金額計算 Set rcpay.CR_Spamt = rcpay!CR_Spamt “ & _ “+ 銷貨金額計算!Amount, rcpay.CR_Upamt = rcpay!CR_Upamt + “ & _“銷貨金額計算!Amount, 銷貨金額計算.TR_Note = ‘T’ “ & _“WHERE ((([銷貨金額計算].[TR_Note]) Is Null));” Set qry2 = db.CreateQueryDef("", str2) qry2.Execute 主從式資料庫系統 - CH8

  46. 更新資料、轉檔或批次刪除資料 • 使用紀錄集操作方法， 範例： str1=” Select spbill.SP_Blno, spbill.CU_No, splist.PD_No, splist.SP_Qty,” & _ “cuquoat.UN_Price, splist!SP_Qty*cuquoat!UN_Price AS Amount, “ & _ “splist.TR_Note FROM cuquoat Inner Join (spbill Inner Join splist ON “ & _ “spbill.SP_Blno = splist.SP_Blno) ON (cuquoat.CU_No = spbill.CU_No) “ & _ “And (cuquoat.PD_No = splist.PD_No) Where (((splist.TR_Note) Is Null)) ” & _ “ Order By spbill.CU_No;” Set qry1 = db.CreateQueryDef("", str1) Set rs1 = qry1.OpenRecordset() str2 = “Select * From rcpay Order By CU_No;” Set rs2 = db.OpenRecordset(str2) With rs1 主從式資料庫系統 - CH8

  47. 使用紀錄集操作方法(續) • rs1.Edit • rs1!TR_Note = ‘T’ • rs1.Update • End If • End With • .MoveNext • Loop • End With • rs1.Close • rs2.Close Do While Not .EOF cu=!CU_No With rs2 .MoveFirst .FindFirst “CU_No = ‘” & cu & “’” If Not .NoMatch Then .Edit !CR_Spamt = !CR_Spamt + rs1!Amount !CR_Upamt =!CR_Upamt + rs1!Amount .Update 主從式資料庫系統 - CH8

  48. 8-2-5 紀錄及欄位操作指令說明 1. 搜尋紀錄指令：FindFirst • 語法：FindFirst 參數一 參數二 參數三 • 說明：參數一為欄名，參數二為關係運算子，參數三為欄位值 • 範例：rs.FindFirst “CU_No = ‘” & cu & “’”，因為cu為字串變數，所以前後需加上單引號，若為日期變數則前後要加上#，若為數值則直接代入變數 • 搜尋次筆，語法：FindNext • 搜尋末筆，語法：FindLast 主從式資料庫系統 - CH8

  49. 8-2-5 紀錄及欄位操作指令說明(續) 2. 移動紀錄指令 • 移次筆，語法：MoveNext • 移上筆，語法：MovePrevious • 第一筆，語法：MoveFirst • 最末筆，語法：MoveLast 3. 計算紀錄筆數 語法：rs．RecordCount，回傳結果為長整數 4. 計算欄位數 語法：rs．Fields．Count，回傳結果為整數 5. 取得欄名語法：rs(i).Name 主從式資料庫系統 - CH8

  50. 8-2-5 紀錄及欄位操作指令說明(續) 6. 欄位資料處理可選用下列任意型式： • 指定欄名：rs(“欄名”)，rs.Fields(“欄名”)，rs.Fields.Item(“欄名”) 皆可 • 指定欄序：rs(i)，rs.Fields (i)，rs.Fields.Item(i)，i 從0起算 • 取出欄值 • 範例： 變數＝rs(i)，或rs!欄名 • 存回欄值 • 範例： rs(i)＝變數，或rs!欄名=變數 • 搭配 For 迴圈處理每個欄位 For i = 0 To rs.Fields.Count – 1 變數＝rs(i) …….. Next i 主從式資料庫系統 - CH8

More Related