使用MS Access作为MySQL数据库后端的前端的问题？ [英] Issues using MS Access as a front-end to a MySQL database back-end?

问题描述

两个用户希望共享同一个数据库，最初是用MS Access编写的，而不是通过单个MDB文件相互冲突。

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数据库到MySQL使用其迁移工具包（其工作顺利，通过

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.

到目前为止，我遇到了以下问题：

So far, I've run into the following:

• 您不能在没有主键的表中插入/更新/删除行（不意外）。


• 自动编号字段MS Access必须是主键，否则它们只会在MySQL中成为整数列（natch，为什么不是PK？）


• 表被迁移到MySQL的InnoDB表类型，但Access关系不会成为MySQL外键约束。

一旦数据库使用，其他事宜？特别是当两个用户都在同一个表中工作时。

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

推荐答案

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

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:

• ODBC链接的开发似乎早就停止了。有各种不同的版本漂浮 - 很混乱。 ODBC链接不支持Unicode / UTF8，我记得还有其他问题（虽然一些可以通过仔细配置克服）。


• 你可能想手动调整您的数据库模式，使其与MS Access兼容。我看到你已经找到了所需的代理键（即int主键）： - ）


• 您应该记住，您可能需要使用传递查询复杂的SQL操作MySQL数据库。


• 小心使用大量的VBA，因为这往往会损坏您的前端文件。定期压缩数据库（使用主菜单，工具|数据库实用程序|压缩和恢复，或类似的东西---我使用荷兰语版本），并且必须进行批次 li>
  
• 访问会导致大量的网络流量。喜欢，真的很大。我还没有找到一个解决方案。如果您想密切注意，建议您使用网络监视器！


• 访问权限坚持将布尔值存储为0 / -1。 IMHO，0 / + 1更有意义，我相信这是在MySQL中做事情的默认方式。不是一个大问题，但如果你的复选框不工作，你一定要检查这个。

• 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 lots of 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.

一个可能的选择是后端（与数据）在共享驱动器上。我记得这是有记录，也在帮助。您可能需要查看有关拆分为前端和后端的一般建议和<在启动时自动重新连接到后端的href =http://allenbrowne.com/ser-13.html =noreferrer>代码;我也可以给你一些更多的示例代码，或发布在这里。

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 backend and code that automatically reconnects to the backend on startup; I can also send you some more sample code, or post it here.

否则，你可能还要考虑MS SQL。我没有这方面的经验，但我认为它与MS Access更好地工作！

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 Access作为MySQL数据库后端的前端的问题？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！