logo标签列表

MS Access 应用程序 - 将数据存储从 Access 转换到 SQL Server

sql-serversql-server-2005ms-accessvbams-access-2007
6
请注意，我不是Access专家。我熟练掌握SQL Server和.Net框架。以下是我的情况：
一位承包商为我们公司建立了一个非常大的MS Access 2007应用程序。
该应用程序已经被ACCESS分成两个层次; 前端部分包含所有Ms Access表单，而后端部分存储在网络上的计算机上的访问表、查询等。
当然，有必要将数据存储部分转换为SQL Server 2005，同时保留在Ms Access中构建的所有GUI表单。这就是我介入的地方。
我已经阅读了一些资料，并发现可以将表单甚至访问表链接到SQL Server表，但仍然不确定到底可以做什么以及如何做。
是否有人完成了这个过程？请在此评论任何关于这样一个项目的能力、限制和注意事项。谢谢！
8个回答
12

不要使用Access中的升级向导:

它将为您做很多事情：

  • 将您的数据从Access移动到SQL Server
  • 自动将表格链接回Access
  • 提供关于两个数据库差异潜在问题的大量信息
  • 跟踪变更，以便在迁移完成之前随时保持两者同步。

我最近写了一篇博客文章介绍这个工具。

1
这是 SQL Server 2005 SSMA for Access 的链接： http://www.microsoft.com/sqlserver/2005/en/us/migration-access.aspx - Ronnie Overby
我发现SSMA非常有用。您可以使用它来重复转换应用程序，随着您进行迭代更改、测试并再次执行，直到完成为止。 - JimS-CLT
3
你有几个选择，upsizing wizard可以相对不错地将Access中的结构和数据移动到Sql。然后你可以设置链接表，使应用程序基本上可以像现在一样工作。不幸的是，Access使用的Sql方言与Sql Server不同，因此如果代码中有任何“原始sql”语句，则可能需要更改。
但是，由于你已经链接了表格，Access的所有其他功能，如QBE、表单等都应该按预期工作。这是最简单也是最好的方法。
另一种方法是像上面那样迁移数据，然后不使用链接表，而是在Access内部使用ADO。如果你习惯于其他编程语言或开发环境，这种方法可能比较熟悉，但这是错误的方法。Access带有许多内置功能，可以使处理数据变得非常容易，如果你回到使用ADO/Sql，就会失去许多这些优势。
我建议从应用程序的一个小部分开始——非必要数据，并迁移几个表格，看看情况如何。当然，在此之前请备份所有内容。
祝你好运！
ODBC负责将Jet SQL转换为与后端兼容的SQL。换句话说，没有在Access中编写的Jet SQL无法被正确解释，当Jet将其发送到ODBC驱动程序时。这并不意味着它会很好地工作，但它会工作。 - David-W-Fenton
顺便提一句，在 Access 2003 版本以后，你可以选择 SQL92 选项来使用 SQL，这意味着你可以在 Access 中使用与 SQL Server 兼容的 SQL（如果你不熟悉 Jet SQL 方言，你可以使用你熟悉的语言）。 - David-W-Fenton
不确定您所说的“SQL Server兼容的SQL”是什么意思。它被称为ANSI-92查询模式，更类似于SQL Server语法，例如Access帮助文档中所述：“在Microsoft SQL Server数据库中运行时需要最少更改的查询”（http://office.microsoft.com/en-us/access/HP030704831033.aspx）。 - onedaywhen
...有很多ACE/Jet SQL关键字（例如IIF），在ANSI-92查询模式下可以正常运行，但在SQL Server上会出错，除非进行更改（例如将IIF替换为CASE..WHEN..ELSE..END）。顺便说一句，查询模式被称为“ANSI-92”，而不是“SQL-92”，此标准与“ANSI-92”不符合！ - onedaywhen
3
其他人建议将Jet后端升级到SQL Server并通过ODBC连接。在理想的情况下，应用程序将在不需要更改任何内容的情况下完美运行。
但在现实世界中，您会发现一些针对Jet后端设计的前端对象在使用服务器数据库时效率和速度并不高。有时Jet猜错了，并发送了一些非常低效的内容到服务器。这在记录的大量更新时特别明显 - 为了不独占服务器资源（这是好事），Jet将为每个记录发送单个UPDATE语句（这对于您的应用程序来说是一个坏事，因为比单个UPDATE语句慢得多）。
您需要评估升级后的应用程序中的所有内容，并在出现性能问题时将某些逻辑移到服务器上。这意味着您可能需要创建一些服务器端视图，或者使用透传查询（将整个SQL语句交给SQL Server而不让Jet担心它），或者您可能需要在服务器上创建存储过程（特别是对于更新操作）。
但总的来说，可以认为大部分内容都可以正常工作，不需要进行更改。它可能不像旧的Access / Jet应用程序那样快，但这就是您可以使用SQL Profiler来找出问题所在并重新设计以使其与SQL Server后端更加高效的地方。
如果Access应用程序已经设计得非常高效（例如，表单从不绑定到完整的表格，而是绑定到具有限制WHERE子句并仅返回1个或几个记录的记录源），那么它很可能会工作得非常好。另一方面，如果它使用了许多在Access示例数据库和模板中看到的不良实践，则可能会遇到巨大的问题。
我认为每个Access / Jet应用程序都应该从一开始就考虑到将来将其升级以使用服务器后端。这意味着Access / Jet应用程序实际上将非常高效和快速，但也意味着当您进行升级时，它将最小化痛苦。
2
这是您最低成本的选择。 您需要为Access客户端设置一个指向SQL Server的ODBC连接。 然后，您可以使用“导入”选项通过ODBC源将表格“链接”到SQL Server。 将数据从Access表迁移到SQL Server，这样您就可以在SQL Server上以可管理和备份的形式管理数据。 重要的是，查询可以在SQL Server上编写为视图，并作为链接表格呈现给Access数据库。
0

看看这个Access到SQL Server迁移工具。它可能是为数不多的，如果不是唯一的，真正的点对点或服务器到服务器迁移工具，作为纯Web应用程序运行。它主要使用ASP 3.0、XML、文件系统对象、数据字典对象、ADO、ADO扩展（ADOX）、字典脚本对象和其他一些微软技术和技巧。如果您在一个服务器上拥有源Access表，而在另一个服务器上甚至是同一台服务器上拥有目标SQL Server，并且您想将其作为Web Internet解决方案运行，那么这就是适合您的产品。此示例讨论了VPASP购物车，但它适用于任何版本的Access和任何版本的SQL Server，从SQL 2000到SQL 2008。

我正在完成一个通用的数据库升级转换过程的开发，涉及自动转换VPASP购物或任何其他Access系统中的Access表、视图和索引结构为它们的SQL Server 2005/2008等效项。它可以直接从您的服务器运行，无需任何外部员工或顾问的帮助。

在将Access表、索引和视图克隆到SQL Server之后，此数据迁移程序将有选择地将所有数据从Access表迁移到新的SQL Server 2005/2008表中，而无需向任何人公开实际的Access数据库或表内容或密码。

这是反向工程过程的一部分，针对一个拥有近200个表和近300个索引和视图的系统进行系统验收测试。虽然仍在进行中，但核心部分已经就位。

http://www.21stcenturyecommerce.com/SQLDDL/ViewDBTables.asp

我进行了Access表DDL（数据定义语言）的自动反向工程，并将其转换为SQL等效DDL语句，因为每个VPASP客户和每个版本的VP-ASP中的表结构甚至额外的表可能略有不同。

在创建这些新的SQL表（包括任何视图或索引）之后，我正在完成实际数据转换例程，该例程将从Access迁移到SQL Server。它完全使用ASP编写，具有VB脚本，文件系统对象（FSO），字典对象，XML，DHTML，JavaScript，目前运行速度非常快，您将看到一个SQL Server 2008数据库的示例。

反向工程近500个不同的数据库对象可能需要15-20秒钟。在此示例中，涉及170个表和270个索引，总共可能涉及超过2,000列。

我甚至想出了一种方法，让您同时运行两个VPASP系统，使用同一服务器上的2个不同的数据库连接文件，以确保在实际投入生产之前，在Access系统和SQL Server系统上输入的订单产生相同的结果。

约翰（又名SQL小子） sales@designersyles.biz （这是VP-ASP演示站点）

0

链接的访问表可以正常工作，但我只使用过它们与ODBC和其他数据库（Firebird，MySQL，Sqlite3）一起使用。 主键或外键的信息未被传递。 数据类型解释也存在问题：MySQL中的日期与Access VBA中不是相同的东西。 我想当使用SQL Server时，这些问题远没有那么严重。

在Access中，唯一的链接表格形式是ODBC，除非有内置的ISAM已经处理它（例如Excel，DBF）。 - David-W-Fenton
0
重要提示：如果在Access中将表链接到SQL Server，则每个表都必须定义主键（承包商？Access？经验表明，可能有些表没有主键）。如果未定义主键，则Access窗体将无法更新和插入行，从而使表有效地为只读。
实际上，乍一看，这个数据库似乎设计得很好。我查看了关系图，显示了大多数表之间的关系，这表明使用了主键。 - Ronnie Overby
在所有的表格中加入时间戳字段非常有帮助——这样可以让Access在刷新数据时无需检查记录中的所有字段。作为一种惯例，我会在所有由Access前端使用的SQL Server表格中包含一个时间戳字段。 - David-W-Fenton
并不是每个表都必须有主键，虽然SQL Server偏好于这么做并且这是一个好的规范实践。如果你要更新表格，则没有主键会导致在SS后端表格中遇到问题，所以如果你要更新表格，则需要添加一个主键。SSMA会根据需要自动添加时间戳字段。 - JimS-CLT
-3

这里有一个我听过的开发者提到的技巧。如果你真的想要像客户端-服务器应用程序一样的东西，可以试试这个方法：

  1. 创建分发给每个用户的 .mdb/.mde 前端文件（你会明白为什么）。
  2. 对于他们需要执行 CRUD 操作的每个表，在第 1 步中的文件中都有一个本地副本。
  3. 表单保持与本地表格的链接。
  4. 编写 VBA 代码来处理从本地表格到 SQL Server 数据库的 CRUD 操作。
  5. 报告可以基于从 SQL Server 创建的临时表（我不认为能在 mde 文件中创建临时表）。

一旦你决定如何使用单个表单进行操作，将同样的技术应用到其他方面并不太困难。使用本地表格上的表单的好处是，你可以保留许多现有应用程序的现有功能（这就是他们使用 Access 的原因和继续使用的原因）。你只需要解决如何在 SQL Server 和本地表格之间传输数据。

你可以继续使用链接表，然后根据时间和性能需求逐渐淘汰它们。

由于每个用户都有自己的本地文件，他们可以在本地数据的副本上工作。只有完成任务所需的最小要求才会被本地复制。例如：如果他们正在更新单个记录，则表格只会有该记录。当用户添加新记录时，您会注意到记录的ID字段为空，因此需要插入语句。

我猜本地表就像.NET中的数据集？我确信在某种程度上这是一个不完美的类比。

不确定为什么绑定表单到本地表格并将其作为记录源是您建议中的关键点（我也不会绑定到原始表），这并不是一个很大的问题。 - JeffO
复制数据总是一个不好的主意。当您将更新应用于真实数据时，您必须处理锁定/冲突。在我看来，这是在寻找问题的解决方案。 - David-W-Fenton
这需要在服务器数据上设置一个字段来“标记”由于断开的数据集/本地表而被锁定的字段。所有非动态记录集都是数据的副本吗？（通过这次讨论/辩论我学到了很多 - 谢谢。） - JeffO
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接