By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,972 Members | 883 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,972 IT Pros & Developers. It's quick & easy.

How to Connect MYSQL to MS ACCESS in NETWORK?

100+
P: 131
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!!!!!!!!!!!
Jun 1 '13 #1
Share this Question
Share on Google+
4 Replies


Oralloy
Expert 100+
P: 983
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:
Expand|Select|Wrap|Line Numbers
  1.  
  2. ''--establish the connection string
  3. ''  NOTE:
  4. ''    <username> is your database login username
  5. ''    <password> is your database login password
  6. Dim dbODBC As String
  7. Let dbODBC = "DSN=mySQL-DB;UID=<username>;PWD=<password>;"
  8.  
  9. ''--connection object
  10. Dim dbConn As ADODB.Connection
  11. Set dbConn = New ADODB.Connection
  12.  
  13. ''--attempt connection
  14. dbConn.Open dbODBC
  15. If dbConn.State = adStateOpen Then
  16.   dbConn.Close
  17.   MsgBox "Yay!  Was able to connect!"
  18. Else
  19.   MsgBox "Boo! Hiss!  Failed to connect!" & Chr(10) & Err.Description
  20. End If
  21.  
  22.  

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
Jun 1 '13 #2

100+
P: 131
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!!!
Jun 2 '13 #3

Oralloy
Expert 100+
P: 983
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
Jun 2 '13 #4

100+
P: 131
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
Jun 3 '13 #5

Post your reply

Sign in to post your reply or Sign up for a free account.