How to use access to edit mysql database

[sysadmin]

Since I have some peeps asking me how I am doing stuff I thought I share how I am doing part of it. If you have questions on this or if you try to do this and fail post here and I will help you the best I can.

Now about access and mysql, to source TONS of data there is no better way but to source in .sql files. but sometimes I think it is faster to edit just a few values in mysql by using access, if that is the case for you here is how to use access with mysql.


Download and install myodbc 3.51

You can use your root or local account but I rather make a special account to fix stuff, example:

Go to start->run and type cmd<enter>
type c:\mysql\bin\<enter>
type mysql -u root<enter>

type the next command in mysql command window you just opened:

GRANT ALL PRIVILEGES ON *.* TO access@localhost IDENTIFIED BY 'mypass' WITH GRANT OPTION;

where access is the username and mypass is the password.

if you did not get any errors you did fine, close the boxes now. Open control panel, go to administration tools, open data sources(odbc).

in user dns tab click add, scroll to the bottom of the list and select myodbc 3.51 driver, type the next:

datasource name = myemudb
description = mysql emu database
server = localhost
user = access
pass = mypass
database = select one from drop down

Note: username and password are the ones you added in the previous grant command, also server is the IP of your mysql server, I use localhost on mysql.

Here is where you will know if you did ok, if you click on test connection then it will tell you if you connected succesfully to the database. If it errors out then check what you did over and over until you get it right. when it connects, SAVE IT.

Now on access:
-create a blank database,
-save it with a name you want
-go to file menu->get external data->link tables
-select from type of file ODBC DATABASES() and new window will open.
-select machine data source tab,
-you will see the connection you saved before, select it,
-when you do, that window will close and a new one will open, click on select all tables, and accept.

At this point access will ask you to select a field in a couple tables, select the one with ID, and finalize the linking.

All the tables from the database will be apearing on your access database, and they will be ready for editing.

Note: this username and password will allow you to edit any database on my sql, I use it this way since I have several databases loaded, but if you are concerned about security, then use eq.* where it says *.*, where eq is your database name. example:

GRANT ALL PRIVILEGES ON eq.* TO access@localhost IDENTIFIED BY 'mypass' WITH GRANT OPTION;

Sysadmin.

[GeorgeS]

This works well. Only thing I could not get to work are BLOBS in access


GeorgeS