把Access数据库移植到SQLServer7.0
时间:2007-12-23 来源:不详 作者:迈克DB
查询名称:Employee Sales By Country, in NWIND.mdb:
PARAMETERS [Beginning Date] DateTime, [Ending Date] DateTime; SELECT Orders.[Order ID], [Last Name] & ", " & [First Name] AS Salespe rson, Employees.Country, Orders.[Shipped Date], [Order Subtotals].Subt otal AS [Sale Amount]
FROM Employees INNER JOIN (Orders INNER JOIN [Order Subtotals] ON Orde rs.[Order ID] = [Order Subtotals].[Order ID]) ON Employees. = Orders.
WHERE (((Orders.[Shipped Date]) Between [Beginning Date] And [Ending D ate]))
ORDER BY [Last Name] & ", " & [First Name], Employees.Country, Orders. [Shipped Date];
SQL Server 7.0:
CREATE PROCEDURE EMP_SALES_BY_COUNTRY @BeginningDate datetime,
@EndingDate datetime
AS
SELECT Orders.[Order ID], [Last Name] ", " [First Name] AS Salespe rson, Employees.Country, copyright dedecms
Orders.[Shipped Date], [Order Subtotals].Subtotal AS [Sale Amount]
FROM Employees INNER JOIN (Orders INNER JOIN [Order Subtotals] ON Orde rs.[Order ID] = [Order Subtotals].[Order ID]) ON Employees. = Orders.
WHERE (((Orders.[Shipped Date]) Between @BeginningDate And @EndingDate ))
ORDER BY [Last Name] ", " [First Name], Employees.Country, Orders. [Shipped Date]
GO
更多信息,请参看SQL Server Book Online
嵌套查询(Nested Queries)
在嵌套形式中,某些Access查询是在别的查询的顶层上创建的。Access中的嵌套查询在SQL Server中变成为嵌套视图。而ORDER BY不能作为视图定义的一部分,可以用附加一个查询视图的SELECT语句来代替它。假如你有嵌套的Access查询,你需要创建一系列视图,然后创建那些在视图上执行SELECT语句的存储过程,并且把ORDER BY子句粘贴到SELECT语句上。
例如,下面的Access查询:
dedecms.com
SELECT * FROM STUDENTS
WHERE COUNTRY = "USA"
ORDER BY LAST_NAME
变成一个SQL Server视图和存储过程
CREATE VIEW US_STUDENTS AS SELECT * FROM STUDENTS
WHERE COUNTRY = "USA"
CREATE PROCEDURE US_STUDENTS_ORDER AS
SELECT * FROM US_STUDENTS ORDER BY LAST NAME
检查SQL Server
你可以使用SQL Server查询分析器的查询菜单上的Parse命令检查SQL Server中的视图或存储过程函数。在下面的例子中,Access查询使用DISTINCTROW。SQL Ser ver使用Transact-SQL DISTINCT命令执行相同的操作。Parse命令答应开发人员孤立并且修改他们的Access查询中的语法问题。
连接你的应用程序
许多Access应用程序是用Microsoft Visual Basic for Applications或Visual Basic for Applications Access用户接口写成的。
文章评论
共有位Admini5网友发表了评论 查看完整内容