Updating MySQL database with Access data
Sorry if there is a simple answer to this. I have a local Access database which is updated every ten minutes using a scheduled task in Windows. 0 or more records are then dumped from Access to a .sql file on my local machine.
I need a script/program that can connect to my remote SQL Server and execute the .sql file (containing 0 or more INSERT blah blah blah INTO blah statements) and then remove the local .sql file once complete. This is to run silently.
If there is an easier way to update a MySQL database with only new records, I would also be interested.
The upcoming MySQL Migration Toolkit is currently in Alpha and supports Access-MySQL migrations. If you have a .SQL file you can always write a batch file and run it at regular intervals.
The main command to concern yourself with is:
c:\pathto-mysql\bin\mysql -u [username] -p[YOURPASSWORD] [databasename] < c:\yourfile.sql
This was first published in March 2005