KB 90

How do I use MS Access with MySQL?

Article:90
Created:2000-11-13 22:54:52
Categories:
  Windows 95
  Windows 98
  Windows NT 4
  MySQL
  Databases

Question or Symptom

I'd like to use the point and click GUI of MS Access, but want to use MySQL on your Unix servers to store the data.

Resolution

Configuring Access to connect to MySQL

Please get the current MyODBC drivers from http://www.mysql.com/download_myodbc.html site, you can download: MyODBC 2.50.24 for Windows95 (full setup) MyODBC 2.50.24 for NT (full setup) Depending if you want to install it on Windows 95 or NT

After it is install on your clients computer, 1. You go to the control panel and you select the "ODBC Data Sources" 2. Select the "User DNS" tab 3. Click "ADD" 4. Select the MySQL data source 5. Click "Finish" 6. This will open the MySQL Driver default configuration 7. Fill the information by giving a DNS name that you will refer to when

   connection to your MySQL database remotely.

8. Put the host name it should be mysql.swcp.com. 9. The database name SWCP created for you. 10. Enter the "User" account, assigned to you from the MySQL e-mail you recieved. 11. The password from the MySQL email you recieved. 12. Click OK, That will close the configuration Window 13. Click OK again to finish the ODBC Administration setup.

   You can change some options if you want, but for your first setup.
 

Configuration Window information: Windows DSN Name: MySQLdb MySQL host (name or IP): mysql.swcp.com MySQL database name: (Usually the same as your SWCP account name) User: (Also, usually the same as your SWCP account name) Password: (you should have received email with this information) Port (if not 3306): (Leave blank 3306 is the correct port) SQL command on connect: (Leave blank) Options that effect the behavior of MyODBC:

 (Nothing needs to be checked here)
 

That's it for the client ODBC configuration. Now for the setup concerning using

Access to change the MySQL database:

1. Start Access 2. Create an empty database without any information in it, or use a database

   you already have and that you want to have access to you MySQL server from
   with in.

3. While the database is open 4. Go to the File->GetExternalData->Import menu 5. In the Import window, select the "Files of type:" and go down to "ODBC

   Database()"

6. This will open the "Select Data Source" windows. 7. Select the "Machine Data Source" tab 8. Scroll down and you will see the "Data Source Name" you create in the

   above ODBC step"

9. Select your DSN name. 10. If you are connected to your network where you put your MySQL server,

   you will see the "Import Objects" windows open with the list of tables you
   created in the database on your MySQL server.

11. Select the tables you want to work with and repeat the step 4 to 11 for

   each table you want to have access to.
 

From here you will have your local database and your remote tables accessible from inside the same database on your computer. You can do a copy and paste from the local table and the remote table, etc.

Related URLS

 http://www.mysql.com/downloads/api-myodbc.html