MySQL and ODBC - The Sample Project
( Page 2 of 6 )

Suppose our client was the Wichita Widget Company. They are interested in making client information available over the Internet for the perusal of sales representatives located across the nation. This information will be maintained by the sales department at the central Kansas office. Rather than use a custom Web interface, they would like MS Access to be used to create a friendly, Windows-like application which can be used to maintain this data.

The first step in this process is the creation of the MySQL database and table(s) which will be used to store the data. The mysqladmin interface was used to create a new database named ‘widget’ (Privileged access is required to use mysqladmin):

%>mysqladmin create widget

Next, a new user and appropriate privileges were assigned to that user for the database ‘widget’:

%>mysql -u root -p mysql
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 17 to server version: 3.22.34-shareware-debug

Type 'help' for help.

mysql>GRANT select, insert, update, delete on widget.* 
    ->to admin@123.456.789.0 IDENTIFIED BY ´secret´;

This creates not only the new user ´admin´ capable of connecting from IP ‘123.456.789.0’ with the password ´secret’, but it also grants that user selection, insertion, update and deletion privileges on the database ´widget´.

Note: For more information about MySQL and the privilege tables, check out the article MySQL Administration, published here on Devshed. Although direct table manipulation has since been deprecated by the GRANT/REVOKE syntax, this article will provide you with valuable information regarding the purpose of these tables.

Finally, log in as the user ´admin´ from the host IP ´123.456.789.0´, and create the table used to store client the information:

mysql> create table clients (
    -> clientId mediumint not null auto_increment,
    -> name char(30) not null,
    -> state char(2) not null,
    -> telephone char(10) not null,
    -> email char(55) not null,
    -> primary key(clientID) );

All necessary preparatory tasks involving MySQL are now complete. The next step is to update the available ODBC Data Sources with the new database information. Information regarding how this is accomplished is the subject of the next section.

{mospagebreak title=Updating the ODBC Data Sources} The client machine must be able to communicate with the MySQL database before it can be incorporated into an Access project. This is accomplished by adding it to the ODBC Data Source Administrator (ODBC DSA). The ODBC DSA can be accessed by clicking on the ODBC Data Sources (32bit) icon located in the "Control Panel" folder. This icon is shown in Figure 1-1.

Figure 1-1: The ODBC Data Sources (32bit) icon located in the Control Panel folder.



Clicking on the icon will result in the appearance of a window very similar to the one shown in Figure 1-2. Click on the System DSN tab found at the top of the window, and then click on the Add... button to add a new data source.

Figure 1-2: The ODBC Data Sources window



Clicking on the Add... button will produce a Create New Data Source prompt. The user is requested to select a driver for which a new data source should be set up. Scroll down and highlight the one that says MySQL. Click Finish. This in turn will produce a new window, which contains a series of textfields relevant to this new datasource. Although all textfields are in some way relevant to the datasource, there are a few which are particularly important:

Windows DSN Name
The Data Source Name (DSN) is the name used to represent the data source that is to be made available. I typically assign this the same name as the database, or choose a name that unmistakably refers to that database.

MySQL Host (name or IP)
This is the hostname assigned to connect to the MySQL database specified in the field "MySQL database name" (see below). Of course, this hostname should be the same as the host from which this client will connect, or the connection will fail.

MySQL database name This is the name of the database made available via ODBC. In the case of the database used in this tutorial, the name would be "widget".

User
This is the user name assigned to connect to the MySQL database specified in the field "MySQL database name".

Password
This is the password assigned to connect to the MySQL database specified in the field MySQL database name.

Fill each of these in which the correct information as it applies to the parameters you specified when setting up the widgets database. Click the OK button, and you will see that the Widget datasource has been added to the datasource list. It is now ready to be used by Access, or any other MyODBC-capable application.

In the next section, instruction regarding how to import the newly ODBC-accessible MySQL database into Access. Also, preliminary information regarding making concurrent updates to the MySQL database through an Access GUI is provided.

>>> More MySQL Articles          >>> More By W.J. Gilmore