第 9 章 ADO 物件應用和程式設計

(1)

第 9 章 ADO

物件應用和程式設計

(2)

本章宗旨

 簡介 ADO 物件結構與引用方式

 簡介 ADO 物件各項資料操作指令

 介紹資料表讀寫模式與鎖定方法

 介紹多功能整合式表單設計方法

 介紹 DataGrid 之應用方式及屬性

 介紹在 Access 內執行 MySQL 專案檔之方式

 介紹以 Access SendObject 指令傳送 E-mail 之方法

(3)

大綱

9-1 ADO 簡介

9-2 ADO 指令簡介

9-3 DataGrid 物件簡介 9-4 進階應用

(4)

9-1 ADO 簡介

9-1-1 ADO 基礎

9-1-2 ADO 物件結構 9-1-3 前置處理

(5)

9-1-1 ADO 基礎

 ADO 為 ActiveX Data Object 之縮寫字

 透過微軟之 OLEDB 系統介面，可直接存取、

編輯和更新所連線的資料源

 常用來查詢一個資料表或是關聯式資料庫內的數個資料表、擷取和展示應用系統之處理結果

，以及允許處理人員更新處理結果

(6)

9-1-1 ADO 基礎

^{( 續 )}

 ADO 用途：

 使用 SQL 指令操作資料庫

 透過 Internet 存取遠端資料

 操作郵件系統之信息

 將資料庫資料轉存為 XML 格式資料

 存取 XML 格式資料

 檢視和更新資料表內資料

(7)

ADO 用途

 執行參數式查詢

 執行預儲程序 (Stored Procedures)

 建立暫存表來處理過渡資料

 執行交易處理

 以動態模式過濾和排序資料

 以離線方式處理遠端資料

(8)

9-1-2 ADO 物件結構

Connection Errors Properties

Error Property

Parameters

Properties Property Command

Recordset Fields Properties

Field Property

Record

Fields Field

Stream

(9)

9-1-3 前置處理

 執行 ADO 物件方法時，必須先將 ADO 物件方法引入程序檔

 需同時引入 DAO 和 ADO 物件，而且 DAO 要在 ADO 上面

(10)

9-2 ADO 指令簡介

9-2-1 資料連結 9-2-2 開啟紀錄集 9-2-3 資料處理程序

9-2-4 批次更新、轉檔及刪除 9-2-5 參數式查詢

9-2-6 紀錄及欄位操作指令說明

(11)

9-2-1 資料連結

 ADO 與其它資料庫連結方式

 直接以連線物件連結（ ADODB.Connectio n ）

 透過 DSN 來連線

(12)

以連線物件連結範例

1. 在 conModule 模組開頭宣告共用變數

 Public adCon As New ADODB.Connection ‘ 宣告公用連線物件變數

 Public adRs As New ADODB.Recordset ‘ 宣告公用紀錄集物件變數

(13)

以連線物件連結範例

( 續 )

2. 檢查連線物件狀態

1. Public Sub checkCon(i As Integer) 2. On Error GoTo chkcon_err:

3. If i = 1 Then

4. If Not adCon.state = adStateOpen Then Call openCon()

5. Else

6. Call closeCon 7. End If

8. Exit Sub 9. chkcon_err:

10. MsgBox Err.Description

(14)

以連線物件連結範例

( 續 )

3. 開啟連線公用程序

1. Public Sub openCon()

2. On Error GoTo opnCon_err_end:

3. Dim cn_str As String

4. cn_str = DRIVER = {MySQL ODBC 3.51 Driver}; "

& _ "SERVER = localhost;" & _ " DATABASE = mysal;" & _

"User = 帳號 ; Password = 密碼 ; OPTION=3"

(15)

3. 開啟連線公用程序

^{( 續 )}

5. If Not adCon.state = adStateOpen Then 6. adCon.CursorLocation = adUseClient 7. adCon.ConnectionString = cn_str

8. adCon.Open 9. End If

10. Exit Sub

11. opnCon_err_end:

12. MsgBox Err.Description 13. End Sub

(16)

4. 關閉連線公用程序

1. Public Sub closeCon()

2. If adCon.state = adStateOpen Then 3. adCon.Close

4. Set adCon = Nothing 5. End If

6. End Sub

(17)

9-2-2 開啟紀錄集

1. 開啟紀錄集指令語法

 紀錄集物件變數． Open 參數一，參數二，參數三，參數四

 紀錄集物件變數：預存記錄變數

 Open ： ADO 開啟物件方法

 參數一： SQL 指令，通常是由 Select 前導之敘述

 參數二：已開啟之連線物件變數

 參數三：紀錄集開啟模式常數，選項常數如表 9-1 所示

 參數四：讀寫模式常數，選項常數如表 9-2 所示

(18)

表 9-1 紀錄集開啟模式常數表

模式選項常數特性與用法

adOpenForwardOnly 紀錄只能往前捲動，不能倒捲用於只瀏覽但不修改資料

adOpenStatic 紀錄可以前後捲動，可開啟為唯讀或讀寫模式不反應同一紀錄集其他使用者所作之資料變更，

adOpenKeyset 此特性介於 Static 和 Dynamic 之間，適用於大型紀錄集但只有少數紀錄符合鍵值條件時；可偵測游標控制權內之紀錄變更，但無法正確反映其原來之順序；

他人所作之變更也無法偵測，除非重新開啟記錄集

adOpenDynamic 所有紀錄之變更均能立即偵測，但是連線物件設為交易模式時，他人所作之變更，必須在執行 Commit 指令之後，才能偵測得知

(19)

表 9-2 讀寫模式常數表

模式選項常數特性與用法

adLockReadOnly 不允許資料更新

adLockBatchOptimistic 批次更新，允許離線處理資料，允許他人同步存取資料；下達 BatchUpdate 指令後，資料才加以鎖定

adLockPessimistic 資料鎖定後，他人無法更新；下達 Edit 指令後，資料即鎖定

adLockOptimistic 允許他人更新資料；下達 Edit 指令時，資料不鎖定

，等到下達 Update 指令資料才被鎖定

(20)

2. 開啟紀錄集公用程序

1. Public Function openRs(rstr As String) AS ADODB.Recor dset

2. On Error Goto openRs_err

3. If adRs.state = adStateOpen Then adRS.Close

4. adRs.Open rstr, adCon, adOpenDynamic, adLockBatchO ptimistic

5. adRs.MarshalOptions = adMarshalModifiedOnly 6. Set adRs.ActiveConnection = Nothing

7. Set openRs=adRS 8. Exit Function

9. openRS_er:

10. Msgbox Err.Description

(21)

3. 關閉紀錄集

1. Public Sub closeRs()

2. If adRs.state = adStateOpen Then 3. adRs.Close

4. Set adRs = Nothing 5. End If

6. End Sub

(22)

9-2-3 資料處理程序範例

 建立新表單，表單取名為 ADO 客戶資料登錄作業

 表單格式如下

(23)

1. 連線及開啟資料表

 搭配 On Open 事件程序

1. Private Sub Form_Open(Cancel As Integer) 2. Dim str As String

3. Call checkCon(1)

4. str = ”Select * From cuinfo”

5. Call openRs(str) 6. End Sub

(24)

更新資料程序

 關閉表單時，搭配 On Close 事件程序將資料寫回伺服端

1. Private Sub Form_Close() 2. adRs.Filter = ""

3. Set adRs.ActiveConnection = adCon 4. adRs.Filter = adFilterPendingRecords 5. adRs.UpdateBatch

6. adRs.Close

7. Set adRs = Nothing 8. End Sub

(25)

新增紀錄

 搭配按鈕【新增】之 On Click 事件程序

指令： adRs ． Addnew ……

adRs ． Update

(26)

3. 查詢紀錄

 3.1 搜尋記錄指令語法

 指令：紀錄集‧ Filter = “ 比對欄位 ” “關係 運算子” “比對條件”

 其中關係運算子可指定為＝、＞＝、＜＝，或

＜＞（表示不等於）

 比對條件屬字串型資料時，須在資料前後加上引號；若屬日期型資料，須在資料前後加上

# 符號；數值型資料則可以直接串接

(27)

3. 查詢紀錄

^{( 續 )}

 範例： adRs.Filter = "CU_No ='" & Me!CU_No & "'“

 判斷未找到紀錄： adRs.EOF

 不支援 adRs.Nomatch 方法

 在搜尋記錄前，最好先執行 adRs.Filter=”” （或是 adRs .Filter=adFilterNone ），將前一次過濾之條件解除，否則會將前一次之搜尋結果當資料來源，因而造成部分資料漏失

(28)

Filter 方法過濾結果常數表

常數選項說明

adFilterAffectedRecords 選出最近以刪除、批次更新、取消更新處理過之紀錄

adFilterConflictingRecords 選出最近以批次更新處理失敗之紀錄

adFilterFetchedRecords 選出最近從資料庫取出並置於快取之紀錄

adFilterNone 移除最近使用之過濾條件並回複所有記錄

adFilterPendingRecords 取出最近被改變但尚未寫回伺服端之記錄，必須搭配批次更新指令

(29)

查詢程序應用範例

 搭配按鈕【查詢】之 On Click 事件程序

1. Private Sub 查詢 _Click()

2. adRS.Filter = “CU_No = ‘” & Me![CU_No] & “’”

3. If adRS.EOF Then

4. MsgBox " 未找到該筆紀錄“

5. Else

6. Me!CU_No = adRs!CU_No 7. …………. （加入其他欄位）

8.

9. End If 10. End Sub

(30)

4. 刪除記錄

• 指令

adRs.Delete

• 搭配按鈕【刪除】之 On Click 事件程序

(31)

5. 修改 / 儲存紀錄

• 指令

adRs.Update

（不需要搭配 adRs ． Edit ）

• 實作

– 搭配按鈕【儲存】之 On Click 事件程序，當需要編輯或修改特定紀錄時，先查詢紀錄，找到記錄後再加以編輯，然後按【儲存】鈕，將紀錄存檔

(32)

修改 / 儲存紀錄程序範例

1. Private Sub 儲存 _Click() 2. adRs.Filter = "“

3. adRs.Filter = "CU_No ='" & Me!CU_No & "'“

4. If Not adRs.EOF Then 5. With adRs

6. !CU_No = Me!CU_No 7. ……….

8. .Update 9. End With 10 End If

11 End Sub

(33)

6. 更新記錄

• 指令

adRs ． UpdateBatch

• 實作

搭配按鈕【更新】之 On Click 事件程序

(34)

更新記錄程序範例

1. Private Sub 更新 _Click() 2. adRs.Filter = "“

3. Set adRs.ActiveConnection = adCon 4. adRs.Filter = adFilterPendingRecords 5. adRs.UpdateBatch

6. Set adRs.ActiveConnection =Nothing 7. End Sub

(35)

9-2-4 批次更新、轉檔及刪除

1. 直接在連線物件執行 SQL 指令

 指令語法：

adCon ． Execute SQL 指令 2. 搭配交易處理方法

adCon.BeginTrans ‘ 開啟一個交易程序 adCon.Execute str ‘ 執行資料更新交易 adCon.CommitTrans ‘ 確認交易結果

(36)

9-2-4 批次更新、轉檔及刪除

^{( 續 )}

3. 交易過程發生錯誤，需要將所有記錄捲回原來狀態

指令語法：

adCon.RollBackTrans ‘ 捲回起始狀態

(37)

9-2-5 參數式查詢

 查詢處理方式，乃將伺服端的紀錄集分批下載至主控端，以便縮短擷取資料之等候時間

 處理指令如下述：

 宣告一個指令物件 (Command) 變數

Dim adCmd AS ADODB.Command （在表單程序檔開頭宣告）

 宣告一個 Parameter 物件變數

Dim qryPar AS ADODB.Parameter （在表單程序檔開頭宣告）

(38)

處理指令

( 續 )

 宣告一個紀錄集連線字串變數並設定初值

Dim str As String

 以下指令在表單 On Open 事件程序宣告

Str = “Select * From cuino Where CU_No = ? ”

 將紀錄集字串加入指令物件

adCmd.CommandText = str

 設定指令物件紀錄型態為指令型記錄

adCmd.CommandType = adCmdText

(39)

處理指令

( 續 )

設定指令物件為預儲參數

adCmd.Prepared = True

建立參數物件，取名為 cuno, 資料型態設為 adVarch ar ，寬度設為 255

Set qryPar =adCmd.CreateParameter(“cuno”,adVarchar, adParmInput, 255)

將參數物件加入指令物件

adCmd.Parameters.Append qryPar

(40)

處理指令

( 續 )

 將指令物件連線至連線物件

adCmd.ActiveConnection = adCon

 輸入參數值並開啟紀錄集

adCmd(“cuno”) = Me![CU_No]

 以下指令在查詢 On Click 事件程序宣告

Set adRs = adCmd.Execute

(41)

擷取方法比較

 此節使用之查詢指令適用於批次下載

 若一類資料筆數甚少時，批次下載每次查詢都須重新連線回伺服端，所以也不是很理想的查詢方式

 除了前述兩種查詢方式外，可將 SQL 指令加上 Where 子句來開啟範圍較小的紀錄集，然後搭配 Filter 方法來過濾資料

 採用方式請斟酌使用時機

(42)

9-2-6 紀錄及欄位操作指令說明

 紀錄及欄位操作指令與第八章第（五）節之說明類似

 ADO 不支援 DAO 物件之 Find 方法，必須改用 Filter 方法來查詢資料

(43)

9-3 DataGrid 物件簡介

9-3-1 DataGrid 物件應用 9-3-2 DataGrid 屬性說明

(44)

9-3-1 DataGrid 物件應用

 ADO 物件可以搭配 DataGrid 物件，直接瀏覽伺服端的紀錄集資料

 設計應用程式介面時，可在表單內插入一個 D ataGrid 控制項，然後將開啟的紀錄集連結至此控制項，即可瀏覽整個紀錄集的資料

(45)

DataGrid 物件應用範例

1. 建立表單：取名【 ADO 泛用查詢表單】，依序加入下列控制項

2. 組合方塊一：取名 tblDa ，標籤抬頭設為【資料表】，資料來源屬性欄輸入下列 SQL 指令：

Select Distinct table From sysTable Where database ='mys al';

3. 組合方塊二：取名 qryDa ，標籤抬頭設為【查詢表】，

在資料來源屬性欄輸入下列 SQL 指令，在格式屬性欄數輸入 2 （表需顯示兩個欄位）

Select qryID, qryName From qryTable Where database ='my sal';

(46)

DataGrid 物件應用範例

^{( 續 )}

4. 指令按鈕一：取名為 tblCmd ，標籤抬頭提示為【查閱資料表】

5. 指令按鈕二：取名為 qryCmd ，提示抬頭為【查閱查詢表】

6. DataGrid 控制項：取名為 dtlData ，

(47)

DataGrid 物件應用範例

^{( 續 )}

DataGrid 控制項加入步驟 如下

點選主功能表之【插

入】、【 ActiveX 控制項 (c) 】

下拉【 ActiveX 控制項】選項功能表，點選

【 Microsoft DataGrid Co ntrol, Version 6.0 】，然

(48)

DataGrid 物件應用範例

^{( 續 )}

7. 當表單出現 DataGrid 控制項後，請以滑鼠調整控制項大小至適當尺寸，將控制項取名為 dtl Data

8. 點開【表單】事件屬性之 On Open 程序編輯視窗，加入下列指令，此程序用於開啟連線物件

Private Sub Form_Open(Cancel As Integer) Call checkCon(1) ‘ 檢查連線狀態

End Sub

(49)

DataGrid 物件應用範例

^{( 續 )}

9. 點開【查閱資料表】事件屬性之 On Click 程序編輯視窗，加入下列指令：

Private Sub tblCmd_Click() Dim str As String

If IsNull(tblDa) Then Exit Sub

str = "Select * From " & Me!tblDa & ";"

Call openRs(str)

Set dtlData.DataSource = adRs End Sub

(50)

DataGrid 物件應用範例

^{( 續 )}

10. 點開【查閱查詢表】事件屬性之 On Click 程序編輯視窗

，加入下列指令：

Private Sub qryCmd_Click() Dim str As String, rstr as String Dim rsq As Recordset

Dim dbs As Database

If IsNull(qryDa) Then Exit Sub

rstr="Select * from qryTable Where qryID=qryDa ; Set dbs = CurrentDb

Set rsq = dbs.OpenRecordset(rst) str = rsq!qrySQL

Call openRs(str)

Set dtlData.DataSource = adRs

(51)

DataGrid 物件應用範例

^{( 續 )}

11. 點開【表單】事件屬性之 On Close 程序編輯視窗，然後加入下列指令，此程序用於關閉紀錄集

Private Sub Form_Close(Cancel As Integer) Call checkRs

End Sub

(52)

測試結果

(53)

9-3-2 DataGrid 屬性說明

1. DataSource ：設定連結資料來源語法：

Set 控制項物件名稱． DataSource = 紀錄集物件

2. Columns ：控制項欄位集合

語法：控制項物件名稱． Columns(i) ， i 從 0 起算

3. Columns(i) ． Value ：控制項欄位資料

取值：變數＝控制項物件名稱． Columns(i) ． Val ue

存值：控制項物件名稱． Columns(i) ． Value ＝變數

(54)

9-3-2 DataGrid 屬性說明

^{( 續 )}

4. Columns(i) ． Width ：控制項欄位寬度

語法：控制項物件名稱． Columns(0) ． Width ＝ 23 0 （以 twis 為單位）

5. Columns(i) ． Caption ：控制項欄位抬頭

語法：控制項物件名稱． Columns(0) ． Caption ＝

”客戶編號”

6. HeadFont ． Size ：控制項欄位抬頭字體大小

語法：控制項物件名稱． HeadFont ． Size ＝ 10

(55)

9-3-2 DataGrid 屬性說明

^{( 續 )}

7. Font ． Size ：控制項欄位資料字體大小

語法：控制項物件名稱． Font ． Size = 10

8. AllowAddNew/ AllowDelete/ AllowUpdate ：允許新增 / 刪除 / 更新

語法：控制項物件名稱． AllowAddNew = True

控制項物件名稱． AllowDelete = False

9. AllowArrows ：方向鍵移動屬性

語法：控制項物件名稱． AllowArrows = True|False

= True ，允許以方向鍵在欄位間或紀錄間移動

=False ，方向鍵用於控制項之移動

(56)

9-3-2 DataGrid 屬性說明

^{( 續 )}

10. WrapCellPointer ：方向鍵移動捕捉屬性

語法：控制項物件名稱． WrapCellPointer = True|False = True ，方向鍵在控制項內之紀錄間移動

= False ，方向鍵無法在上下紀錄間移動

11. TabAction ： Tab 鍵屬性

語法：控制項物件名稱． TabAction = 0|1|2

= 0 (dbgControlNavigation) ， Tab 鍵用於控制項之移動 )

=1 (dbgColumnNavigatio) ， Tab 鍵用於欄位間移動，當移動會造成紀錄改變時，則移至下一個控制項

=2 (dbgGridNavigation) ，和 1 類似，但有設定 WrapCellPoint er 時，則不移至其它控制項。

(57)

9-4 進階應用

9-4-1 在 Access 執行專案檔 9-4-2 以 Email 傳送資料表

相關程序請參看書本說明

(58)

9-4-1 在 Access 執行專案檔

(59)

9-4-2 以 Email 傳送資料表

(60)

SendObject 巨集指令

 SendObject 巨集指令，可以將指定的 Access 資料工作表、查詢、表單、報表、模組、或資料頁包含在電子郵件訊息中，以供檢視及轉寄

 郵件傳送引擎可以搭配 Outlook 、 Microsoft Exchang e 或其他使用 Mail Applications Programming Interfa ce (MAPI) 的電子郵件應用程式

 傳送的資料格式可以是 Excel 2000 (*.xls) 、文字檔 (*.txt) 、 RTF 格式 (*.rtf) 或 HTML (*.html) 格式的物件

(61)

SendObject 巨集指令

^{( 續 )}

 SendObject 巨集指令之語法

語法： DoCmd.SendObject 參數一 , 參數二 , 參數三 , 參數四 , 參數五 , 參數六 , 參數七 , 參數八 , 參數九

 參數一：傳送資料物件型式，可以是資料表、

查詢、表單、報表、模組、資料頁、或函數

 參數二：傳送資料物件名稱

 參數三：傳送資料格式，可以是 acFormatXLS

、 acFormatTXT 、 acFormatHTML 、或 acFo rmatRTF 之選項常數

(62)

SendObject 巨集指令

^{( 續 )}

 參數四：收件者

 參數五：副本

 參數六：密件副本

 參數七：主旨

(63)

SendObject 巨集指令 ( 續 )

 參數八：附加信息

 除了資料庫物件外，任何您想要含入訊息中的文字

 此文字會出現在郵件訊息的主體中，並置於資料物件的後面

 如果讓此引數保留空白，則郵件訊息內不會含入額外的文字

 如果讓 [ 物件類型 ] 和 [ 物件名稱 ] 引數保留空白，則可以使用此引數來傳送一個沒有資料物件的郵件

(64)

SendObject 巨集指令 ( 續 )

 參數九：指定訊息在傳送之前是否可以編輯

 如果選取 [ 是 ] （ -1 ），則電子郵件應用程式會自動啟動，並可編輯訊息

 如果選取 [ 否 ] （ 0 ），訊息即被傳送，但不提供編輯該訊息的機會

 系統預設值是 [ 否 ]

第 9 章 ADO 物件應用和程式設計

第 9 章 ADO

物件應用和程式設計

本章宗旨

大 綱

9-1 ADO 簡介

9-1-1 ADO 基礎

9-1-1 ADO 基礎

ADO 用途

9-1-2 ADO 物件結構

9-1-3 前置處理

9-2 ADO 指令簡介

9-2-1 資料連結

以連線物件連結範例

以連線物件連結範例

以連線物件連結範例

3. 開啟連線公用程序

4. 關閉連線公用程序

9-2-2 開啟紀錄集

9-2-3 資料處理程序範例

1. 連線及開啟資料表

更新資料程序

新增紀錄

3. 查詢紀錄

3. 查詢紀錄

查詢程序應用範例

4. 刪除記錄

5. 修改 / 儲存紀錄

修改 / 儲存紀錄程序範例

6. 更新記錄

更新記錄程序範例

9-2-4 批次更新、轉檔及刪除

9-2-4 批次更新、轉檔及刪除

9-2-5 參數式查詢

處理指令

處理指令

處理指令

擷取方法比較

9-2-6 紀錄及欄位操作指令說明

9-3 DataGrid 物件簡介

9-3-1 DataGrid 物件應用

DataGrid 物件應用範例

DataGrid 物件應用範例

DataGrid 物件應用範例

DataGrid 物件應用範例

DataGrid 物件應用範例

DataGrid 物件應用範例

DataGrid 物件應用範例

測試結果

9-3-2 DataGrid 屬性說明

9-3-2 DataGrid 屬性說明

9-3-2 DataGrid 屬性說明

9-3-2 DataGrid 屬性說明

9-4 進階應用

9-4-1 在 Access 執行專案檔

9-4-2 以 Email 傳送資料表

SendObject 巨集指令

SendObject 巨集指令

SendObject 巨集指令

SendObject 巨集指令 ( 續 )

SendObject 巨集指令 ( 續 )

大綱