将Access数据库移植到SQL Server.doc

将Access数据库移植到SQL Server
随着用户对于企业级高性能数据库的需求的增长，用户时常要从Microsoft Access Jet引擎的文件-服务器环境下转换到Microsoft SQL Server的客户-服务器环境。Microsoft Office 2000中的Access 2000 Upsizing Wizard可实现将数据表和查询转移到SQL Server 7.0中。如果您用的是Access的较早的版本，您可以先将它升级为Access 2000，然后再使用其中的Upsizing Wizard，从而将您的应用移植到SQL Server中。 
如果您并不太愿意采用Access 2000 和Upsizing Wizard来实现移植，本文可以作为将Access 2000移植到SQL Server的一个指南。转移一个Access上的应用首先需要将数据转移到SQL Server，然后将查询转移进数据库，或是转移为SQL文件以备稍后执行。最后要采取的步骤是移植应用程序。 
数据库移植中用到的SQL Server 工具 
SQL Server管理器（SQL Server Enterprise Manager） 
SQL Server管理器 允许对SQL Server以及SQL Server中的对象进行企业级的配置和管理。SQL Server管理器提供一个强有力的scheduling引擎，高度的容错力和一个嵌入式的复制管理界面。使用SQL Server管理器可以实现以下功能： 
管理连接和用户许可 
创建脚本程序 
管理SQL Server对象的备份 
备份数据和事务处理日志 
管理表、视图、存储过程、触发器、索引、规则、默认值以及用户定义的数据类型 
建立全文本索引、数据库图表和数据库维护计划 
输入和输出数据 
转换数据 
执行各种网络管理任务 
在以Microsoft Windows NT为操作系统的计算机中，SQL Server Manager由SQL Server Setup进行安装，并被默认为服务器组件，而在运行着Windows NT 和Microsoft Windows 95的机器上，它将被默认为客户方组件。您将从SQL Server Manager的图形用户界面中启动数据转移服务（DTS，Data Transformation Services）。 
数据转移服务（Data Transformation Services ，DTS) 
数据转移服务允许您在多种异构数据源之间输入和输出数据，这些数据源采用基于数据库的OLE体系结构；或在使用SQL Server 7.0的多个计算机之间转移数据库和数据库对象；您还可以通过运用数据转移服务，更便捷地在一个在线事务处理系统（OLTP）中建立数据仓库和数据中心。 
DTS Wizard允许您交互地创建DTS包，通过OLE DB和ODBC来输入、输出、验证和转移数据。DTS Wizard还允许您在关系型数据库之间拷贝图解（schema）和数据。
SQL Server 查询分析器（Query Analyzer） 
SQL Server 查询分析器是一种图形化的查询工具，通过它您可以分析一个查询，同时执行多个查询，查看数据和获取索引建议。SQL Server 查询分析器提供了showplan选项，可用来显示SQL Server查询优化器所选择的数据提取方法。 
SQL Server Profiler 
SQL Server Profiler可以实时地捕获数据库服务器活动的连续记录。SQL Server Profiler允许您监控SQL Server产生的事件，过滤基于用户指定标准的事件，或将操作步骤输出到屏幕、文件或数据表。运用SQL Server Profiler，您可以重新执行所捕获的上一次操作。这种工具可以帮助应用程序开发者识别那些可能会降低应用程序性能的事务处理。在将一个基于文件体系结构的应用程序移植到客户/服务器结构中时该特性是很有用的，因为它的最后一步包括对面向新的客户/服务器环境的应用程序进行优化。 
转移表和数据 
使用DTS Wizard将您的Access数据转移到SQL Server，可采取以下步骤： 
在 SQL Server Manager（Enterprise Manager）的工具菜单中，鼠标指向“Data Transformation Services”, 然后点击“Import Data.” 
在“选择数据源”（ Choose a Data Source）的对话窗口中，选择Microsoft Access为数据源，然后输入您的．mdb文件名（mdb为文件扩展名）或者选择浏览文件。 
在“选择数据目标”（Choose a Destination）的对话窗口中，选择“Microsoft OLE DB Provider for SQL Server”，再选择好数据库服务器，然后点击所需的认证模式。 
在“指定表备份或查询”（ Specify Table Copy or Query）的对话窗口中，点击“拷贝表”（Copy tables）。 
在“选择数据源”的对话窗口中，点击“选择所有项”（ Select All）。 
移植Microsoft Access查询 
您可以将Access的查询以下面的格式之一转移至SQL Server中： 
事务处理SQL脚本程序（Transact-SQL s cripts ） 
事务处理SQL语句通常是由数据库程序调用的，但是您也可以使用SQL Server 7.0中包含的SQL Server 查询分析器直接运行它们。SQL Server 查询分析器可帮助开发者测试事务处理SQL语句，或运行那些执行查询处理、数据操作（插入，修改，删除）和数据定义（创建表）的事务处理SQL语句。 
存储过程（Stored procedures ）
开发者可以将大部分产生自Access查询（查找，插入，修改，删除）的事务处理SQL语句转移至存储过程。用事务处理SQL语句书写的存储过程可以用来对您的数据存取打包，并使之标准化，而且存储过程实际上是存储在数据库中的。存储过程可以带参数，也可不带参数，可以由数据库程序调用或者由SQL Server查询分析器手动执行。 
视图（Views ） 
视图是从一个或多个表中显示特定的行和列的虚拟表。它们允许用户可以不直接执行构成查询基础的复杂连接而建立查询。视图不支持参数的使用。连接多个数据表的视图不能用INSERT, UPDATE或 DELETE语句来修改。视图由事务处理SQL语句调用，也可用于SQL Server查询分析器中运行的程序段。SQL Server视图和SQL-92标准不支持视图中的ORDER BY排序子句。如欲了解事务处理SQL，存储过程和视图的其他信息，请参阅SQL Server 在线参考书。 
Access查询类型的SQL Server移植选择与建议
一个SELECT语句可以存储在事务处理SQL文件、存储过程或是视图中。建立存储过程是将数据库应用开发与数据库设计的物理实施分开的最佳方法。存储过程可在一处创建而由应用程序调用。
如果存储过程所基于的数据库变化了，而存储过程经过仔细的修改以反应这些变化，则对存储过程的调用将不会受到破坏。 
交叉表（CROSSTAB） 
交叉表经常用于总结报表。
一个Access的交叉表可以通过SQL程序段、存储过程或视图中的事务处理SQL语句来执行。每当发出一个查询时，数据连接被重现执行以确保最近的数据得到使用。 
根据实际应用情况，比较合适的方法是将交叉表中的数据存储为一个临时表（参考下面的MAKE TABLE），临时表对资源的需求比较少，但是临时表在建立的同时只提供对数据的一个快照（snapshot）。 
创建表（MAKE TABLE） 
Access中的“MAKE TABLE”（ 创建表）可以通过事务处理SQL脚本程序或存储过程中的事务处理SQL语言的建表语句“CREATE TABLE”来执行。语法如下所示： 
SELECT [ ALL | DISTINCT ] 
[ {TOP integer | TOP integer PERCENT} [ WITH TIES] ] 
<select_list> 
[ INTO new_table ] 
[ FROM {<table_source>} [,…n] ] 
[ WHERE <search_condition> ] 
[ GROUP BY [ALL] group_by_eXPression [,…n] 
[ WITH { CUBE | ROLLUP } ] 
CREATE TABLE mytable (low int, high int) 
UPDATE（修改） 
UPDATE语句可以存储在事务_SQL脚本程序中，然而比较好地执行UPDATE语句的方法是创建一个存储过程。 
APPEND（添加） 
ALLEND语句可以存储在事务_SQL脚本程序中，然而比较好地执行APPEND语句的方法是创建一个存储过程。 
移植Microsoft Access的查询到存储过程和视图
每个Access查询都必须用以下的一系列语句替换： 
CREATE PROCEDURE <NAME_HERE> AS 
< SELECT, UPDATE, DELETE, INSERT, CREATE TABLE statement from Microsoft Access > 
GO 
CREATE VIEW <NAME_HERE> AS 
<Place (SELECT only, with no parameters) Microsoft Access Query> 
GO 
对每个Access查询应执行： 
打开Access，然后在SQL Server中，打开SQL Server查询分析器。 
在Access的数据库窗口中点击“Queries”tab键，然后点击“Design”按钮。 
在“View”菜单上点击“SQL”按钮。 
将整个查询粘贴到SQL Server查询分析器中。 
测试语法，保存事务处理SQL语句以备后用，或者在数据库中执行这些语句。您可以选择将事务处理SQL语句保存到一段脚本程序中。 
移植Microsoft Access查询到事务处理SQL语句 
大部分的Access查询应该转换成存储过程和视图。然而，有一些应用程序开发者不太常用的语句可以存储为事务处理SQL脚本，一种以sql为文件扩展名的文本文件。 这些文件可以在SQL Server查询分析器中运行。 
如果您打算将一些Access查询转换为sql文件的话，可以考虑根据它们使用的方式有区别地将这些事务处理SQL语句分别放在几个脚本程序中。例如，您可以将必须以同样频率运行的事务处理SQL语句归类到同一个脚本中。另一个脚本中则应包含所有只在某些条件下运行的事务处理SQL语句。此外，必须以一定顺序执行的事务处理SQL语句应当归类到一个不连续的脚本中。 
将Access语句转移到事务处理SQL 文件 
将语句拷贝到SQL Server查询分析器中 
使用蓝色的多选项图标分析语句 
在适当时候执行该语句 
要执行Access中的创建表（MAKE TABLE）的查询任务的开发者在SQL Server中有几种选择。开发者可创建下列对象之一： 
一个视图 
视图具有动态的虚拟表的效果，可提供最近的信息。这是一个输入/输出强化器，因为每当发出一个查询时它都要求对数据表重现建立连接。 
一个临时表 
临时表为已连接的用户会话建立一个快照。您可以建立局部的或全局的临时表。局部临时表只在当前会话中可见，而全局临时表则在所有会话都是可见的。在局部临时表的名字前加上单个数字的前缀（(#table_name)），而在全局临时表的名字前加上两位数字的前缀(##table_name)。对临时表的查询执行起来非常快，因为它们取得一个结果集的时候通常只用一个表，而不是将多个表动态地连接在一起来。 
如欲了解临时表的其他信息，请参阅SQL Server在线参考书。 
SQL Server 7.0中的数据转换服务（DTS）允许您通过创建包来实现临时表建立的标准化、自动化和进度安排。例如，当您移植Access 2.0中的Northwind 范例数据库时，用于季度数据报表的交叉表可转变为一个视图或者一个可在规范基础上建立临时表的数据转换。如欲了解关于DTS的其他信息，请参阅SQL Server在线参考书。
其他设计上的考虑 
下面是当您将您的Access应用移植到SQL Server时必须考虑的一些其他问题： 
使用参数 
带参数的SQL Server存储过程需要一种不同于Access查询的语法格式，例如：
Access 2.0格式： 
查询名：Employee Sales By Country, in NWIND.mdb: 
PARAMETERS [Beginning Date] DateTime, [Ending Date] DateTime; 
SELECT Orders.[Order ID], [Last Name] & ", " & [First Name] AS Salesperson, Employees.Country, Orders.[Shipped Date], [Order SuBTotals].Subtotal AS [Sale Amount] 
FROM Employees INNER JOIN (Orders INNER JOIN [Order Subtotals] ON Orders.[Order ID] = [Order Subtotals].[Order ID]) ON Employees. = Orders. 
WHERE (((Orders.[Shipped Date]) Between [Beginning Date] And [Ending Date])) 
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 Salesperson, Employees.Country, 
Orders.[Shipped Date], [Order Subtotals].Subtotal AS [Sale Amount] 
FROM Employees INNER JOIN (Orders INNER J

问题：新手来看：Access转SQL Server要注意些什么？
方法一：
新手来看：Access转SQL Server要注意些什么？
首先，你要明白什么是 Access 开发，什么是 JET 应用，如果你还不能明白，请参考：

在这里，我们谈论的是 Access 开发的升级，如何升级到 SQL Server 2000。你有几种升级方法可以选择。
1、你可以采用链接表方式，通过链接表链接到 SQL SERVER 2000 中的表。原先在 ACCESS 中开发的程序基本无需更改，除非你要更改数据结构
2、你可以采用 ADP 的方式升级数据库，ADP 方式基本抛弃了原先 MDB 大多数模式。你必须适应新的数据结构，接受 JET SQL 编写的查询变成 T-SQL 编写的存储过程、视图等等对象的结果。也就是说你必须重新学习 T-SQL 。
另外请参考：

方法二：
给你一些我的经验吧。反正access到sqlserver的移植是很痛苦的，我弄过好几次了。
希望大家能补充一下。
1.数据库导入以后，自动增加字段需要重写，所有的数字类型需要增加长度，最好用
decimal。
2.所有的默认值都丢失了。主要是数字类型和日期类型。
3.所有now()，time()，date()要改成getdate()。
4.所有datediff('d', time1, time2)要改成datediff(day, time1, time2)
5.有可能一些true/false类型不能使用，要变为1/0。
6.备注类型要通过cast(column as varchar)来使用。
7.CursorType要改成1,也就是打开数据库时要给出第一个数字参数为1，否则记录可能
显示不完整。
8.isnull(rowname)要改成rowname = null

方法三：
1.ACCESS的数据库中的自动编号类型在转化时，sql server并没有将它设为自动编号型，我们需在SQL创建语句中加上identity，表示自动编号！
    2.转化时，跟日期有关的字段，SQL SERVER默认为smalldatetime型，我们最好将它变为datetime型，因为datetime型的范围比smalldatetime型大。我遇见这种情况，用smalldatetime型时，转化失败，而用datetime型时，转化成功。
    3.对此两种数据库进行操作的sql语句不全相同，例如：在对ACCESS数据库进行删除纪录时用："delete * from user where id=10",而对SQL SERVER数据库进行删除是用："delete user where id=10".
    4.日期函数不相同，在对ACCESS数据库处理中，可用date()、time()等函数，但对
    SQL SERVER数据库处理中，只能用datediff,dateadd等函数，而不能用date()、time()等函数。
    5.在对ACCESS数据库处理中,sql语句中直接可以用一些VB的函数，像cstr()函数，而对SQL SERVER数据库处理中，却不能用。

Visual Basic for Applications 与 SQL Server Transact-SQL 之间的函数比较 (ADP)：：
Visual Basic for Applications 与 SQL Server Transact-SQL 之间的函数比较 (ADP)
注释  本主题中的信息仅适用于 Microsoft Access 项目 (.adp)。
下表比较了 Microsoft Visual Basic for Applications (VBA) 和 Microsoft SQL Server Transact-SQL 中的常用函数。有关 Transact-SQL 标量函数的详细信息，请参阅 SQL Server 文档。
注意   “升迁向导”将下列 VBA 函数（用于表有效性规则、字段验证和默认情况下）转换为对等的 Transact-SQL 函数。
字符串函数
VBA 函数
Transact-SQL
标量函数
Asc(x)
ASCII(x)
Chr$(x)
CHAR(x)
Lcase$(x)
LCASE(x)
LOWER(x)
Len(x)
DATALENGTH(x)
Ltrim$( x)
LTRIM(x)
Mid$(x,y,z)
SUBSTRING(x,y,z)
Right$(x,y)
RIGHT(x,y)
Rtrim$(x)
RTRIM(x)
Space$(x)
SPACE(x)
Str$(x)
STR(x)
Ucase$( x)
UCASE(x)
UPPER(x)
转换函数
VBA 函数
Transact-SQL
标量函数
Ccur(x)
CONVERT(money,x)
Cdbl(x)
CONVERT(float,x)
Cint(x)
CONVERT(smallint,x)
Clng(x)
CONVERT(int,x)
Csng(x)
CONVERT(real,x)
Cstr(x)
CONVERT(varchar,x)
Cvdate(x)
CONVERT(datetime,x)
日期函数
VBA 函数
Transact-SQL
标量函数
Date(x)
CONVERT(DATETIME,CONVERT(varchar,GETDATE(x)))
Dateadd("<Access datepart>",x,y)
DATEADD(<SQL Server datepart>, x, y)
Datediff("<Access datepart>",x,y)
DATEDIFF(<SQL Server datepart>, x, y)
Datepart("<Access datepart>", x)
DATEPART(<SQL Server datepart>, x)
Day(x)
DATEPART(dd,x)
Hour(x)
DATEPART(hh,x)
Minute(x)
DATEPART(mi,x)
Month(x)
DATEPART(mm,x)
Now(x)
GETDATE(x)
Second(x)
DATEPART(ss,x)
Weekday(x)
DATEPART(dw,x)
Year(x)
DATEPART(yy,x)
数学函数
VBA 函数
Transact-SQL
标量函数
Int(x)
FLOOR(x)
Sgn(x)
SIGN(x)
Rnd(x)
ROUND(x)

专题地址：

简述：

阅读前需掌握：

难度等级：
等级未评定

新手来看：Access的特点、概念。什么是纯 ACCESS 环境？
问题：这里只解决一个问题，到底什么是Access？
设计一个数据库管理系统，用access
在access里面设计好表，查询，，然后再用vb做窗体做连接，跟在access里面设计窗体,报表 再调VBA来编代码有什么区别吗
我们是分成 几个组做的,但其他人好像没这个意识,我觉得直接在access里把一切都作好再调用 vb编码好像 更容易一点
我不明白，MDB到底是什么，假如说SQL是一个后台数据存储库，那ACCESS既是后台又是前台，这么说，SQL和ACCESS同样都可以被调用，那为什么还要SQL呢，ACCESS不是完全可以代替SQL了吗 
MDB 格式文件能否脱离 ACCESS 环境，在未装 ACCESS 的机器上被其他软件调用？

回答：1、Access是 Microsoft Office 的一个组件，一个前后台结合的数据库“软件”。
也就是说Access既拥有用户界面(VB可以用来开发用户界面)；也拥有逻辑、流程处理，即VBA语言(VB也可以用来做逻辑处理)；又可以存储数据，即在“表”中存储数据。而所有这些都存储在一个 MDB 格式的文件中(当然，也可以是 MDE 等格式)。
回答上述问题： Access里面设计好界面，VB中必须建立ACCESS.APPLICATION才能调用，也就是说这基本是VBA编程，而不是VB编程。
2、Access不是一种存储格式，是一种软件。
请大家严格区分 Access 软件与 MDB 格式。以下的语句是错误的：“我用VB来开发MIS系统，数据存储在ACCESS中。”。VB下调用 MDB 格式(或 JET DB)只是调用一种格式的文件而已，并没有调用到 Access，其实官方说法叫 “VB 的 JET 应用”。另外，MDB 格式的文件也可以被其他开发工具，比如 DELPHI / PB / C / PHP /  ASP / CGI / C# / J# / JAVA / VB.NET / C.NET.... 调用。但是被调用的只是 MDB 文件，很多 ACCESS 软件所具有的功能，这些开发工具是无法调用的。
也正因为如此，类似这样的问题“我的系统中没有安装 OFFICE ACCESS，是否能用 ASP 调用 ACCESS 数据库呢？”，答案是：当然可以，其实你根本没有使用 ACCESS 这个软件，只是使用了 MDB 这种数据库格式，而这种格式只要系统中有 OLE DB 引擎就可以支持。WINDOWS 98 以及以上版本的 WINDOWS 操作系统都内置支持 OLE DB 引擎。

3、Access 中的一些对象可以被 VB 调用，但是必须在调用的机器上安装 ACCESS 这个软件（COPY一个MDB文件是不够的）或者安装额外的 ActiveX 控件
4、这里指的纯 ACCESS 环境就是指没有使用任何其他软件，直接使用 ACCESS 界面以及存储格式的环境，也就是不能脱离 ACCESS 运行时的环境。上面说的 ASP + MDB / VB + MDB 等等都不是所谓的“纯 ACCESS 环境”。因此上述环境中出现的问题基本与 ACCESS 这个软件毫无关系。
5、“我不明白，MDB到底是什么”，回答上述问题：MDB 是一种文件格式，其中的数据以特定方式组织起来，而 ACCESS 这种软件的默认存储格式正好是 MDB 
6、“假如说SQL是一个后台数据存储库，那ACCESS既是后台又是前台”，
回答上述问题： SQL 是“结构化查询语言”，请不要与 SQL SERVER 这种数据库软件混淆。SQL SERVER 是一种数据库软件，但是他没有“最终用户”界面，只有适合于开发者使用的界面，开发者可以使用其他编程工具来开发界面，比如用 VB VB.NET 等；而 ACCESS 这个软件本身就具有开发者使用的界面和适合于“最终用户”的界面，也就是我们通常说的前后台结合。
7、“那ACCESS既是后台又是前台，这么说，SQL和ACCESS同样都可以被调用，那为什么还要SQL呢，ACCESS不是完全可以代替SQL了吗”，
回答上述问题：请先参考回答3，ACCESS 的功能和 SQL SERVER 不同所以并不冲突，具体有什么不同请自行参考 ACCESS 的“帮助”以及 SQL SERVER 的“联机丛书”
8、“MDB 格式文件能否脱离 ACCESS 环境，在未装 ACCESS 的机器上被其他软件调用？”
回答上述问题：可以，这是因为有 JET DB 的支持，而 WINDOWS 98 以上默认就内置了 JET DB ，就像 98 以上的机器默认就可以浏览网页，而 windows 95 的机器必须安装浏览器才能浏览网页一个道理。有关 JET DB 以及 MDAC ，请参考：
    《发布》MDAC是什么？在哪里下载？包含什么？

注意：由于 ACCESS 2007 彻底改变了数据库引擎，所以回答8的情况只适用与 ACCESS 97 - ACCESS 2003 格式的 MDB 文件，不适用于 ACCESS 2007 的 accdb 格式

20