傳遞查詢的例子-在ACCESS中通過傳遞查詢調用Sql server后臺存儲過程

問題（網(wǎng)友：Tom_HYQ）

我希望學學Access的傳遞查詢，可惜MS的help上沒有相關的例子，請高手們多多指教了。
謝謝。

解答（ zhengjialon）：
在ACCESS中使用傳遞查詢調用后臺存儲過程  

周杰 提供

    ACCESS是一個Client/Server的優(yōu)秀前端開發(fā)工具，具有易學易用，界面友好，開發(fā)簡單，和其他數(shù)據(jù)庫接口靈活。但是，它要對大量數(shù)據(jù)處理時，速度比較慢。深圳供電局現(xiàn)有60萬用戶，下面有6個營業(yè)所，通過64K DDN 和局相連，如果有一個營業(yè)所要對1萬用戶進行處理的話，要花2~3小時，嚴重影響其他業(yè)務的開展。所以，當有大量數(shù)據(jù)需要處理時，不能在Client端處理，而必須在Server端處理。 但ACCESS和Server端之間多數(shù)通過ODBC來連接，這樣就增加了調用后臺存儲過程的難度。筆者通過在實際工作中長期的摸索，根據(jù)不同的業(yè)務需要，可以用下面三種方法去調用后臺存儲過程。 

一、 Access 向后臺提交作業(yè)，這一個個作業(yè)對應一個個的存儲過程。在SQL Server 6.5中，通過Task Manager 來建立相應的Task; 而在Oracle 8.0中，通過Managing Job Queue 來建立相應的Job。在Access中，在job_list表中插入一個作業(yè)，這些作業(yè)一般每天晚上運行一次。這些作業(yè)對應的業(yè)務一般是需要處理非常大量數(shù)據(jù)，而實時性又是較低的，在我們的業(yè)務中比較典型的例子是每天晚上大量的電費計算。 

二、 在Server端建立一些觸發(fā)器（Trigger），在Access中激活這些觸發(fā)器。在SQL Server 6.5 和Oracle 8.0中，都是通過Create Trigger 來實現(xiàn)。在Access中，根據(jù)不同的業(yè)務，通過插入、修改、刪除記錄來激活不同觸發(fā)器。在我們的業(yè)務中一個例子是，當業(yè)務人員修改用戶的電表讀數(shù)后，激活計算電費的觸發(fā)器，能立即重新計算該用戶的電費。這樣子，既可以和批量計算電費共享同一程序，不用在Access端重新開發(fā)，有可以加快前端的響應速度。 

三、 以上二種方法，只可以說是間接調用后臺存儲過程，Access還提供一種直接調用的方法，可以用Access 傳遞查詢直接將命令發(fā)送到數(shù)據(jù)庫服務器。 

建立Access 傳遞查詢步驟如下： 

(1) 在數(shù)據(jù)庫窗口中單擊“查詢”選項卡，然后單擊“新建”按鈕。 

(2) 在“新建查詢”對話框中單擊“設計視圖”選項，然后單擊“確定”按鈕。 

(3) 在“顯示表”對話框內單擊“關閉”按鈕。 

(4) 在“查詢”菜單上，指到“SQL 語句條件”，然后單擊“傳遞”命令。 

(5) 在工具欄上，請單擊“屬性”按鈕 以顯示查詢屬性表。 

(6) 在查詢屬性表中，請設置“ODBC 連接字符串”屬性來指定要連接的數(shù)據(jù)庫信息?？梢暂斎脒B接信息: ”O(jiān)DBC;DSN=ntserver0;UID=sa;PWD=;DATABASE=BMS”，或用“生成器”按鈕生成。 

(7) 因存儲過程不用返回記錄，所以將“返回記錄”屬性設置為“否”。 

(8) 在“SQL 傳遞查詢”窗口中，輸入傳遞查詢: exec statistics。Statistics 是SQL Server 數(shù)據(jù)庫的一個存儲過程。 

(9) 關閉查詢，保存查詢?yōu)椋簊tat_query。 

運行傳遞查詢的方法有： 

(1) 用Microsoft Access的宏 OpenQuery。 

(2) 用下面的事件過程： 

Private Sub 統(tǒng)計_Click() 
Dim dbs As Database, qdf As QueryDef 
Dim tmq As Dynaset 
Dim strSQL As String 

Set dbs = CurrentDb 
'打開上面建的傳遞查詢stat_query 
Set qdf = dbs.OpenQueryDef("stat_query ") 
qdf.Execute 
Set dbs = Nothing 
End Sub 

這種方法在SQL Server 6.5 中實現(xiàn)完全沒問題，在Oracle 8.0中不能實現(xiàn)，錯誤信息為：ORA-00900 invalid SQL statement. 由于這種方法不需要一個中間的表去排隊或激活后臺存儲過程，所以它的應用面就很廣，凡是需要處理大量數(shù)據(jù)的業(yè)務，都可以放到Server端去處理，如統(tǒng)計報表。 

通過以上三種方法，解決了Access對大量數(shù)據(jù)處理速度慢的弱點，大大加大了深圳供電局數(shù)據(jù)處理能力。 
 
zhengjialon
本示例使用直接傳遞查詢從 Microsoft Access 版本的 Northwind 示例數(shù)據(jù)庫中檢索結果集。

SELECT *
FROM OpenRowset('Microsoft.Jet.OLEDB.4.0', 
   'c:\northwind.mdb';'admin'; '', 
   'SELECT CustomerID, CompanyName
         FROM Customers
         WHERE Region = ''WA'' ')

Tom_HYQ
謝謝版主，看了你的幫助，我有了一點認識

是不是可以這樣理解：(我說得不對，請糾正)
傳遞查詢主要用于通過access向后臺數(shù)據(jù)庫發(fā)命令，讓數(shù)據(jù)庫進行相關操作，把相關的數(shù)據(jù)集返回到access中。因為access只是發(fā)送命令而沒有必要與后臺數(shù)據(jù)庫進行數(shù)據(jù)訪問，所以速度比較快。

我有幾點不明白
有沒有一個例子是返回記錄集的？(如果想訪問后臺數(shù)據(jù)庫以返回記錄，用傳遞查詢合適嗎？還是用odbc鏈接表的方式更有效？)

直接使用你的第二個例子時，access出錯，說不能返回一個MS jet的數(shù)據(jù)庫，是什么回事？

zhengjialon
第二個示例可能不能在ACCESS中運行 

(相關查詢-相關文章技巧鏈接)：
ACCESS與SQL SERVER系列之二—MSDE部署-免費Access培訓公開課[4]
在 Access 中使用“存儲過程”(一)
Access無需DSN文件快速連接SQL Server的方法!