469,898 Members | 1,707 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,898 developers. It's quick & easy.

How to Connect MYSQL to MS ACCESS in NETWORK?

132 100+
Hi all!!
I have MYSQL server running in one machine (say PC1 with IP 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
4 4425
983 Expert 512MB
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
  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>;"
  9. ''--connection object
  10. Dim dbConn As ADODB.Connection
  11. Set dbConn = New ADODB.Connection
  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

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,
Jun 1 '13 #2
Mr Key
132 100+
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
983 Expert 512MB
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.
Jun 2 '13 #4
Mr Key
132 100+
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';

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.

Similar topics

2 posts views Thread by Beda Christoph Hammerschmidt | last post: by
2 posts views Thread by CM Manager via DotNetMonster.com | last post: by
3 posts views Thread by Kevin Killion | last post: by
1 post views Thread by Waqarahmed | last post: by
reply views Thread by Salome Sato | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.