We help IT Professionals succeed at work.

Ms Access mysql back up

Tony
Tony asked
on
I am using ms access as a front end for a mysql database.   I need to back up the database from where the ms access engine is located...i mean from a pc that is not the server.   How?
Comment
Watch Question

Daniel PineaultPresident / Owner CARDA Consultants Inc.
Distinguished Expert 2018

Commented:
I do not believe this to be natively possible.

You could write a proc to create a text file with the INSERT commands... yourself.  Basically write your own function to create a dump file, but this would probably be a slow process compared to using a Cron Job (see below).

Why not simply create a Cron job on the Server to create the dump file on regular intervals and then you can use VBA to grab a copy using FTP code?  Or your Cron Job could automatically FTP it somewhere or email it...
Distinguished Expert 2017

Commented:
When you use a "real" RDBMS as the BE for Access, you should be using the tools provided with that RDBMS to manage the database.
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
You could have a supported backup routine installed on the server that could be called on demand.
Then, from your workstation, call that command.
John TsioumprisSoftware & Systems Engineer

Commented:
Is the mysql running on Windows or Linux..?

Author

Commented:
Mysql Run in Windows...

Author

Commented:
Yes....I have an automatic schedule back up in the server.  However, I would like for a user to be able to back up manually also, from the user pc.
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Then, somehow, let your application send a command to the backup system to run an unscheduled backup.
Software & Systems Engineer
Commented:
Probably the easiest way is to have a scheduled task that checks e.g. every 1 min if a file is present in a network drive...if that's true it will execute a batch file to backup mysql.
Access will create this file :https://www.thespreadsheetguru.com/blog/vba-guide-text-files
Then it will execute a  script (bat) that is backing up MySQL : https://github.com/improy/MySQL-DataBase-backup-using-windows-bat-file/blob/master/MySqlBackup.bat
and finally delete the file.
If your "access" to the server is not very restrictive probably you could skip just everything and use SysInternals PsExec to execute remotely the mysqldump command..but its a bad idea in terms of security...but doable

Author

Commented:
Excellent