在MS Access和MySQL之间同步数据的最佳方法是什么？ [英] What is the best way to synchronize data between MS Access and MySQL?

问题描述

我在windows机器上有一个访问数据库，我必须导入到linux web服务器上的mysql。目前，访问dabatbase表导出为文本文件，自动使用ftp复制，然后加载到mysql。

I have an access database on a windows machine, which I must import into mysql on a linux webserver. At the moment the access dabatbase table is exported as a text file, automatically copied with ftp, and then loaded into mysql.

有更好的方法做这个，也许使用ODBC连接或其他东西？

Is there a better way to do this, perhaps using an ODBC connection or something else?

限制复制已经存在的信息的最佳方法是只传输访问数据库中但尚未在mysql中的记录。

What is the best way to limit copying information that is already present, ie to only transfer records that are in the access database but not yet in the mysql one.

访问数据库由另一个程序处理，如果我不需要更改它，甚至打开它，这将是最好的。没有可能需要从访问数据库导入更新的记录。 mysql数据库将是主要来源，一些记录将被删除或更改。

The access database is handled by another program, and it would be best if I did not have to makes changes to it, or even open it. There is no possibility of needing to import updated records from the access database. The mysql database will be the main source, and some records will be deleted or changed. I only want to import records that were never in the mysql database, not restore the ones that were purposefully deleted.

推荐答案

为什么不使用按照Keltia的建议通过ODBC链接表，然后使用一系列查询添加缺失的记录并更新更改的记录。查询可以通过VBA运行。 ADO也将与MySQL和Access协同工作。

Why not link the tables through ODBC as suggested by Keltia, and then use a series of queries to add records that are missing and to update changed records. The queries can be run through VBA. ADO will also work well with MySQL and Access.

Access中使用的代码的一些注释：

Some notes on code used in Access:

   Set db = CurrentDb
   strSQL = "Insert INTO [ODBC;DSN=DSNName;].NameOfMySQLTable " _
   & "Select AnyField As NameOfMySQLField FROM AnyAccessTable;"

   db.Execute strSQL, dbFailOnError
   End Sub

- http://forum.lessthandot.com/viewtopic.php?f = 95& t = 3862

这篇关于在MS Access和MySQL之间同步数据的最佳方法是什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！