How to Connect MYSQL to MS ACCESS in NETWORK?

[Mr Key]

Hi all!!
I have MYSQL server running in one machine (say PC1 with IP 192.168.0.45) and I want to connect to MS-ACCESS in another machine (say PC2) on the same network.
How can I go about it? Please help!!!!!!!!!!!

[Oralloy]

http://connectionstrings.com/Mr Key,

Do you know how to set up an ODBC connection? I assume not.

I'm assuming the client is a windoze machine.

First, open the Control Panel.

If running Windows Xp, 7, or 8, open the "Administrative Tools".

Next, open "Data Sources" or "Data Sources (ODBC)".

Then you have to "Add" a data source. Which type "User DSN", or "System DSN" is up to you (and whether you have administrative control on the system).

It will give you a list of available data source types. Select the one for MySQL. If you do not have one for MySQL, you will have to install the appropriate driver first.

Once you've selected the data source type, a connection dialogue will display - populate it and test your connection.

Once you've got the data source set up, close out the control panel. Remember the name; I will use the string"mySQL-DB" in the example, just to keep consistency. Also, for security reasons, I would suggest that you not store username and password as part of the perminant ODBC connection.

In Access, you create a database connection something like this:

 
''--establish the connection string
''  NOTE:
''    <username> is your database login username
''    <password> is your database login password
Dim dbODBC As String
Let dbODBC = "DSN=mySQL-DB;UID=<username>;PWD=<password>;"
 
''--connection object
Dim dbConn As ADODB.Connection
Set dbConn = New ADODB.Connection
 
''--attempt connection
dbConn.Open dbODBC
If dbConn.State = adStateOpen Then
  dbConn.Close
  MsgBox "Yay!  Was able to connect!"
Else
  MsgBox "Boo! Hiss!  Failed to connect!" & Chr(10) & Err.Description
End If
 
 

This page on ODBC Administration might be useful to you.

This page has a great deal of information on constructing MySQL ODBC connection strings.

There are lots of code examples about the 'Net.

Kind Regards,
Oralloy

[Mr Key]

Ok thanks Orollay!!
I know how to connect with ODBC in local machine but my question is on networked computers. Say Computer1 as a server installed with MYSQL and Computer2 as a client machine installed with MS_ACCESS with its applications. I can use MS applications on the same machine with MYSQL using ODBC connection as a link. I can also connect by PHPMYADMIN on network. but I need ODBC in network to simplify applications buildup like forms and several reports.
Please help!!!

[Oralloy]

Mr. Key,

I don't have a MySQL instance to test against, but isn't one of the options in the MySQL ODBC setup the server name or IP address?

Anyway, on Computer2, the client machine, you have to setup the ODBC connection to the MySQL instance on Computer1.

If Computer2 doesn't have it, you will have to install the ODBC driver for MySQL on it. The mySQL site has two pages for connectors/ODBC-drivers:

1. Latest MySQL Version
2. Older MySQL Versions

Hope that helps.
Oralloy

[Mr Key]

Here is the step achieved to accomplish this task.

A:On the MYSQL server machine
1.Add Mysql port 3306 to the exceptions list of windows FIREWALL
2.Give GRANTS to users that supposed to use MYSQL
i. If you are using windows XP, On the command line just go the directory yo have installed MYSQL as follows.
$ C:\Program Files\MySQL\MySQL Server 5.6\bin
then write
$ mysql -u root -p
$ Enter pasword:

mysql> use mysql;
mysql> GRANT ALL ON *.* to databaseuser IDENTIFIED BY 'your-password';
mysql> FLUSH PRIVILEGES;

B: On the Client Machine
1. Install ODBC connector
2. Open Access then go to Externaldata>>ODBC database>>Select datasource then add NEW then select MYSQL ODBC 5.1 DRIVER then OK
3. Write the name of the file you want the data to be stored then click next then finish. The pop_up window will display.
4. On the POP_UP window, TCP/IP server write the IP address of the MYSQL server machine and then fill in the username and password of MYSQL user.
5. Choose appropriate database you want to select from COMBOBOX displayed then click OK>>OK>> then select appropriate tables you want to connect too!! then click finish. You are done