将您的 Access 数据库移植到 Oracle

技术说明

将您的 Access 数据库移植到 Oracle
作者：Puneet Sangal，高级 Java 开发人员，National Leisure Group (Woburn, Mass.)
发表日期：2004 年 12 月

从现有系统向 Oracle 数据库的移植过程可能令人畏惧，尤其是现有数据库与 Oracle 数据库结构具有很少的相似性或者没有相似性的时。在本技术说明中，我将对比从 Microsoft Access 移植到 Oracle 的两种技术：使用 Microsoft Access 中的实用程序以及某些 DOS 专用批处理文件的“原始”方法，以及使用 Oracle Migration Workbench (OMW) 工具的“改进”方法。

手动方法

 

第一种技术包括两个阶段。第一阶段是将 Access 数据库转换为 Oracle 数据库。只有当 Access 作为前端（如表单和报表） — 并且 Oracle 数据库继续作为后端的时候才使用第二阶段。后者提供了一种在即席查询基础上将 Access 数据库备份到 Oracle 的策略。还可以建立一个服务，使这一过程定期自动地运行。

为了实现从 Access 到 Oracle 数据库的手动移植，您可以遵循以下这些步骤：

• 在 Oracle 中定义具有相同数据类型的表。避免在 Oracle 中使用 CLOB，因为这种数据类型会使系统速度降低；使用 varchar2 作为替代类型。此外，禁止在列名中使用空格，禁止使用百分号。单词 mode 和 comment 是 Oracle 中的关键字，因此如果您的 Access 数据库有包含这些单词的列名，则应该在 Oracle 中将它们改为其他有意义的名称。谨慎处理日期和时间之间、具有小数的数字与没有小数的数字之间以及 char 与 varchar2 的数据类型转换。在 Oracle 中没有 Boolean 类型 — 使用 char(1) 作为替代类型。
• 确保 Access 表定义了主键（Oracle 的所有表均需它们）。此外，Oracle 区分大小写，而 Access 不区分大小写。所有列和表的名称均使用大写字母。另外，在 Oracle 中名称应少于 30 个字符（在 Access 中为少于 64 个）。
• 前往“控制面板”->“管理工具”->“数据源 (ODBC)”。
  • 在“系统 DSN”选项卡中，单击“添加”。
  • 选择 Oracle ODBC 驱动程序，然后单击“完成。
    • 在“ODBC 驱动程序设置”框中，为 DNS 键入数据库名称而非主机名称。
    • 键入说明。
    • 对于“数据源服务名”，再次键入数据库的名称并指定 UserID。
    • 根据需要设置其他选项，并相应地更改预取数量。注意，应用程序提取行所用的 Oracle 数据库必须是 8.0.3 或更高版本。
    • 单击“确定”两次。注意，根据您的操作系统版本不同，您可能可以选择在驱动程序配置中直接使用 TNS 名称服务。
    • 在继续操作之前，测试该连接。
• 转到 Access。
  • 选择表。
  • 右键单击“链接表”。
  • 在对话框中，转到“文件类型”，选择 ODBC Databases ()。
  • 转到“机器数据源”选项卡，找到所需 DSN。选中它，单击“确定”。
• 如果尚未填写服务名、用户名和口令，则键入这些项。对于服务名，应该使用该 DSN。
  • 如果尚未填写服务名、用户名和口令，则键入这些项。对于服务名，应该使用 DSN。
    • 单击“确定”，出现一系列的表和模式。选择表。单击“确定”。
    • 对于没有主键的表，提示您选择唯一的记录标识符。对于这些表，选择“不显示字段”，然后单击“确定”。现在您将看到 Access 中所有的链接表（在表的旁边有一个球形标志）。
  • 转到“查询”选项卡。
    • 选择“新建”->“查找不匹配项查询向导”。
    • 选择 Access 中的表。单击“下一步”。
    • 选择从 Oracle 中链接的表（现在位于 Access 中）。
      • 如果有主键，则对其进行匹配。
      • 如果没有主键，只需单击“下一步。
      • 如果有多个主键，则只需匹配其中一个。
      • 单击“下一步”，选择所有字段，选择“下一步”，单击“修改设计”，然后单击“完成”。
    • 转到“查询”->“追加查询”。
      • 选择表名作为所讨论的链接表，如果尚未选择，则选择当前数据库，然后单击“确定”。
      • 在“追加到”部分中，如果尚未选择相应列，则选择这些列。
      • 转到匹配主键列中，删除出现在“追加到”部分中的任何内容。
      • 保存该查询。
      • 如果有一个主键，则此步骤即可完成。
      • 如果有多个主键，则转到 SQL 视图中，将查询更改为包含两个主键 — 例如，如果表有两个主键（如 Catalog# 和 Step#），并且您在匹配查询向导中使用了 Catalog#，则将：

        INSERT INTO PMSANGAL_TBLSPECSTEPS ( MOD, [CATALOG#], [STEP#], LAYER, NOM_OHMS,
                        NOM_OHMS, MAX_OHMS, LEFT_PERCENT, RIGHT_PERCENT )
                        SELECT SpecSteps.Mode, SpecSteps.[Catalog #], SpecSteps.[Step #], SpecSteps.Layer,
                        SpecSteps.[Min Ohms], SpecSteps.[Nom Ohms], SpecSteps.[Max Ohms], SpecSteps.[Left %],
                        SpecSteps.[Right %]
                        FROM SpecSteps LEFT JOIN PMSANGAL_TBLSPECSTEPS ON SpecSteps.[Catalog #] =
                        PMSANGAL_TBLSPECSTEPS.[CATALOG#]
                        WHERE (((PMSANGAL_TBLSPECSTEPS.[CATALOG#]) Is Null));
                        
                        

        修改为：

        INSERT INTO PMSANGAL_TBLSPECSTEPS ( MOD, [CATALOG#], [STEP#], LAYER, MIN_OHMS,
                        NOM_OHMS, MAX_OHMS, LEFT_PERCENT, RIGHT_PERCENT )
                        SELECT tblSpecSteps.Mode, tblSpecSteps.[Catalog #], tblSpecSteps.[Step #], tblSpecSteps.Layer,
                        tblSpecSteps.[Min Ohms], tblSpecSteps.[Nom Ohms], tblSpecSteps.[Max Ohms],
                        tblSpecSteps.[Left %], tblSpecSteps.[Right %]
                        FROM tblSpecSteps LEFT JOIN PMSANGAL_TBLSPECSTEPS ON ((tblSpecSteps.[Catalog #] =
                        PMSANGAL_TBLSPECSTEPS.[CATALOG#]) AND  (tblSpecSteps.[Step #] = PMSANGAL_TBLSPECSTEPS.[STEP#]))
                        WHERE (((PMSANGAL_TBLSPECSTEPS.[CATALOG#]) Is Null) AND ((PMSANGAL_TBLSPECSTEPS.[STEP#]) Is Null));
                        

        因为所有主键列均非空，所以您需要在 WHERE 条件中使用 AND。
    • 对所有表重复以上步骤。
    • 如果表中没有主键，则使用 AND 而非 OR 在所有列上进行一次联接并在所有列上进行一次 null 检查。
  • 转到“宏”。
    • 在“操作”中选择“OpenQuery”，并选择追加的查询之一。“视图”将是“数据表”，而“数据模式”将是“编辑”。
    • 保存该宏。
    • 选择该宏，并转到“工具”-“宏”->“将宏转换为 Visual Basic 代码”。
    • 如果尚未选中“给生成的函数加入错误处理”和“包含宏注释”，则选中此二者，并单击“转换”，将该宏转换为具有函数名的模块。
    • 保存该模块。
  • 再次转到该模块，对其进行更改，使用以下的模板：

    '------------------------------------------------------------
            ' CalMacro
            '
            '------------------------------------------------------------
            Function CalMacro()
            On Error GoTo CalMacro_Err
            Dim strConnect As String
            Dim strMyFile As String
            Dim dbs As Database
            Dim rst As Recordset
            strConnect = "ODBC;DATABASE=PUNEET;DSN=PUNEET;" & "UID=PMSANGAL;" & "PWD=********;"
            'PUNEET is the DSN or name of the database...
            Set dbs = OpenDatabase("", False, True, strConnect)
            Set rst = dbs.OpenRecordset("PMSANGAL.tblCal", dbOpenDynaset)
            DoCmd.SetWarnings False
            DoCmd.OpenQuery "tblCal Without Matching PMSANGAL_TBLCAL", acNormal, acEdit
            dbs.Close
            CalMacro_Exit:
            Exit Function
            CalMacro_Err:
            MsgBox Error$
            Resume CalMacro_Exit
            End Function
            

  • 对于所有的追加查询，重复以上过程。
  • 在创建这些模块之后，您可以删除那些用于创建这些模块的宏。
  • 再创建一个新的宏。
    • 在“操作”中选择“RunCode”，并从刚才为其所创建的模块中选择或键入函数名。为此，双击“函数”->“数据库”，选择正确的模块和函数。
  • 对于同一个宏中的所有追加查询都执行此过程。
  • 最后，在“操作”中选择“Quit”，并选择“退出”选项。
  • 现在执行简单的一步，但却是最重要的一步：利用以下信息制作一个批处理文件：

    "C:\Program Files\Microsoft Office\Office\Msaccess.exe" "database path" /x  Macroname
            

  • 运行该批处理文件。

您可以看到此方法需要大量手动工作。需要执行很多步骤，并且该过程很容量出错。即使您只做错一步，也很难进行反向跟踪并调试什么地方出错了。此外，如果您需要重新链接表，则会感到困难重重且很费时，这取决于要转换的表的数量。

自动方法

第二种移植方法与第一种方法相比具有明显的优势。

以数据类型转换为例。OMW 提供一个向导来指导您完成此过程，从而简化了移植过程。该工具推荐并提供了易于更改的默认值。下表列出了一些推荐值：

访问字段	默认值	推荐值
文本	Varchar2(999)	Varchar2(999)
数字 — 长整型	Number(11,0)	Number(11,0)
数字 — 字节型	Number(3,0)	Number(3,0)
数字 — 整型	Number(5,0)	Number(5,0)
日期/时间	Date	Date
布尔型	Number(1,0)	Number(1,) 或 char(1)

即使对于非 Access 数据库，OMW 也在集成环境中提供了对触发器、过程和数据库模式等转换的完整支持。可以转换的 Microsoft Access 版本是 2.0、95、97 和 2000。

下表总结了 Oracle 与 Access 之间的转换：

特性	支持的转换？
表	是
视图	否（只支持查询）
索引	是
组/角色	N/A
用户	否
约束	是（有效性规则）
权限	否
用户定义类型	N/A
存储过程	N/A
触发器	N/A
嵌入式 SQL	N/A
其他特性	关系、链接表、应用程序代码重用

下图演示了 OMW 在体系结构级别上的运作方式：

图 1：OMW 体系结构

 

OMW 使用向导和脚本，如下：

• 捕获向导捕获源数据库。
• 移植向导将源模型移植到 Oracle 模型。
• 脚本创建一系列描述数据库结构的文件，还将数据从源数据库复制到目标数据库。

 

在使用向导和脚本之后，您可以使用 OMW 用户接口来自定义 Oracle 数据模型。

为了移植不同的第三方数据库，OMW 使用了专用于它所能移植的各种数据库的插件，包括 SQL Server、DB2 和 Informix。必须至少与 OMW 一起安装一个插件。

Oracle 在 OTN 上存放了关于如何使用 OMW 进行移植的大量文档，并且提供免费下载。但是下面的概述表明该过程相当简单：

• 启动捕获向导。
• 单击 Next。
• 输入连接目标 Oracle 数据库所需的信息。
• 单击 Next。
• 出现“Migrate Tablespaces, Users, and User Tables”页面。
• 查看“Migrate Tablespaces, Users, and User Tables”页面上的信息，并单击 Next；出现“Migrate Table Data”页面。
• 指定在本移植向导会话期间是否传输数据。
• 单击 Next；出现“Select Schema Objects to Migrate”页面。
• 选择要移植的模式对象，并单击 Next；出现“Summary”页面。
• 查看摘要信息。
• 单击 Finish，启动移植过程。
• 出现一个进度屏幕，显示关于移植进度的信息。复杂数据库的移植过程可能需要很长时间。
• 在具有 Microsoft Access 插件时，出现“Modify Microsoft Access Database”屏幕。您还可以更改 Microsoft Access 数据库文件，以便 Microsoft Access 表单和报表使用 ODBC 连接来使用目标数据库：
  • 输入 ODBC 连接的用户名。ODBC 连接用户名是被移植数据库的名称。
  • 输入用户的口令。
  • 输入目标数据库的 ODBC 连接名称。
  • 出现一个进度屏幕，显示关于移植进度的信息。复杂数据库的移植过程可能需要很长时间。

结论

这里描述的步骤只是对过程的概述，过程可能更短或更长，这取决于所要转换的数据库。强烈建议使用 OMW 进行移植。对于此主题，您可以利用 Oracle 在线教育以及大量的论坛。有关将 Access 应用程序移植到 HTML DB 的信息，请阅读此技术白皮书。