使用 MS Access 作为 MySQL 数据库后端的前端的问题？

Issues using MS Access as a front-end to a MySQL database back-end?

提问by yukondude

Two users wanted to share the same database, originally written in MS Access, without conflicting with one another over a single MDB file.

两个用户想要共享同一个数据库，最初是用 MS Access 编写的，而不是因为一个 MDB 文件而相互冲突。

I moved the tables from a simple MS Access database to MySQL using its Migration Toolkit(which works well, by the way) and set up Access to link to those tables via ODBC.

我使用它的迁移工具包（顺便说一下，它运行良好）将表从一个简单的 MS Access 数据库移动到 MySQL ，并设置 Access 以通过 ODBC 链接到这些表。

So far, I've run into the following:

到目前为止，我遇到了以下情况：

• You can't insert/update/delete rows in a table without a primary key (no surprise there).
• AutoNumber fields in MS Access must be the primary key or they'll just end up as integer columns in MySQL (natch, why wouldn't it be the PK?)
• The tables were migrated to MySQL's InnoDB table type, but the Access relationships didn't become MySQL foreign key constraints.

• 你不能在没有主键的表中插入/更新/删除行（这并不奇怪）。
• MS Access 中的 AutoNumber 字段必须是主键，否则它们最终会在 MySQL 中作为整数列（natch，为什么不是 PK？）
• 表迁移到 MySQL 的 InnoDB 表类型，但访问关系没有成为 MySQL 外键约束。

Once the database is in use, can I expect any other issues? Particularly when both users are working in the same table?

一旦数据库投入使用，我还能期待任何其他问题吗？特别是当两个用户都在同一张桌子上工作时？

回答by

I know this topic is not too fresh, but just some additional explanations:

我知道这个话题不太新鲜，只是一些额外的解释：

If you want to use MS Access effectively, especially with bigger, multiuser databases, please do the following:

如果您想有效地使用 MS Access，尤其是对于更大的多用户数据库，请执行以下操作：

• split your MDB into frontend application and backend (data only) files - you'll have two separate MDB files then.

• migrate all the tables with data and structure into external database. It can be: MySQL (works very well, no database size limitations, requires some more skills as it's not MS technology, but it is a good choice in many cases - moreover you can scale your backend with more RAM and additional CPUs, so everything depends on your needs and hardware capabilities); Oracle (if you have enough money or some kind of corporate license) or Oracle 10g XE (if this is not a problem, that the database size is limited up to 4 GB and it will always use 1 GB of RAM and 1 CPU), MS SQL Server 2008 (it's a great pair to have MS Access frontend and MS SQL Server backend in all the cases, but you have to pay for license! - advantages are: close integration, both technologies are form the same vendor; MS SQL Server is very easy to maintain an effective at the same time) or Express edition (same story like with Oracle XE - almost the same limitations).

• relink your MS Access frontend with backend database. If you selected MS SQL Server for the backend then it will be as easy as to use the wizard from MS Access. For MySQL - you have to use ODBC drivers (it's simple and works very good). For Oracle - please do not use the ODBC drivers from Microsoft. These from Oracle will do their work much better (you can compare the time needed to execute SQL query from MS Access to Oracle via Oracle ODBC and MS Oracle ODBC drivers). At this point you'll have solid database backend and fully functional MS Access frontend - MDB file.

• compile your MDB frontend to MDE - it will give you a lot of speed. Moreover, it's the only reasonable form of distributing MS Access application to your end users.

• for daily work - use MDE file with MS Access frontend. For futher MS Access frontend development use MDB file.

• don't use badly written ActiveX components to enhance MS Access frontend capabilities. Better write them yourself or buy the proper ones.

• don't believe into the myths that there are a lot of issues with MS Access - this is a great product which can help in may occassions. The problem is a lot of people assume it's a toy or that MS Access is generaly simple. Usually they generate a lot of errors and issues by themselves and their lack of knowledge and experience. To be successfull with MS Access it is important to understand this tool - this is the same rule, like with any other technology outhere.

• 将您的 MDB 拆分为前端应用程序和后端（仅限数据）文件 - 然后您将拥有两个单独的 MDB 文件。

• 将所有带有数据和结构的表迁移到外部数据库中。它可以是： MySQL（运行良好，没有数据库大小限制，需要更多技能，因为它不是 MS 技术，但在许多情况下它是一个不错的选择 - 此外，您可以使用更多 RAM 和额外 CPU 来扩展后端，因此一切取决于您的需求和硬件能力）；Oracle（如果您有足够的资金或某种公司许可证）或 Oracle 10g XE（如果这不是问题，数据库大小限制为 4 GB，它将始终使用 1 GB 的 RAM 和 1 个 CPU）， MS SQL Server 2008（在所有情况下都拥有 MS Access 前端和 MS SQL Server 后端是一个很好的组合，但您必须支付许可费用！ - 优点是：紧密集成，两种技术来自同一供应商；

• 将您的 MS Access 前端与后端数据库重新链接。如果您为后端选择了 MS SQL Server，那么使用 MS Access 中的向导将非常简单。对于 MySQL - 你必须使用 ODBC 驱动程序（它很简单而且效果很好）。对于 Oracle - 请不要使用 Microsoft 的 ODBC 驱动程序。这些来自 Oracle 的工作会做得更好（您可以比较通过 Oracle ODBC 和 MS Oracle ODBC 驱动程序从 MS Access 到 Oracle 执行 SQL 查询所需的时间）。此时，您将拥有可靠的数据库后端和功能齐全的 MS Access 前端 - MDB 文件。

• 将您的 MDB 前端编译为 MDE - 它会给您带来很大的速度。此外，它是将 MS Access 应用程序分发给最终用户的唯一合理形式。

• 用于日常工作 - 将 MDE 文件与 MS Access 前端一起使用。对于进一步的 MS Access 前端开发，请使用 MDB 文件。

• 不要使用写得不好的 ActiveX 组件来增强 MS Access 前端功能。最好自己写或者买合适的。

• 不要相信 MS Access 存在很多问题的神话 - 这是一款出色的产品，可以在某些情况下提供帮助。问题是很多人认为它是一个玩具，或者 MS Access 通常很简单。通常他们自己和他们缺乏知识和经验会产生很多错误和问题。要成功使用 MS Access，了解这个工具很重要 - 这是相同的规则，就像外面的任何其他技术一样。

I can tell you that I'm using quite advanced MS Access fronted to MySQL backend and I'm very satisfied (as a developer which is maintaining this application). My friends, the users are also satisfied as they feel very comfortable with the GUI (frontend), the speed (MySQL), they don't have any issues with records locking or database performance.

我可以告诉您，我正在使用面向 MySQL 后端的非常先进的 MS Access，我非常满意（作为维护此应用程序的开发人员）。我的朋友们，用户也很满意，因为他们对 GUI（前端）、速度（MySQL）感到非常满意，他们在记录锁定或数据库性能方面没有任何问题。

Moreover, it's important to read a lot about good practices and other people experiences. I would say that in many cases MS Access is a good solution. I know a lot of dedicated, custom made systems which started as an experiment in form of private MS Access database (MDB file) and then evolved to: splitted MS Access (MDE - frontend, MDB - backend) and finally to: MS Access frontend (MDE) and "serious" database backend (mainly MS SQL Server and MySQL). It's also important that you can always use your MS Access solution as a working prototype - you have ready to use backend in your database (MySQL - let's assume) and you can rewrite frontend to the technology of your choice (web solution? maybe desktop C# application - what you require!).

此外，阅读大量关于良好实践和其他人的经验也很重要。我会说在很多情况下 MS Access 是一个很好的解决方案。我知道很多专用的、定制的系统，它们以私有 MS Access 数据库（MDB 文件）的形式作为实验开始，然后演变为：拆分 MS Access（MDE - 前端，MDB - 后端），最后到：MS Access 前端(MDE) 和“严肃的”数据库后端（主要是 MS SQL Server 和 MySQL）。同样重要的是，您始终可以将 MS Access 解决方案用作工作原型 - 您已准备好在数据库中使用后端（MySQL - 让我们假设），并且您可以将前端重写为您选择的技术（Web 解决方案？也许是桌面 C#应用程序 - 您需要什么！）。

I hope I helped some of you considering the work with MS Access.

我希望我能帮助你们中的一些人考虑使用 MS Access。

Regards, Wawrzyn http://dcserwis.pl

问候，Wawrzyn http://dcserwis.pl

回答by onnodb

I had an application that worked likewise: an MS Access frontend to a MySQL backend. It was such a huge pain that I ended up writing a Win32 frontend instead. From the top of my head, I encountered the following problems:

我有一个同样工作的应用程序：一个 MS Access 前端到一个 MySQL 后端。这是一个巨大的痛苦，我最终编写了一个 Win32 前端。从我的头顶，我遇到了以下问题：

• Development of the ODBC link seems to have ceased long ago. There are various different versions floating around --- very confusing. The ODBC link doesn't support Unicode/UTF8, and I remember there were other issues with it as well (though some could be overcome by careful configuration).
• You probably want to manually tweak your db schema to make it compatible with MS Access. I see you already found out about the needed surrogate keys (i.e., int primary keys) :-)
• You should keep in mind that you may need to use pass-through queries to do more sophisticated SQL manipulations of the MySQL database.
• Be careful with using lots of VBA, as that tends to corrupt your frontend file. Regularly compressing the database (using main menu, Tools | Database utilities | Compress and restore, or something like that --- I'm using the Dutch version) and making lotsof backups is necessary.
• Access tends to cause lots of network traffic. Like, really huge lots. I haven't been able to find a solution for that. Using a network monitor is recommended if you want to keep an eye on that!
• Access insists on storing booleans as 0/-1. IMHO, 0/+1 makes more sense, and I believe it is the default way of doing things in MySQL as well. Not a huge problem, but if your checkboxes don't work, you should definitely check this.

• ODBC 链接的开发似乎很久以前就停止了。有各种不同的版本漂浮着---非常混乱。ODBC 链接不支持 Unicode/UTF8，我记得它还有其他问题（尽管有些问题可以通过仔细配置来克服）。
• 您可能想要手动调整您的数据库架构以使其与 MS Access 兼容。我看到您已经发现了所需的代理键（即 int 主键）:-)
• 您应该记住，您可能需要使用传递查询来对 MySQL 数据库进行更复杂的 SQL 操作。
• 使用大量 VBA 时要小心，因为这往往会损坏您的前端文件。定期压缩数据库（使用主菜单、工具 | 数据库实用程序 | 压缩和还原，或类似的东西 --- 我使用的是荷兰语版本）并进行大量备份是必要的。
• 访问往往会导致大量网络流量。就像，真的很大很多。我一直无法找到解决方案。如果您想密切注意，建议使用网络监视器！
• Access 坚持将布尔值存储为 0/-1。恕我直言，0/+1 更有意义，我相信这也是 MySQL 中的默认处理方式。不是一个大问题，但如果您的复选框不起作用，您一定要检查这个。

One possible alternative would be to put the backend (with the data) on a shared drive. I remember this is well-documented, also in the help. You may want to have a look at some general advice on splitting into a frontend and a backendand code that automatically reconnects to the backend on startup; I can also send you some more sample code, or post it here.

一种可能的替代方法是将后端（带有数据）放在共享驱动器上。我记得这是有据可查的，也在帮助中。你可能想看看在分裂一些普遍性的建议为前端和后端和代码，自动重新连接到启动时的后端; 我还可以向您发送更多示例代码，或在此处发布。

Otherwise, you might also want to consider MS SQL. I don't have experience with that, but I presume it works together with MS Access much more nicely!

否则，您可能还想考虑 MS SQL。我没有这方面的经验，但我认为它与 MS Access 一起工作得更好！

回答by David-W-Fenton

Gareth Simpson opined:

加雷斯·辛普森认为：

If it's only two users, then Access should do just fine if you put the .mdb on a shared drive.

如果只有两个用户，那么如果您将 .mdb 放在共享驱动器上，Access 应该可以正常工作。

Er, no. There is no multi-user Access application for which each user should not have a dedicated copy of the front end. That means each user should have an MDB on their workstation. Why? Because the objects in front ends do not share well (not nearly as well as Jet data tables, though there aren't any of those in this scenario using MySQL as the back end).

呃没有。没有多用户 Access 应用程序，每个用户不应拥有前端的专用副本。这意味着每个用户的工作站上都​​应该有一个 MDB。为什么？因为前端的对象不能很好地共享（不如 Jet 数据表，尽管在这个场景中没有任何使用 MySQL 作为后端的对象）。

Gareth Simpson continued:

加雷斯·辛普森继续说道：

I believe the recommended max concurrent users for Access is 5 but on occasion I've pushed it past this and never come unstuck.

我相信 Access 的推荐最大并发用户数是 5，但有时我已经超过了这个并且永远不会被卡住。

No, this is completely incorrect. The theoretical limit for users of an MDB is 255. That's not realistic, of course, as once you reach about 20 users you have to program your Access app carefully to work well (though the things you need to do in an Access-to-Jet app are the same kinds of things you'd do to make any server database application efficient, e.g., retrieving the smallest usable data sets).

不，这是完全错误的。MDB 用户的理论限制是 255。当然，这是不现实的，因为一旦您达到大约 20 个用户，您必须仔细编程您的 Access 应用程序才能正常工作（尽管您需要在 Access-to- Jet 应用程序与您为使任何服务器数据库应用程序高效所做的事情相同，例如，检索最小的可用数据集）。

In this case, since each user should have an individual copy of the front-end MDB, the multi-user limits of Access/Jet are simply not relevant at all.

在这种情况下，由于每个用户都应该拥有前端 MDB 的单独副本，因此 Access/Jet 的多用户限制根本不相关。

回答by David-W-Fenton

Dont forget to put some type time/date stamp on each record. sometimes ms access will think "another user has changed or deleted the record" and will not allow you to make a change! I found this out the hard way.

不要忘记在每条记录上放置某种类型的时间/日期戳。有时 ms access 会认为“另一个用户更改或删除了记录”，并且不允许您进行更改！我发现这很困难。

回答by lomaxx

I know this doesn't answer your question directly, but it might be worth checking out the SQL Server 2005 migration tool for Access. I've never used the tool, but it might be worth using with SQL Server 2005 Express Edition to see if there are the same issues as you had with MySQL

我知道这不能直接回答您的问题，但可能值得查看SQL Server 2005 的 Access 迁移工具。我从来没有使用过这个工具，但它可能值得与 SQL Server 2005 Express Edition 一起使用，看看是否存在与 MySQL 相同的问题

回答by BIBD

In general, it depends :)

一般来说，这取决于:)

I haven't had a lot of problems when the application side has just been updating the data through the forms. You can get warnings/errors when the same row has been updated by more than one user; but Access seems to be constantly updating its live record sets all the time.

当应用程序端刚刚通过表单更新数据时，我没有遇到很多问题。当同一行被多个用户更新时，您可能会收到警告/错误；但是 Access 似乎一直在不断更新其实时记录集。

Problems can happen if Alice is already working with record 365, and the Bob updates it, and then Alice tries to update it with her changes. As I recall, Alice will get a cryptic error message. It would be easier for the users if you trap these errors and at least give them a friendlier error message.

如果 Alice 已经在使用记录 365，并且 Bob 更新它，然后 Alice 尝试用她的更改来更新它，那么问题就会发生。我记得，Alice 会收到一条神秘的错误消息。如果您捕获这些错误并至少给他们一个更友好的错误消息，那么对用户来说会更容易。

I've had more problems when I was editing records in the VB code through RecordSets, especially when combined with editing the same data on forms. That's not necessarily a multi user problem; however, you have almost the same situation because you have one user with multiple connections to the same data.

当我通过 RecordSets 在 VB 代码中编辑记录时，我遇到了更多问题，尤其是在与编辑表单上的相同数据相结合时。这不一定是多用户问题；但是，您的情况几乎相同，因为您有一个用户与同一数据的多个连接。

回答by Gareth Simpson

If it's only two users, then Access should do just fine if you put the .mdb on a shared drive.

如果只有两个用户，那么如果您将 .mdb 放在共享驱动器上，Access 应该可以正常工作。

Have you tried it first rather than just assume it will be a problem.

你有没有先尝试过，而不是假设它会有问题。

I believe the recommended max concurrent users for Access is 5 but on occasion I've pushed it past this and never come unstuck.

我相信 Access 的推荐最大并发用户数是 5，但有时我已经超过了这个并且永远不会被卡住。

On the other hand I did once use Access as the front end to MySQL in a single user environment (me). It was a singularly unpleasant experience, I can't imagine it would become nicer with two users.

另一方面，我曾经在单用户环境（我）中使用 Access 作为 MySQL 的前端。这是一次非常不愉快的体验，我无法想象有两个用户会变得更好。