office交流網--QQ交流群號

Access培訓群:792054000         Excel免費交流群群:686050929          Outlook交流群:221378704    

Word交流群:218156588             PPT交流群:324131555

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

2017-07-13 22:10:00
zstmtony
轉貼
3112
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 訂單.訂單時間>[email protected] AND 訂單.訂單時間<[email protected]

當兩個輸入參數為’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.guizmj.live)

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.guizmj.live)
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.guizmj.live)
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.guizmj.live) 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 傳遞查詢的詳細步驟

分享
北京十一选五基本走势