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
NameThe 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".
UserThis is the user name assigned to connect to the
MySQL database specified in the field "MySQL database
name".
PasswordThis 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.