Accessing mySql on web from MS Access at the office...

[sweetleaf]

Hi all,

Do you know if its possible to connect an access front end at my head office to a mysql database sitting on a remote server hosted by an ISP?

Is this a relatively straightforward task?

Regards,
Mark

 

[rycamor]

This might save you some time:

Your ISP needs to have MySQL configured with the MyODBC library, so that Windows can connect through ODBC. If they don't support that, then there is no point to trying anything else.

I'm fairly certain that the standard ISP running MySQL will NOT have MyODBC installed, as it is a bit tricky.

If your ISP has MyODBC installed, then theoretically it is as simple as:

1. Go to Control Panel, Data Sources(ODBC), Create a new System DSN, that points to the remote host, with the correct values (provided by your ISP).

2. Create a blank Access database, then go to File->Get External Data->Import, and choose ODBC as your source. Choose your DSN name your created in step 1 above.

Now, I must stress that I have not actually used MySQL this way myself. (I apologize but I can't stand Access. This is an opinion based on experience.) So I hope others who have actually done this will help out with the glitches and gotchas.

MySQL ODBC documentation:

http://www.mysql.com/doc/O/D/ODBC.html

I must ask one further question, though: what is your purpose? I'm willing to bet there is a better way than to use Access to manage MySQL remotely. Is your ISP running PHP? Take a shot at installing phpMyAdmin (

http://phpmyadmin.sourceforge.net).

It's like having a web-based "SQL Administrator" for MySQL.

Good luck... ;-) -------------------

Current reading --

http://www.wnd.com/news/article.asp?ARTICLE_ID=25456

 

[MajorGeek]

My ISP doesn't allow an ODBC coonection "for security" reasons, but I suspect it's too complicated for them.

 

[sweetleaf]

Thanks Rycamor!

Yup! great instructions - now i just have to verify whether myodbc is installed on their side (linux). yes, i agree..access was not my first choice - but b/c i am a total mysql newbie, i wasnt sure what else to use..

Thanks for the insights - i'll let you know if everything turns out..

cheers

 

[rycamor]

Check out

http://www.dbtools.com.br/

for a nice MySQL administration kit that doesn't require ODBC. (It does, however, require that your ISP configure MySQL to accept remote TCP/IP connections).

I still don't know exactly what your needs are, but if it is just for a remote administration system, then please, do yourself a favor and check out this link and the other one I provided.

If your need is to actually have a data entry application, done through an Access GUI, then this is an appropriate use. But, Access should never be used as a SQL administrator. It is much too limited, and buggy, to be your prime method of creating and administrating your database. Also, Access was not built FOR mysql, so it doesn't help you with any of MySQL's internal functionality. -------------------

Current reading --

http://www.wnd.com/news/article.asp?ARTICLE_ID=25456