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

MS Access Application - Convert data storage from Access to SQL Server

提问by Ronnie Overby

Bear in mind here, I am not an Access guru. I am proficient with SQL Server and .Net framework. Here is my situation:

请记住，我不是 Access 专家。我精通 SQL Server 和 .Net 框架。这是我的情况：

A very large MS Access 2007 application was built for my company by a contractor.

一个非常大的 MS Access 2007 应用程序是由承包商为我的公司构建的。

The application has been split into two tiers BY ACCESS; there is a front end portion that holds all of the Ms Access forms, and then on the back end part, which are access tables, queries, etc., that is stored on a computer on the network.

该应用程序已通过 ACCESS 分为两层；有一个前端部分保存所有的 Ms Access 表单，然后在后端部分，即存储在网络上的计算机上的访问表、查询等。

Well, of course, there is a need to convert the data storage portion to SQL Server 2005 while keeping all of these GUI forms which were built in Ms Access. This is where I come in.

嗯，当然，需要将数据存储部分转换为 SQL Server 2005，同时保留所有这些在 Ms Access 中构建的 GUI 表单。这是我进来的地方。

I have read a little, and have found that you can link the forms or maybe even the access tables to SQL Server tables, but I am still very unsure on what exactly can be done and how to do it.

我读了一点，发现您可以将表单甚至访问表链接到 SQL Server 表，但我仍然非常不确定究竟可以做什么以及如何做。

Has anyone done this? Please comment on any capabilities, limitations, considerations about such an undertaking. Thanks!

有没有人做过这个？请评论有关此类任务的任何能力、限制和考虑。谢谢！

回答by Renaud Bompuis

Do notuse the upsizing wizard from Access:

不要使用 Access 中的升迁向导：

• First, it won't work with SQL Server 2008.

• Second, there is a much better tool for the job:
  SSMA, the SQL Server Migration Assistant for Accesswhich is provided for free by Microsoft.

• 首先，它不适用于 SQL Server 2008。

• 其次，有一个更好的工具来完成这项工作：
  SSMA，Microsoft 免费提供的用于Access的SQL Server 迁移助手。

It will do a lot for you:

它会为你做很多事情：

• move your data from Access to SQL Server
• automatically link the tables back into Access
• give you lots of information about potential issues due to differences in the two databases
• keeps track of the changes so you can keep the two synchronised over time until your migration is complete.

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

I wrote a blog entryabout it recently.

我最近写了一篇关于它的博客条目。

回答by MrTelly

You have a couple of options, the upsizing wizarddoes a decent(ish) job of moving structure and data from access to Sql. You can then setup linked tables so your application 'should' work pretty much as it does now. Unfortunately the Sql dialect used by Access is different from Sql Server, so if there are any 'raw sql' statements in the code they may need to be changed.

您有几个选择，升迁向导在将结构和数据从访问权限移动到 Sql 方面做得不错（ish）。然后，您可以设置链接表，以便您的应用程序“应该”像现在一样工作。不幸的是，Access 使用的 Sql 方言与 Sql Server 不同，因此如果代码中有任何“原始 sql”语句，它们可能需要更改。

As you've linked to tables though all the other features of Access, the QBE, forms and so on should work as expected. That's the simplest and probably best approach.

由于您已链接到表，但 Access 的所有其他功能、QBE、表单等都应按预期工作。这是最简单也可能是最好的方法。

Another way of approaching the issue would be to migrate the data as above, and then rather than using linked tables, make use of ADO from within access. That approach is kind of famaliar if you're used to other languages/dev environments, but it's the wrong approach. Access comes with loads of built in stuff that makes working with data really easy, if you go back to use ADO/Sql you then lose many of those benefits.

解决该问题的另一种方法是如上所述迁移数据，然后从访问中使用 ADO，而不是使用链接表。如果您习惯于其他语言/开发环境，则这种方法有点熟悉，但这是错误的方法。Access 带有大量内置内容，使处理数据变得非常容易，如果您回去使用 ADO/Sql，您将失去许多这些好处。

I suggest start on a small part of the application - non essential data, and migrate a few tables and see how it goes. Of course you back everything up first.

我建议从应用程序的一小部分开始——非必要数据，然后迁移一些表，看看它是如何进行的。当然，您首先要备份所有内容。

Good luck

祝你好运

回答by David-W-Fenton

Others have suggested upsizing the Jet back end to SQL Server and linking via ODBC. In an ideal world, the app will work beautifully without needing to change anything.

其他人建议将 Jet 后端升级到 SQL Server 并通过 ODBC 进行链接。在理想的世界中，该应用程序无需更改任何内容即可完美运行。

In the real world, you'll find that some of your front-end objects that were engineered to be efficient and fast with a Jet back end don't actually work very well with a server database. Sometimes Jet guesses wrong and sends something really inefficient to the server. This is particular the case with mass updates of records -- in order not to hog server resources (a good thing), Jet will send a single UPDATE statement for each record (which is a bad thing for your app, since it's much, much slower than a single UPDATE statement).

在现实世界中，您会发现一些前端对象设计为使用 Jet 后端高效和快速，实际上并不能很好地与服务器数据库配合使用。有时 Jet 猜测错误并向服务器发送一些非常低效的信息。大量更新记录的情况尤其如此——为了不占用服务器资源（一件好事），Jet 将为每条记录发送一个 UPDATE 语句（这对您的应用程序来说是一件坏事，因为它太多了比单个 UPDATE 语句慢）。

What you have to do is evaluate everything in your app after you've upsized it and where there are performance problems, move some of the logic to the server. This means you may create a few server-side views, or you may use passthrough queries (to hand off the whole SQL statement to SQL Server and not letting Jet worry about it), or you may need to create stored procedures on the server (especially for update operations).

您必须做的是在升级应用程序后评估应用程序中的所有内容以及存在性能问题的地方，将一些逻辑移至服务器。这意味着您可以创建一些服务器端视图，或者您可以使用传递查询（将整个 SQL 语句交给 SQL Server 而不让 Jet 担心），或者您可能需要在服务器上创建存储过程（特别是对于更新操作）。

But in general, it's actually quite safe to assume that mostof it will work fine without change. It likely won't be as fast as the old Access/Jet app, but that's where you can use SQL Profiler to figure out what the holdup is and re-architect things to be more efficient with the SQL Server back end.

但总的来说，假设其中大部分内容无需更改即可正常工作，实际上是相当安全的。它可能不会像旧的 Access/Jet 应用程序那样快，但您可以在那里使用 SQL Profiler 来确定阻塞是什么，并重新构建事物以提高 SQL Server 后端的效率。

If the Access app was already efficiently designed (e.g., forms are never bound to full tables, but instead to recordsources with restrictive WHERE clauses returning only 1 or a few records), then it will likely work pretty well. On the other hand, if it uses a lot of the bad practices seen in the Access sample databases and templates, you could run into huge problems.

如果 Access 应用程序的设计已经很有效（例如，表单永远不会绑定到完整的表，而是绑定到具有限制性 WHERE 子句仅返回 1 条或几条记录的记录源），那么它可能会工作得很好。另一方面，如果它使用了许多在 Access 示例数据库和模板中看到的不良做法，您可能会遇到巨大的问题。

It's my opinion that every Access/Jet app should be designed from the beginning with the idea that someday it will be upsized to use a server back end. This means that the Access/Jet app will actually be quite efficient and speedy, but also that when you doupsize, it will cause a minimum of pain.

我的观点是，每个 Access/Jet 应用程序都应该从一开始就考虑到有一天它会被升级以使用服务器后端。这意味着访问/喷气应用程序将会变得非常的高效，快捷，而且，当你做增发，就会造成痛苦最小。

回答by Dave Markle

This is your lowest-cost option. You're going to want to set up an ODBC connection for your Access clients pointing to your SQL Server. You can then use the (I think) "Import" option to "link" a table to the SQL Server via the ODBC source. Migrate your data from the Access tables to SQL Server, and you have your data on SQL Server in a form you can manage and back up. Important, queries can then be written on SQL Server as views and presented to the Access db as linked tables as well.

这是您成本最低的选择。您将要为指向 SQL Server 的 Access 客户端设置 ODBC 连接。然后，您可以使用（我认为）“导入”选项通过 ODBC 源将表“链接”到 SQL Server。将数据从 Access 表迁移到 SQL Server，SQL Server 上的数据就会以一种可以管理和备份的形式存在。重要的是，查询可以作为视图写在 SQL Server 上，也可以作为链接表呈现给 Access 数据库。

回答by reinierpost

Linked Access tables work fine but I've only used them with ODBC and other databases (Firebird, MySQL, Sqlite3). Information on primary or foreign keys wasn't passing through. There were also problems with datatype interpretation: a date in MySQL is not the same thing as in Access VBA. I guess these problems aren't nearly as bad when using SQL Server.

链接访问表工作正常，但我只将它们用于 ODBC 和其他数据库（Firebird、MySQL、Sqlite3）。主键或外键的信息没有通过。数据类型解释也存在问题：MySQL 中的日期与 Access VBA 中的日期不同。我猜这些问题在使用 SQL Server 时并没有那么严重。

回答by reinierpost

Take a look at this Access to SQL Server migration tool. It might be one of the few, if not the ONLY, true peer-to-peer or server-to-server migration tools running as a pure Web Application. It uses mostly ASP 3.0, XML, the File System Object, the Data Dictionary Object, ADO, ADO Extensions (ADOX), the Dictionary Scripting Objects and a few other neat Microsoft techniques and technologies. If you have the Source Access Table on one server and the destination SQL Server on another server or even the same server and you want to run this as a Web Internet solution this is the product for you. This example discusses the VPASP Shopping Cart, but it will work for ANY version of Access and for ANY version of SQL Server from SQL 2000 to SQL 2008.

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

I am finishing up development for a generic Database Upgrade Conversion process involving the automated conversion of Access Table, View and Index Structures in a VPASP Shopping or any other Access System to their SQL Server 2005/2008 equivalents. It runs right from your server without the need for any outside assistance from external staff or consultants.

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

After creating a clone of your Access tables, indexes and views in SQL Server this data migration routine will selectively migrate all the data from your Access tables into your new SQL Server 2005/2008 tables without having to give out either your actual Access Database or the Table Contents or your passwords to anyone.

在 SQL Server 中创建 Access 表、索引和视图的克隆后，此数据迁移例程将有选择地将 Access 表中的所有数据迁移到新的 SQL Server 2005/2008 表中，而无需提供实际的 Access 数据库或表内容或您的密码给任何人。

Here is the Reverse Engineering part of the process running against a system with almost 200 tables and almost 300 indexes and Views which is being done as a system acceptance test. Still a work in progress, but the core pieces are in place.

这是针对具有近 200 个表和近 300 个索引和视图的系统运行的过程的逆向工程部分，这是作为系统验收测试完成的。仍在进行中，但核心部分已就位。

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

http://www.21st Centuryecommerce.com/SQLDDL/ViewDBTables.asp

I do the automated reverse engineering of the Access Table DDLs (Data Definition Language) and convert them into SQL equivalent DDL Statements, because table structures and even extra tables might be slightly different for every VPASP customer and for every version of VP-ASP out there.

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

I am finishing the actual data conversion routine which would migrate the data from Access to SQL Server after these new SQL Tables have been created including any views or indexes. It is written entirely in ASP, with VB Scripting, the File System Object (FSO), the Dictionary Object, XML, DHTML, JavaScript right now and runs pretty quickly as you will see against a SQL Server 2008 Database just for the sake of an example.

我正在完成实际的数据转换例程，该例程将在创建这些新 SQL 表（包括任何视图或索引）后将数据从 Access 迁移到 SQL Server。它完全用 ASP 编写，包含 VB 脚本、文件系统对象 (FSO)、字典对象、XML、DHTML、JavaScript，并且运行速度非常快，正如您将在 SQL Server 2008 数据库中看到的那样例子。

It takes perhaps 15-20 seconds to reverse engineer almost 500 different database objects. There might be a total of over 2,000 columns involved in this example for the 170 tables and 270 indexes involved.

对近 500 个不同的数据库对象进行逆向工程可能需要 15-20 秒。对于涉及的 170 个表和 270 个索引，本示例中总共可能涉及 2,000 多个列。

I have even come up with a way for you to run both VPASP systems in parallel using 2 different database connection files on the same server just to be sure that orders entered on the Access System and the SQL Server system produce the same results before actual cutover to production.

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

John (a/k/a The SQL Dude) [email protected] (This is a VP-ASP Demo Site)

John (a/k/a The SQL Dude) [email protected]（这是一个 VP-ASP 演示站点）

回答by HardCode

Important Point: If you link the tables in Access to SQL Server, then EVERY table must have a Primary Key defined (Contractor? Access? Experience says that probably some tables don't have PKs). If a PK is not defined, then the Access forms will not be able to update and insert rows, rendering the tables effectively read-only.

要点：如果将 Access 中的表链接到 SQL Server，那么每个表都必须定义一个主键（Contractor？Access？经验表明，可能有些表没有 PK）。如果未定义 PK，则 Access 表单将无法更新和插入行，从而将表格呈现为只读。

回答by JeffO

Here is a technique I've heard one developer speak on. This is if you really want something like a Client-Server application.

这是我听到一位开发人员谈到的一种技术。这是如果您真的想要像客户端 - 服务器应用程序那样的东西。

1. Create .mdb/.mde frontend files distributed to each user (You'll see why).
2. For every table they need to perform an CRUD, have a local copy in the file in #1.
3. The forms stay linked to the local tables.
4. Write VBA code to handle the CRUD from the local tables to the SQL Server database.
5. Reports can be based off of temp tables created from the SQL Server (Won't be able to create temp tables in mde file I don't think).

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

Once you decide how you want to do this with a single form, it is not too difficult to apply the same technique to the rest. The nice thing about working with the form on a local table is you can keep a lot of the existing functionality as the existing application (Which is why they used and continue to use Access I hope). You just need to address getting data back and forth to the SQL Server.

一旦您决定如何使用单个表单执行此操作，将相同的技术应用于其余表单并不太困难。在本地表上使用表单的好处是您可以将许多现有功能保留为现有应用程序（这就是他们使用并继续使用 Access 我希望的原因）。您只需要解决在 SQL Server 中来回获取数据的问题。

You can continue to have linked tables, and then gradually phase them out with this technique as time and performance needs dictate.

您可以继续拥有链接表，然后根据时间和性能需要使用此技术逐步淘汰它们。

Since each user has their own local file, they can work on their local copy of the data. Only the minimum required to do their task should ever be copied locally. Example: if they are updating a single record, the table would only have that record. When a user adds a new record, you would notice that the ID field for the record is Null, so an insert statement is needed.

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

I guess the local table acts like a dataset in .NET? I'm sure in some way this is an imperfect analogy.

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