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”