How do I get a mysql databse into Access

[avayaman]

I have a standalone MS Access database that I want to be able to run off a server so people across the country can access & work with it. I have managed to get all the tables into a mysql database on the server and I have an ODBC driver installed on my PC that can connect to it. Here is where I am lost. This is access 2003, BTW. I am not sure how this works but how I would like it to work, is this:

I want a VB button on the switchboard that will connect access to the mysql server & load the tables in, overwriting any that are currently in the database on the home PC. In other words it is reading table info off the server in real time. At that point I hope any changes made will update the mysql database on the server. Presumably when Access, is closed a local copy will contain copies of the updated tables and will stay that way unless it connects to the server again. This would be good since there is an option to save a backup on the PC when Access is closed. Not a bad idea, in case the sql on the server gets corrupted.

Is there a way to do this or am I all wet? What sort of VB code would I need to get it to connect to the server & work off the mysql tables there?

Paul Beddows

Consulting, Avaya/EAS implementation, Training
Vancouver, Canada
E-mail paul at natcoa.com

 

[jrbarnett]

Paul - this sounds like it is some sort of remote working facility for your employer.

If this is the case, you will need some sort of secure data connectivity (either VPN or running the db through something like Citrix or Windows terminal services to stop the data travelling across the internet in clear text - in which case you can run the database application directly from it, not just the back end. You also don't need then to handle saving new/amended data back to the source database).

From a security perspective, I would have major issues allowing external clients to connect directly to a database server through a firewall, whatever the platform.

Code to handle data copying (if necessary) will depend on what arrangements for security you get in place and if it involves replication technologies of one form or other.

John

 

[avayaman]

OK, Let me ask this, is there a way to put the database Access 2003) itself on the server & have it reference a mysql database on the same server? I can put the database in a password protected directory, not linked to anywhere else.

Paul Beddows

Consulting, Avaya/EAS implementation, Training
Vancouver, Canada
E-mail paul at natcoa.com