Geek Answers Handbook

What is the best way to sync data between MS Access and MySQL?

I have an access database on a Windows machine that I have to import into mysql on a linux web server. Currently, the dabatbase access table is exported as a text file, automatically copied from ftp, and then loaded into mysql.

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

What is the best way to limit the copying of existing information, i.e. only transfer records that are in the access database but not yet included in mysql.

The access database is being processed by another program, and it would be better if I did not have to make changes to it or even open it. There is no need to import updated records from the access database. The main source will be the mysql database, and some records will be deleted or modified. I only want to import records that have never been in the mysql database, and not restore those that were purposely deleted.

+5
source share
5 answers

ODBC, Keltia, . VBA. ADO MySQL Access.

, 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

+3

, - script, ( ODBC ) . , , , mysql .

+1

, Access, - RecordChanges.

:

RecordChangeID (int) - Primary Key (Autonumber)
TableName (varchar(250)) - Name of table that changed
RecordID (int) - ID of the record in that table that was added / modified
RecordAction (char(1)) - 'A' if add, 'M' if modified or 'D' if deleted

. , .

, , , MySQL.

, , ExportStatus, :

LastRecordChangeID (int) - ID of the last Record Change 
                           you exported in the Record Changes table

( ExportStatus) SQL MySQL, ExportStatus, . RecordChange, , .

, , , .

0

.

DB5 MySQL

0

:

Access MySQL, "" . , ODBC, MySQL, Access "".

  • UPDATE script MySql,
  • INSERT script MySql,
  • (, , , ): DELETE script MySql .
  • temp MySql.

This can be done from within MS Access via linked tables. But performance will not be as good, and Access statements will need to be changed when / if your MySql tables change.

This answer may be useful to you and .

-1
source

More articles:


All Articles