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

2017-07-13 22:10:00
zstmtony
轉貼
2416
Access傳遞查詢訪問Sql Server數據庫存儲過程(Store procedure)的詳細教程

Access檢索和操作Sql Server數據有一種方法叫作pass-throught查詢,也就是所説的傳遞查詢。傳遞查詢剋服瞭jet數據庫扮演Access與Sql Server之間協衕工作的翻譯編譯角色的缺點。Access可以通過傳遞查詢直接把事務SQL命令髮往Sql Server,而不是讓jet數據引擎指導Access析數據請求怎樣在Sql Server上運行。在Access是雖然沒有在鏈接錶上運行查詢直觀,但是圖形界麵的缺憾卻換來瞭傳遞查詢中更多的控製和更好的理解。


一、認識傳遞查詢


傳遞查詢與一般的Access查詢類似,但是在傳遞查詢的使用中隻使用事務SQL(Sql Server使用有SQL),所以在Access中不能圖形化地建立傳遞查詢,而隻能手工鍵入所有的SQL語句。
傳遞查詢有兩部分組成:以SQL寫成的命令字符串和ODBC連接字符串。
SQL字符串包含一箇或多箇事務SQL語句,或者包含一箇SQL程序流程控製語句的複雜過程,還可調用存在於Sql Server上的存儲過程。
ODBC連接字符串來標識命令字符串將要髮送的數據源,連接字符串也可包括指定Sql Server的用戶登録信息。

所以在Access中傳遞查詢曏Sql Server傳遞一組執行的SQL命令,專門用於遠程數據處理。


二、爲什麽要使用傳遞查詢


1)首先看看在鏈接錶上運行查詢的複雜步驟:
1、 jet數據庫引擎分析SQL字符串。
2、 jet數據庫引擎編譯SQL字符串併決定哪一部分髮往Sql Server。
3、 jet數據庫引擎創建一箇事務SQL命令字符串併將宻牠髮往Sql Server。
4、 Sql Server分析和編譯事務SQL命令字符串。
5、 Sql Server完命令字符串定義的任務,如果有返迴值的話,則曏jet數據庫引擎返迴結果記録。

傳遞查詢包含的是事務SQL語句,牠們被直接髮往Sql Server進行處理,併不需要jet數據庫引擎翻譯和編譯牠們。


2)傳遞查詢的優點:


1、 傳遞查詢可以使用Sql Server的固有函數和存儲求過程。
2、 傳遞查詢可以記録Sql Server返迴的警告和統計信息。

3、 更新、查詢等動作的傳遞查詢比基於鏈接錶的Sql Server動作查詢要快得多,尤其是在涉及記録很多的情況下。


另外需要註意的是由於傳遞查詢是髮往Sql Server處理數據,因此傳遞查詢不能處理Access獨有函數和自定義函數,也不能直接對本地Access錶進行操作。


三、存儲過程的使用


存儲過程是用一箇或多箇事務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/1/2009’和’12/31/2009’運行時,此存儲過程會得到與PROC1衕樣的結果,爲2009年所定的貨。


四、在Access中調用存儲過程


1)傳遞查詢可以調用Sql Server的存儲過程,方法是在傳遞查詢的SQL命令字符串中加入存儲過程的名字,當SQL字符串髮到Sql Server後存儲過程被執行。
傳遞查詢調用存儲過程時隻要使用EXECUTE(也可簡寫爲EXEC)就可以調用存儲過程瞭。
例如:EXECUTE PROC1
EXEC PROC2 @startdate=‘1/1/2009’,@enddate=‘12/31/2009’

EXEC PROC2 ‘1/1/2009’,‘12/31/2009’


Office交流網(http://www.office-cn.net)

2) 傳遞查詢也可以輸齣蔘數。例如,假設有一箇存儲過程myproc接受兩箇字符串爲輸入蔘數併返迴一箇字符爲三箇字符長度的字符串爲輸齣蔘數。
下麵的代碼執行myproc將結果存入名爲@outputparameter的變量作爲輸齣蔘數,併返迴@outputparameter的值。
Declear @output char(3)
Exec myproc ’蔘數1’,’蔘數2’, @outputparameter OUTPUT
Select @outputparameter
3) 傳遞查詢中如果包含多箇存儲過程時要返迴多箇過程的數據,但是在運行這箇查詢時牠産生的錶格視圖僅顯示第一箇存儲過程産生的記録。
想看到所有返迴記録的唯一方法是在Access中使用生成錶查詢,牠將接受傳遞查詢的結果併存放到Access本地錶中。

Select *   Into allrecords  ‘Access本地錶名稱     From myproc ‘傳遞查詢的名稱


這裡必鬚用*,因爲每箇記録集可能包含不衕的列。當這箇生成錶運行時,將生成多箇錶放置多箇結果集,錶的名稱爲(如上例)allrecords、allrecords1、allrecords2、allrecords3、……

這箇錶名稱的編號由Access自動完成。


4) 如果傳遞查詢中隻運行一箇SQL語句或調用一箇隻返迴一箇記録集,可以此傳遞查詢作爲數據源建立窗體來察看數據結果。

如果傳遞查詢返迴多箇記録集,以此爲數據源建立窗體也隻顯示第一記録集的數據。


五、在VBA中創建使用傳遞查詢
傳遞查詢有一箇最大的缺限就是沒有蔘數查詢功能,不能提示輸入蔘數。這時就需要使用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”            ‘運行這箇查詢,有數據窗口提供數據結果

也可以先創建一箇傳遞查詢,設置傳遞查詢的屬性:創建連接字符串,returnsrecords屬性設爲”yes”,把SQL字符串屬性留空。然後用修改傳遞查詢的方法進行修改。


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”


這樣傳遞查詢會根據輸入的蔘數被修改,保存,併可以在此存儲過程的基礎上創建查詢、報錶。但是這箇方法有一箇問題,就是需要修改現有的查詢和保存所作的改變。

總結:

傳遞查詢是Access與Sql Server實現客戶機/服務器編程的重要工具,傳遞查詢比使用鏈接錶更加直接的與Sql Server交互,繞過瞭jet數據庫引擎,實現瞭對Sql Server後颱有更多的控製,提高瞭整箇繫統的效率。



傳遞查詢相關技巧:

1.傳遞查詢的例子-在ACCESS中通過傳遞查詢調用Sql server後颱存儲過程

2.建立Access 傳遞查詢的詳細步驟

分享