使用 OBDC 将 .sql 导入 MS Access
我目前在 MySQL 中有一个数据库，我想将其导入到 MS Access 中。
是否可以在保持所有关系完好无损的情况下执行此操作（即不导出到 .csv，或通过使用 ODBC）？
我是这个领域的菜鸟，因此非常感谢任何帮助。
谢谢。
I currently have a database in MySQL, which I'd like to import in MS Access.
Is it possible to do this while keeping all relationships intact (i.e. without exporting to .csv, or by using ODBC)?
I'm a noob in this area so any help is greatly appreciated.
Thanks.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
您需要解决两个不同的问题：
创建一个空的 MS Access 数据库，其结构与 MySQL 数据库结构匹配。
从 MySQL 中提取数据并将其加载到 MS Access 中。
这并不容易，因为不同的 SQL 数据库提供不同的结构特征、不同的数据类型等等。 MySQL 使用越复杂，在转换过程中就越有可能遇到一些阻碍（例如，Access 根本不支持触发器）。相反，如果您使用 MySQL 作为简单的数据存储，您可能会发现转换相当容易。
要获得与 MySQL 数据库结构相同的 MS Access 数据库，最好的选择是找到一个提供逆向工程并支持 MySQL 和 MS Access 的数据库定义/图表工具。使用它将您的 MySQL 数据库逆向工程为数据库图，然后将底层数据库更改为 MS Access 并使用该工具生成数据库。
查看Dezign For Databases，它（无论如何，在纸面上）提供了执行此操作所需的功能。
为了传输数据，有许多工具。这种操作一般称为ETL（Extract、Translate、Load）。
You need to solve two different problems:
Creating an empty MS Access database with a structure that matches the MySQL database structure.
Extracting the data from MySQL and loading it into MS Access.
This is not easy because different SQL databases offer different structural features, different datatypes, and so on. The more complex your use of MySQL is the more likely you'll run into some show-stopper during the conversion (for instance, Access doesn't support triggers at all). Conversely if you're using MySQL as a simple data store you may find the conversion fairly easy.
To get an MS Access database with the same structure as your MySQL database, your best bet is to find a database definition / diagramming tool that offers reverse engineering and supports both MySQL and MS Access. Use it to reverse engineer your MySQL database into a database diagram, then change the underlying database to MS Access and use the tool to generate a database.
Check out Dezign For Databases which (on paper, anyway) offers the features you would need to do this.
To pump the data across, there are any number of tools. This kind of operation is generically referred to as ETL (Extract, Translate, Load).
你是说SQL Server吗？一个好的起点可能是查看 SQL Server Integration Services (SSIS)，它可用于像这样传输数据。
Google 也会有所帮助，请查看第一个结果：
http://support.microsoft.com/kb/237980 顺便说一句
，您在问题中提到了“.sql”：.SQL 文件是一个脚本文件，它可以执行任何操作，从创建数据库、插入数据、删除表、删除数据或给予正确的权限，调用系统程序并重新启动计算机、格式化驱动器、发送电子邮件......仅供参考，.SQL 文件不是 SQL Server 使用的存储格式。
Do you mean SQL Server? A good starting point might be to check out SQL Server Integration Services (SSIS), which can be used for transferring data around like that.
Google will also be helpful, check out the first result:
http://support.microsoft.com/kb/237980
By the way, you said ".sql" in your question: a .SQL file is a script file, which could do anything from create a database, insert data, drop table, delete data, or given the right permissions, call system procedures and reboot a machine, format a drive, send an email.. Just for ref, .SQL files aren't the storage format used by SQL Server.
虽然您可以通过 SQLyog 之类的工具将数据库模式编写到脚本文件中，但您会发现数据库之间的语法差异很大（在您的情况下为 MySQL 到 Access），您无法直接应用脚本。
经过大量努力，可以通过编辑脚本来创建转换脚本（可能使用程序自动化，具体取决于生成的脚本大小）。我认为使用 ODBC 复制表（和数据），然后手动从生成的脚本中提取并重新应用关系会更好。耗时，但也是我希望的一次性操作。
当两个系统是相同的数据库时，有一些工具可以进行比较和脚本生成（TOAD for MySQL 和 RedGate Compare for Microsoft SQL），但它们不执行跨数据库工作（至少我知道的不是） ）。
While you can script your database's schema into script files via something like SQLyog, you will find that the syntax varies enough from database to database (MySQL to Access, in your case) that you can't directly apply the scripts.
With much effort a conversion script could be created by editing the script (perhaps automated with a program, depending on the resulting script size). I think you would be better served using ODBC to copy the tables (and data) and then extracting and re-applying the relationships from the generated script by hand. Time consuming, but also a one time operation I would hope.
When both systems are the same database, there are tools that can do the comparison and script generation (TOAD for MySQL and RedGate Compare for Microsoft SQL), but they don't do cross database work (at least not the ones I am aware of).
如果创建 ODBC DSN，则可以使用 TransferDatabase 从 MySQL 数据库导入。您可以使用 GET EXTERNAL DATA 命令（或 A2007/A2010 中的任何命令）手动执行此操作，并查看其工作效果如何。它不会使所有数据类型完全正确，但您可以进行一些调整，并可能使其更接近最有效的数据类型。
是否有某种原因不能直接链接到 MySQL 表并直接使用它们？也就是说，为什么需要导入到 Access 中？
If you create a ODBC DSN, you can use TransferDatabase to import from your MySQL database. You can do it manually with the GET EXTERNAL DATA command (or whatever it is in A2007/A2010) and see how well it works. It won't get all data types exactly right, but you could do some massaging and likely get it closer to what will work best.
Is there some reason you can't just link to the MySQL tables and use them directly? That is, why do you need to import into Access at all?
访问：运行查询。只需确保调整 SQL 代码，因为每个 RDMS 都有自己的 sintaxis（尽管 SQL 是 ANSI 标准）。
Access: run query. Just make sure to adapt the SQL code since every RDMS has its own sintaxis (despite SQL being an ANSI standard).