Access传递查询访问Sql Server数据方法简介
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、 jet数据库引擎分析SQL字符串。
2、 jet数据库引擎编译SQL字符串并决定哪一部分发往Sql Server。
3、 jet数据库引擎创建一个事务SQL命令字符串并将宻它发往Sql Server。
4、 Sql Server分析和编译事务SQL命令字符串。
5、 Sql Server完命令字符串定义的任务，如果有返回值的话，则向jet数据库引擎返回结果记录。
传递查询包含的是事务SQL语句，它们被直接发往Sql Server进行处理，并不需要jet数据库引擎翻译和编译它们。
传递查询的优点：
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中调用存储过程
传递查询可以调用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’
l 传递查询也可以输出参数。例如，假设有一个存储过程myproc接受两个字符串为输入参数并返回一个字符为三个字符长度的字符串为输出参数。
下面的代码执行myproc将结果存入名为@outputparameter的变量作为输出参数，并返回@outputparameter的值。
Declear @output char(3)
Exec myproc ’参数1’,’参数2’, @outputparameter OUTPUT
Select @outputparameter
l 传递查询中如果包含多个存储过程时要返回多个过程的数据，但是在运行这个查询时它产生的表格视图仅显示第一个存储过程产生的记录。
想看到所有返回记录的唯一方法是在Access中使用生成表查询，它将接受传递查询的结果并存放到Access本地表中。
Select *
Into allrecords ‘Access本地表名称
From myproc ‘传递查询的名称
这里必须用*,因为每个记录集可能包含不同的列。当这个生成表运行时，将生成多个表放置多个结果集，表的名称为（如上例）allrecords、allrecords1、allrecords2、allrecords3、……这个表名称的编号由Access自动完成。
l 如果传递查询中只运行一个SQL语句或调用一个只返回一个记录集，可以此传递查询作为数据源建立窗体来察看数据结果。如果传递查询返回多个记录集，以此为数据源建立窗体也只显示第一记录集的数据。
五、在VBA中创建使用传递查询
传递查询有一个最大的缺限就是没有参数查询功能，不能提示输入参数。这时就需要使用VBA来解决这个问题。
l 下例用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 ‘返回记录为”真”
docmd.openquery “test” ‘运行这个查询,有数据窗口提供数据结果
l 修改一个已经存在的传递查询
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属性只能用运作查询
l 向传递查询传递参数
创建窗体,在窗体中建立一个文本框和一个按钮,在按钮的click事件中输入以下代码:
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=”exec myproc ’” & me![窗体文本框的值] & “’”’设置SQL语句,相当于传递查询窗口中的语句。当然也可以用SQL语句。
myquery.returnsrecords=true ‘返回记录为”真”
docmd.openquery “test” ‘运行这个查询,有数据窗口提供数据结果
也可以先创建一个传递查询,设置传递查询的属性:创建连接字符串,returnsrecords属性设为”yes”,把SQL字符串属性留空。然后用修改传递查询的方法进行修改。
dim mydb as database ‘定义数据库对象变量
dim myquery as querydef ‘定义查询对象变量
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后台有更多的控制，提高了整个系统的效率。
