Access傳遞查詢訪問Sql Server數據庫存儲過程（Store procedure)的詳細教程

Access傳遞查詢訪問Sql Server數據庫存儲過程（Store procedure)的詳細教程

---

傳遞查詢與一般的Access查詢類似，但是在傳遞查詢的使用中隻使用事務SQL(Sql Server使用有SQL)，所以在Access中不能圖形化地建立傳遞查詢，而隻能手工鍵入所有的SQL語句。

傳遞查詢有兩部分組成：以SQL寫成的命令字符串和ODBC連接字符串。

SQL字符串包含一箇或多箇事務SQL語句，或者包含一箇SQL程序流程控製語句的複雜過程，還可調用存在於Sql Server上的存儲過程。

ODBC連接字符串來標識命令字符串將要髮送的數據源，連接字符串也可包括指定Sql Server的用戶登録信息。

1）首先看看在鏈接錶上運行查詢的複雜步驟：

1、 jet數據庫引擎分析SQL字符串。

2、 jet數據庫引擎編譯SQL字符串併決定哪一部分髮往Sql Server。

3、 jet數據庫引擎創建一箇事務SQL命令字符串併將宻牠髮往Sql Server。

4、 Sql Server分析和編譯事務SQL命令字符串。

5、 Sql Server完命令字符串定義的任務，如果有返迴值的話，則曏jet數據庫引擎返迴結果記録。

1、 傳遞查詢可以使用Sql Server的固有函數和存儲求過程。

2、 傳遞查詢可以記録Sql Server返迴的警告和統計信息。

存儲過程是用一箇或多箇事務SQL編寫的編譯後存儲在Sql Server上的一段程序。存儲過程與DOS的批處理文件類似，是一些一起批量運行的多箇命令。

例如：下麵的過程首先創建一箇名叫#MyTemp的臨時錶，在此錶中插入一對日期，然後返迴#MyTemp和訂單錶 連接的記録。

Create procedure proc1 As

Create Table #MyTemp(開始時間 DATETIME NOT NULL,結束時間 DATETIME NOT NULL)

Insert Into #MyTemp Value (‘1/1/2009’, ‘12/31/2009’)

Select 訂單.* from 訂單, #MyTemp

where 訂單.訂單時間>=#MyTemp.開始時間 AND 訂單.訂單時間<=#MyTemp.結束時間

需要註意的是在事務SQL中，名字中有“#”“##”的錶均是臨時錶。

名字中有“#”的臨時錶是本地錶，在一箇對話終止後牠們被自動丟棄。在上例中錶#MyTemp僅在存儲過程的執行過程中纔存在。

名字中有“##”的臨時錶是全局的，而且對所有連接都有效，一箇全局臨時錶在使用該錶的最後一箇對話終止時被丟棄。

存儲過程也可以接受變量作爲輸入蔘數。下麵的例子有兩箇輸入變量，在事務SQL中所有變量的名必鬚以@開頭。

Create procedure proc2 @startdate datetime,@enddate datetime as

Select 訂單.* from 訂單

where 訂單.訂單時間>=@startdate AND 訂單.訂單時間<=@enddate

1）傳遞查詢可以調用Sql Server的存儲過程，方法是在傳遞查詢的SQL命令字符串中加入存儲過程的名字，當SQL字符串髮到Sql Server後存儲過程被執行。

傳遞查詢調用存儲過程時隻要使用EXECUTE（也可簡寫爲EXEC）就可以調用存儲過程瞭。

2） 傳遞查詢也可以輸齣蔘數。例如，假設有一箇存儲過程myproc接受兩箇字符串爲輸入蔘數併返迴一箇字符爲三箇字符長度的字符串爲輸齣蔘數。

下麵的代碼執行myproc將結果存入名爲@outputparameter的變量作爲輸齣蔘數，併返迴@outputparameter的值。

3） 傳遞查詢中如果包含多箇存儲過程時要返迴多箇過程的數據，但是在運行這箇查詢時牠産生的錶格視圖僅顯示第一箇存儲過程産生的記録。

想看到所有返迴記録的唯一方法是在Access中使用生成錶查詢，牠將接受傳遞查詢的結果併存放到Access本地錶中。

傳遞查詢有一箇最大的缺限就是沒有蔘數查詢功能，不能提示輸入蔘數。這時就需要使用VBA來解決這箇問題。

1）下例用VBA創建一箇傳遞查詢：

dim mydb as database            ‘定義數據庫對象變量
dim myquery as querydef         ‘定義查詢對象變量
set mydb=currentdb()            ‘設置數據庫對象變量爲當前數據庫
set myquery=mydb.createquerydef(“test”)   ‘創建名爲test的查詢
myquery.connect=” ODBC;DRIVER=SQL Server;_
SERVER=127.0.0.1\EXPRESS;UID=sa;PWD=system;DATABASE=cwbase1”’設置查詢爲傳遞查詢併定義連接字符串
myquery.sql=”select * from zwkmzd”’設置SQL語句,相當於傳遞查詢窗口中的語句
myquery.returnsrecords=true         ‘返迴記録爲”真” Office交流網(http://www.office-cn.net)
docmd.openquery “test”            ‘運行這箇查詢,有數據窗口提供數據結果

2）修改一箇已經存在的傳遞查詢

dim mydb as database            ‘定義數據庫對象變量
dim myquery as querydef         ‘定義查詢對象變量
set mydb=currentdb()            ‘設置數據庫對象變量爲當前數據庫
set myquery=mydb.querydef(“test”) ‘設名爲test的查詢
myquery.sql=”select * from zwpzk”’重新設置SQL語句,相當於傳遞查詢窗口中的語句
myquery.returnsrecords=true         ‘返迴記録爲”真”
myquery.execute         ‘運行這箇查,等價docmd.openquery “test”,但execute屬性隻能用運作查詢

3）曏傳遞查詢傳遞蔘數

創建窗體,在窗體中建立一箇文本框和一箇按鈕,在按鈕的click事件中輸入以下代碼:

dim mydb as database            ‘定義數據庫對象變量
dim myquery as querydef         ‘定義查詢對象變量
set mydb=currentdb()            ‘設置數據庫對象變量爲當前數據庫
set myquery=mydb.createquerydef(“test”)   ‘創建名爲test的查詢  Office交流網(http://www.office-cn.net)
myquery.connect=” ODBC;DRIVER=SQL Server;_
SERVER=127.0.0.1\EXPRESS;UID=sa;PWD=system;DATABASE=cwbase1”’設置查詢爲傳遞查詢併定義連接字符串
myquery.sql=”exec myproc ’” & me![窗體文本框的值] & “’”’設置SQL語句,相當於傳遞查詢窗口中的語句。當然也可以用SQL語句。
myquery.returnsrecords=true         ‘返迴記録爲”真”
docmd.openquery “test”            ‘運行這箇查詢,有數據窗口提供數據結果

dim mydb as database            ‘定義數據庫對象變量
dim myquery as querydef         ‘定義查詢對象變量  Office交流網(http://www.office-cn.net) set mydb=currentdb()            ‘設置數據庫對象變量爲當前數據庫
set myquery=mydb.querydef(“test”) ‘設名爲test的查詢
myquery.sql=”exec myproc ’” & me![窗體文本框的值] & “’”’重新設置SQL語句,相當於傳遞查詢窗口中的語句。當然也可以用SQL語句。
myquery.execute         ‘運行這箇查,等價docmd.openquery “test”