Office Space:撰寫 Microsoft Office 應用程式指令碼的秘訣

Office Space

歡迎蒞臨《Office Space》專欄,這裡提供撰寫 Microsoft® Office 應用程式指令碼的秘訣。每週四我們將刊登新的秘訣,若要參閱以前討論過的秘訣,請造訪 Office Space 過往文件。如果您有關於 Microsoft Office 指令碼方面的問題,請將電子郵件傳送到 scripter@microsoft.com。我們無法保證能夠逐一回答每個問題,不過我們會盡力而為。

從 Microsoft Access 資料庫讀取資料

每個家族總會出一個奇特人物,這與好或壞沒關係,純粹就是與眾不同。有可能是一群矮頭中出個長腿的,或是明明吃素又跑去開烤肉店。或是書蟲家族裡出了一個優秀的運動員,或 Scripting Guys 裡頭出了一個特別勤奮的人物。不管怎樣,每個家族裡總有一個不一樣的人物,不禁讓人問「那是你姊姊喔?想都想不到。」

Microsoft Office 產品家族中最不一樣的要算 Microsoft Access。其他的 Office 應用程式,尤其是 Word 和 Excel,都可以透過指令碼以程式的方式在文件內新增資料,唯一的缺點就是您必須使用專屬物件模型和專屬方法。這不表示作法就很困難,而是您得為了 Word 和 Excel 各學兩個物件模型:畢竟知道如何將資料新增到 Word 文件,不表示可以依樣畫葫蘆將資料新增到 Excel 試算表。

但是 Microsoft Access 就不一樣啦。Access 有自己的物件模型,但是如果只是要進行讀取或寫入資料表等基本的資料庫作業,那連物件模型都派不上用場。只要使用 ActiveX Data Objects (ADO) 就可以了,事實上那還不賴。為什麼?因為 ADO 是一種標準的程式設計模型,可以使用任何資料庫類型。您使用 SQL Server 資料庫嗎?沒問題,Access 使用的指令碼也可以用在 SQL Server,很可能連調整都不用。那 Oracle 資料庫呢?一樣,標準的 ADO 程式碼可以存取 Oracle 資料庫。Visual FoxPro?DB2?AS 400?MySQL?我想您已經猜到了。ADO 也可以與這些資料庫互動,而且不只如此。

所以我們今天打算深入介紹 ADO,尤其是如何在 Access 資料庫使用 ADO。先從基本的著手,介紹怎麼存取 Access 資料庫並擷取資訊,然後說明新增記錄、修改現有記錄、建立和刪除資料表等其他工作。

附註:本文假設讀者已經有一個名為 C:\Scripts\Inventory.mdb 的資料庫 (您也可以自行建立,很簡單),並假設資料庫有個名為 GeneralProperties 的資料表,裡面有個欄位叫 ComputerName。如果您不知道「資料表」或「欄位」是什麼,這篇文章對您來說應該沒什麼用途。讀者必須具備基本的資料庫觀念,才能瞭解今天的專欄。

順帶一提,日後我們將介紹如何以程式建立出資料庫、資料表和欄位,今天就先跳過。


咱們先來看看一個可以從 Microsoft Access 資料庫讀取資料的指令碼,它可以連接到 C:\Scripts\Inventory.mdb 檔案,從名為 GeneralProperties 的資料表擷取資料,然後回報資料表內每筆記錄中 ComputerName 欄位的值。


On Error Resume Next

Const adOpenStatic = 3
Const adLockOptimistic = 3

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

objConnection.Open _
    "Provider=Microsoft.Jet.OLEDB.4.0; " & _
        "Data Source=c:\scripts\inventory.mdb" 

objRecordSet.Open "SELECT * FROM GeneralProperties" , _
    objConnection, adOpenStatic, adLockOptimistic

objRecordSet.MoveFirst

Do Until objRecordSet.EOF
    Wscript.Echo objRecordSet.Fields.Item("ComputerName")
    objRecordSet.MoveNext
Loop


指令碼很少 (而且裡頭大部分都是現成的指令碼,永遠不用修改。)指令碼有些部分雖然看起來複雜,您不用擔心,我們將為讀者一一解說指令碼的運作原理,

沒問題,畢竟我們薪水不是白拿的。

指令碼先定義 adOpenStatic 和 adLockOptimistic 這兩個常數,並將值分別設為 3。

常數 adOpenStatic 是用來判斷資料錄集的「游標類型」。稍微解釋一下,游標類型決定您使用資料錄集的方式,以及資料錄集是會否動態回應資料庫內的變更 (假設您從資料庫擷取資料後,又有人在資料庫新增一筆記錄。這一筆記錄是否會動態地出現在您的資料錄集內,或您必須重新查詢資料庫才能取得更新資訊?)

adOpenStatic 游標可讓您在資料錄集內前後游動,但本身並不會在資料庫內容變更時自動更新。這樣可以減少處理時間和網路流量,因為擷取資料錄集後,指令碼就可以在一旁「納涼」,不用一直監看資料庫的變更。

游標類型、相關常數和數值如下表所示:

游標類型

常數

說明

Forward-only

adOpenForwardOnly

0

只能在資料錄集內以順向移動。不能用來尋找個別記錄,也不會傳回資料錄集中的記錄筆數。

Keyset

adOpenKeyset

1

可讓您在資料錄集內上下捲動。支援 Find 和傳回記錄筆數。可動態回應現有記錄內的變更,但不會回應建立新記錄。

Dynamic

adOpenDynamic

2

可讓您在資料錄集內上下捲動。支援 Find 和傳回記錄筆數。動態回應資料錄集中的所有變更。

Static

adOpenStatic

3

可讓您在資料錄集內上下捲動。支援 Find 和傳回記錄筆數。不會動態回應資料錄集中的所有變更。


還有一個常數 adLockOptimistic 可以用來設定記錄的「鎖定類型」。鎖定類型可決定資料庫如何或是否要鎖定目前檢視的記錄,給您修改該記錄的獨佔權。鎖定類型、相關常數和數值如下表所示:

鎖定類型

常數

說明

Read Only

adLockReadOnly

1

透過避免記錄鎖定來釋放系統資源。但是會造成唯讀的資料錄集。

Pessimistic

adLockPessimistic

2

在您編輯記錄的同時將之鎖定,直到您呼叫 Update 方法才解除鎖定。

Optimistic

adLockOptimistic

3

只會在呼叫 Update 方法時暫時鎖定記錄。

Batch

adLockOptimisticBatch

4

用於批次更新。


定義常數後,下一步要建立一組物件:ADODB.Connection 物件和 ADODB.Recordset 物件。這兩個物件都是恰如其名:Connection 物件主要用來管理和維護與資料庫的連線,而 Recordset 物件則是一個容器,用來放置查詢傳回的資料。建立這兩個物件很簡單,只要執行下面這行程式碼:


Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")


Connection 物件準備好後,就可以連接到資料庫。今天的文章要使用一個檔案路徑繫結至資料庫,日後的文章再教您如何使用 DSN (資料來源名稱) 繫結到資料庫。現在給各位介紹連接到 C:\Scripts\Inventory.mdb 資料庫的指令碼:


objConnection.Open _
    "Provider=Microsoft.Jet.OLEDB.4.0; " & _
        "Data Source=c:\scripts\inventory.mdb"


怎樣,很簡單吧!這裡只不過呼叫了 Open 方法並傳給它一組參數:Provider 的名稱 (Microsoft.Jet.OLEDB.4.0) 和 Data Source 的名稱 (本例中為檔案路徑):執行指令碼後,您就會連上 Inventory.mdb 資料庫:

連線後就可以發出 SQL 查詢與資料庫互動。先拿一個簡單的查詢開頭,我們選取所有來自 GeneralProperties 表格的屬性,SQL 查詢應該這麼寫:


SELECT * FROM GeneralProperties


接著使用查詢搭配 Recordset 物件和 Open 方法:


objRecordSet.Open "SELECT * FROM GeneralProperties" , _
    objConnection, adOpenStatic, adLockOptimistic


正如您所看到,我們指定了四個參數:

  • SQL 查詢 (沒錯,除了 Select 查詢,您還可以使用 Update、Insert Into 等其他查詢類型。)

  • 資料庫連接的物件參考 (objConnection)。

  • 想使用的游標類型 (以 adOpenStatic 常數指定要使用靜態游標)。

  • 想使用的鎖定類型 (以 adLockOptimistic 常數指定要使用開放式鎖定)。

該命令發出後,傳回的資料會以 Recordset 物件儲存在記憶體中。 這時候需兩個動作來處理傳回的資料集合:呼叫 MoveFirst 方法,然後設定 Do Until 迴圈逐一處理資料錄集內的所有記錄。

我們使用 MoveFirst 方法將游標放在資料錄集的開端。老實說您應該不用做這個動作,其實游標應該已經自動放在資料錄集的開端了,但是還是以防萬一。

順便一提,「把游標放在資料錄集的開端」表示放在集合內的第一筆記錄:該游標可用來追蹤您在資料錄集內的位置。這裡的動作不過是要確定待會在集合內運作時,是從第一筆記錄開始。如果基於某些原因要存取資料錄集的最後一筆記錄,請呼叫 MoveLast 方法。當然啦,您也可以使用 Find 方法快速找出資料錄集內的任何一筆記錄。先煞車一下,超出範圍了。

Scripting Guy 的秘訣:依預設,記錄以哪種順序加入資料庫,就會以哪種順序傳給我們。傳回的資料絕對不會經過排序。

但您若真的想排序,方法是這樣的,只要加上一個 ORDER BY 子句,指定要排序的欄位名稱和排序類型 (ASC 代表遞增順序,DESC 代表遞減順序)。例如,SQL 查詢傳回了以遞增 (A 到 Z) 順序傳回排序過的 Manufacturer 資料。

SELECT * FROM GeneralProperties ORDER BY Manufacturer ASC

這行則是先依 Manufacturer,再依 ComputerName 將資料排序:

SELECT * FROM GeneralProperties ORDER BY Manufacturer ASC, ComputerName ASC


下一步要建立一個 Do Until 迴圈,逐一循環處理集合內的每筆記錄。


Do Until objRecordSet.EOF
    Wscript.Echo objRecordSet.Fields.Item("ComputerName")
    objRecordSet.MoveNext
Loop


這是程式設計新手最容易出錯的地方,有幾個重點要記住。

要拿紙記下來嗎?慢慢來沒關係,我們可以等。可是不好意思喔,我們沒有足夠的鉛筆提供給大家,所有如果有多的,要傳出來啊。

準備好了嗎?好,開始先注意 Do Until 迴圈跑到 objRecordset.EOFEOF 是 「End Of File」的縮寫,代表我們得從資料錄集的第一筆記錄開始,直到跑遍所有的記錄,指令碼就會自動離開迴圈。

這裡要注意一點,如果您嘗試處理的資料錄集沒有包含任何項目,ADO 會產生錯誤。 您可以使用 On Error Resume Next 陳述式忽略這個錯誤,也可以選擇在進入 Do Until 迴圈前,先檢查 RecordCount 屬性的值 (也就是資料錄集內的記錄筆數)。如果 RecordCount 等於 0,表示資料錄集內沒有任何記錄,就沒有必要跑 Do Until 迴圈。所以要根據 RecordCount 的值作不同的反應,指令碼應該這樣改:


If objRecordset.RecordCount <> 0 Then
    Do Until objRecordSet.EOF
        Wscript.Echo objRecordSet.Fields.Item("ComputerName")
        objRecordSet.MoveNext
    Loop
End If


這裡是另一個重點。如果您寫過很多 WMI 或 ASDI 指令碼,應該很常使用 For Each 迴圈來處理集合。For Each 迴圈很好用,因為它很聰明。For Each 迴圈「知道」集合內每一個項目都要跑過,例如,這是跑過 colServices 集合並回應每項服務的名稱所需的全部指令碼:


For Each objService in colServices
    Wscript.Echo objService.Name
Next


Do Until 迴圈不太聰明,但還是得用它,因為 For Each 迴圈無法處理傳回的資料錄集。請記住,Do Until 不會自動跑遍資料錄集內的每一筆記錄。您必須包含一個程式碼,告訴指令碼要移動到下一筆記錄,這正是 MoveNext 方法的作用所在:


objRecordSet.MoveNext


如果不加這行程式碼會怎樣?指令碼會從第一筆記錄開始,到達 Loop 陳述式,跑迴圈,然後又再處理第一筆記錄。又到達 Loop 陳述式,跑迴圈,然後又再處理第一筆記錄,如此週而復始。也就是說,您一定要在迴圈跑完前呼叫 MoveNext 方法,才能夠處理資料錄集內的每一筆記錄。

最後要瞭解如何在 Do Until 迴圈內參考資料庫欄位。


Wscript.Echo objRecordSet.Fields.Item("ComputerName")


如您所見,不能光回應欄位名稱,這樣行不通:


Wscript.Echo ComputerName


我們得提供物件的完整路徑:objRecordSet.Fields.Item("ComputerName")。看起來有點複雜,但別忘了大部分內容都是現成的,您只要修改欄位名稱:Wscript.Echo objRecordSet.Fields.Item("ComputerName")。要多打幾個字,但和只回應欄位名稱一樣簡單。

現在請到命令提示執行指令碼,然後視您資料庫內儲存的記錄,您會收到一組電腦名稱:


atl-ws-01
atl-ws-02
atl-ws-03
atl-ws-04
atl-ws-05


漂亮吧?

我們知道讀者在想什麼:忙了這麼久,只不過是要讀取資料庫內的幾個電腦名稱?我承認,今天學的不是什麼了不起的東西。但也是因為我們想多解釋一些基本概念,何謂游標和鎖定類型、怎麼在資料錄集中移動,還有怎麼參考資料錄集裡的欄位。解決了這些事情,就可以開始玩啦!

不好意思,我指的是玩「資料庫」。但這也挺有趣的,不是嗎?

顯示: