在Access自身的幫助中看到了Create Procedure語句，測試了老半天，終于發(fā)現(xiàn)了訣竅：必須使用OleDb連接才能使用Create Procedure語句。ODBC連接不支持該語句，提示Create Table語法錯誤。
創(chuàng)建了存儲過程后，使用Office Access工具打開數(shù)據(jù)庫，在“對象 - 查詢”中能夠看到你創(chuàng)建的存儲過程。
創(chuàng)建存儲過程的語法：
復制代碼 代碼如下:
Create Procedure YourProc
(
@param1 varchar(254),
@param2 int
)
As
(
select * from Table1 where ID>@param2 and username=@param1
)
查詢數(shù)據(jù)時只需要使用：
Rs.Open "YourProc admin,1", Conn
myproc.vbs
復制代碼 代碼如下:
Set DB = GetObject("script:http://www.zope.org/Members/Rimifon/DbHelper.sct")
DB.ConnStr = "Provider=Microsoft.Jet.OleDb.4.0;Data Source=MyProc.mdb"
DB.NonQuery "create table Sheet1(id counter,name varchar(254),score int)"
DB.NonQuery "Create Procedure MyProc(@name varchar(254), @score int) as (insert into Sheet1(name,score) values(@name, @score))"
DB.NonQuery "Create Procedure Result as (select * from Sheet1)"
MsgBox "創(chuàng)建了表和存儲過程"
DB.NonQuery "Execute MyProc Rimifon,90"
DB.NonQuery "Exec MyProc FengYun,93"
Set Ds = DB.DataSet("Result")
MsgBox "執(zhí)行了存儲過程"
DB.NonQuery "drop Procedure MyProc"
DB.NonQuery "drop Procedure Result"
DB.NonQuery "drop table Sheet1"
MsgBox "刪除了表和存儲過程"
Set DB = Nothing
Dim Result
Result="所有記錄：" Chr(13)
For Each Item In Ds
If IsObject(Item) Then
Result = Result Item.ID Chr(9) _
Item.Name Chr(9) Chr(9) _
Item.Score Chr(13)
End If
Next
Set Ds = Nothing
MsgBox Result
12下一頁閱讀全文
