Access中使用Create Procedure创建存储过程
人气:1想了解Access中使用Create Procedure创建存储过程的相关内容吗，在本文为您仔细讲解的相关知识和一些Code实例，欢迎阅读和指正，我们先划重点：Access中使用Create,Procedure创建存储过程，下面大家一起来学习吧。
在Access自身的帮助中看到了Create Procedure语句，测试了老半天，终于发现了诀窍：必须使用OleDb连接才能使用Create Procedure语句。ODBC连接不支持该语句，提示Create Table语法错误。 创建了存储过程后，使用Office Access工具打开数据库，在“对象 - 查询”中能够看到你创建的存储过程。
创建存储过程的语法：
复制代码 代码如下:
Create Procedure YourProc
(
@param1 varchar(254),
@param2 int
)
As
(
select * from Table1 where ID>@param2 and username=@param1
)
查询数据时只需要使用：
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 "创建了表和存储过程"
DB.NonQuery "Execute MyProc Rimifon,90"
DB.NonQuery "Exec MyProc FengYun,93"
Set Ds = DB.DataSet("Result")
MsgBox "执行了存储过程"
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
http://www.zope.org/Members/Rimifon/DbHelper.sct的内容
复制代码 代码如下:
<?xml version="1.0" encoding="utf-8"?>
<package>
<?component error="false" debug="false"?>
<comment>
<![CDATA[ 風雲数据库操作组件
Author: Rimifon
LastDate: 10:50 2006-08-19]]>
</comment>
<scriptlet id="DataHelper">
<registration classid="{12345678-aabb-ccdd-eeff-012345678901}" progid="Rimifon.DataHelper" description="風雲数据库操作组件" version="1.2" remotable="true">
<script language="vbscript">
<![CDATA[Function Register()
MsgBox "欢迎使用Rimifon.DataHelper组件，你已经成功注册了本组件。", 0, "组件注册提示"
End Function
Function Unregister()
MsgBox "感谢使用Rimifon.DataHelper组件，你已经成功卸载了本组件。", 0, "组件卸载提示"
End Function]]>
</script>
</registration>
<public>
<property name="Name" dispid="0" description="组件名称，只读"><get internalName="ProductName" /></property>
<property name="ConnStr" description="数据库连接字符串设置" />
<method name="CreateAccess" description="创建空白Access数据库，成功后返回连接字符串">
<parameter name="DbPath" description="新数据库的位置，如果不指定则使用ConnStr中设置的数据库位置" />
</method>
<method name="RecordSet" description="返回一个数据库记录集对象">
<parameter name="ConnectionString" description="设置新记录集的连接字符串（为空则使用公共连接字符串）" />
</method>
<method name="ParseSql" description="解析Sql字符串，vb和vbs中不推荐使用">
<parameter name="Sql" description="代Sql语句" />
<parameter name="Rule" description="代Sql语句中替代单引号的字符串，默认为Chr(12)" />
</method>
<method name="NonQuery" description="执行一个不返回结果集的查询">
<parameter name="Sql" description="查询语句" />
<parameter name="Rec" description="记录集对象，为空则生成临时记录集对象" />
</method>
<method name="Scalar" description="执行一个返回单行单字段的数据">
<parameter name="Sql" description="查询语句" />
<parameter name="Rec" description="记录集对象，为空则生成临时记录集对象" />
</method>
<method name="DataSet" description="执行查询并返回指定页码的数据集">
<parameter name="Sql" description="查询语句" />
<parameter name="Size" description="设置每页显示的条数，为0则全部显示" />
<parameter name="Page" description="设值页码（第几页）" />
<parameter name="Rec" description="记录集对象，为空则生成临时记录集对象" />
</method>
<method name="CompactAccess" description="压缩指定Access数据库，压缩失败后返回失败原因">
<parameter name="DbPath" description="被压缩的数据库，不指定则使用ConnStr中设置的数据库位置" />
</method>
</public>
<script language="javascript">
<![CDATA[var Nothing;
var ConnStr = "Driver=Microsoft Excel Driver (*.xls);ReadOnly=0;DBQ=Rimifon.xls";
function ProductName()
{
return "風雲数据库操作组件";
}
function CreateAccess(DbPath)
{
DbPath = DbPath?("Provider=Microsoft.Jet.OleDb.4.0;Data Source=" + DbPath):ConnStr;
var Catalog = new ActiveXObject("AdoX.Catalog");
try
{
Catalog.Create(DbPath);
Catalog.ActiveConnection.Close();
}
catch(err)
{
DbPath = new Boolean;
DbPath.message = err.message;
}
Catalog = Nothing;
CollectGarbage();
return DbPath;
}
function RecordSet(ConnectionString)
{
var Rec = new ActiveXObject("Adodb.RecordSet");
Rec.ActiveConnection = ConnectionString?ConnectionString:ConnStr;
Rec.CursorType = 1;
Rec.CursorLocation = 2;
Rec.LockType = 3;
return Rec;
}
function ParseSql(Sql, Rule)
{
if(!Rule) Rule="\f";
if(Sql.indexOf(Rule)<0) return Sql;
return Sql.replace(/'/g,"''").replace(new RegExp(Rule,"g"),"'");
}
function NonQuery(Sql, Rec)
{
if(!Rec) Rec = new RecordSet;
Rec.Source = Sql;
Rec.Open();
if(!arguments[1])
{
Dispose(Rec);
}
else
{
if(Rec.State) Rec.Close();
}
}
function Scalar(Sql, Rec)
{
if(!Rec) Rec = new RecordSet;
Rec.Source = Sql;
Rec.Open();
var Result = Rec.EOF?null:Rec.Fields.Item(0).Value;
if(Rec.State) Rec.Close();
if(!arguments[1]) Dispose(Rec);
return Result;
}
function DataSet(Sql, Size, Page, Rec)
{
var dataArr = new Array;
if(Size instanceof ActiveXObject)
{
Rec = arguments[3] = Size;
Size = 0;
}
if(!Rec) Rec = new RecordSet;
Rec.Source = Sql;
Rec.Open();
Size = parseInt(Size);
if(isNaN(Size) || !Size) Size = Rec.RecordCount;
if(Size) Rec.PageSize = Size;
if(!Page || isNaN(Page) || Page<0) Page = 1;
if(Page > Rec.PageCount) Page = Rec.PageCount;
dataArr.AbsolutePage = Page;
if(Page > 0) Rec.AbsolutePage = Page;
dataArr.RecordCount = Rec.RecordCount;
dataArr.PageSize = Size;
dataArr.PageCount = Rec.PageCount;
var C = 0;
while(!Rec.EOF && C++ < Size)
{
var Fields = new Object;
for(var x=0; x<Rec.Fields.Count; x++)
{
Fields[x] = Fields[Rec.Fields(x).Name.replace(/\s/g,"_")] = Rec.Fields.Item(x).Value;
}
dataArr.push(Fields);
Rec.MoveNext();
}
Rec.Close();
if(!arguments[3]) Dispose(Rec);
return dataArr;
}
function CompactAccess(DbPath)
{
if(!DbPath)
{
DbPath = ConnStr.match(/(dbq|data source)=([^;]*)/i);
if(!DbPath) return "未指定数据库位置。";
DbPath = DbPath[2];
}
DbPath = DbPath.replace(/\//g,"\\");
var DbName = DbPath.slice(DbPath.lastIndexOf("\\")+1);
var BackDb = DbName + Math.random() + ".MyDocs";
DbPath = DbPath.substr(0, DbPath.lastIndexOf("\\"));
var DBQ = "Provider=Microsoft.Jet.OleDb.4.0;Data Source=" + DbPath;
try
{
var Jro = new ActiveXObject("Jro.JetEngine");
var Fso = new ActiveXObject("Scripting.FileSystemObject");
Jro.CompactDatabase(DBQ+DbName, DBQ+BackDb);
var File = Fso.GetFile(DbPath+DbName);
File.Delete();
File = Fso.GetFile(DbPath+BackDb);
File.Name = DbName;
Jro = Fso = Nothing;
CollectGarbage();
}
catch(err)
{
return err.message;
}
}
function Dispose(Rec)
{
Rec.ActiveConnection.Close();
Rec = Nothing;
CollectGarbage();
}]]>
</script>
</scriptlet>
</package>
加载全部内容