Powering Databases with MySQL

Like any multiuser relational database management system (DBMS), MySQL relies on user accounts to specify access to databases, tables, and columns. Before you create accounts, you need to decide from which computers a user will connect. For example, if you're creating an account that has administrative control over a MySQL database and you want administrators to access that account only from the MySQL server console, you must create the account with only local machine access. To allow access over a network, you must specify one account for each remote client machine address, where the address is either the client's name or TCP/IP address. Alternatively, when you specify an account, you can simply use the percent sign (%) in the Host portion of the account. The % character acts as a wildcard and allows any remote machine to access the database. For example, say that Alice needs access only from server1 and server2 and Bob needs access from anywhere. Table 1 shows the accounts you'd create to provide the required access.

For the NetworkData database, you need to allow the user access from anywhere, so you'll create two accounts: networkdata_user@localhost and networkdata_user@%. To create the accounts, right-click User Administration in the MySQLCC window and choose New User. Enter networkdata_user in the Username field, localhost in the Host field, and a password, as Figure 2 shows. To specify that networkdata_user has access to the NetworkData database, select the check box next to networkdata in the Allow access to pane. Finally, click Add to create the user, then click Close. Repeat these steps to create another account with the same username, but specify % in the Host field.

Connecting to MySQL from Windows Applications
The next step is to access the NetworkData database from a Windows application, namely Microsoft Access. Being able to use multiple methods to connect to MySQL is convenient when you're building interfaces for use by people (a situation in which Access or even Visual Basic—VB—shines) or shell scripts (for which the MySQL command-line tools play an important role).

From a client machine, download Connector/ODBC (formerly known as MyODBC), the open-source ODBC driver for MySQL (http://www.mysql.com/downloads/api-myodbc.html). Click the link to the current production release, page down to the Windows downloads section, and download Driver Installer. (I used Connector/ODBC Driver Installer 3.51.06 for this article.) Next, run the installation program and follow the instructions.

Then, configure a Data Source Name (DSN). A DSN is a way to abstract ODBC database connections from the applications that use them. For example, in Access you might use the Database Connection DSN to connect to a SQL Server database. Later, you might replace SQL Server with MySQL, which would require you to reconfigure the Database Connection DSN. If you retain the name Database Connection for the DSN, however, you won't need to reconfigure Access as well.

To configure a DSN, go to Administrative Tools and open the Data Sources (ODBC) tool. Choose either User DSN or System DSN. (System DSNs are systemwide, whereas User DSNs can be used only by the logged-in user who created the DSN.) Click Add, MySQL ODBC 3.51 Driver, Finish. The resulting MySQL Connector/ODBC configuration screen contains several fields that you need to complete. For Data Source Name, enter a descriptive name for the DSN, such as NetworkData. For Host/Server Name (or IP), enter the MySQL server's host name or TCP/IP address. Type NetworkData in the Database Name field, and enter networkdata_user and the appropriate password in the User and Password fields, respectively. Before you continue, click Test Data Source to ensure that the connection is working.

Now you can access your NetworkData database tables in Access 2000. To do so, follow these steps:

1. Open Access.
2. Create a new, blank database.
3. In the Tables object, right-click in the Tables window and choose Link Tables.
4. For Files of type, choose ODBC Databases.
5. In the Select Data Source window that appears, choose Machine Data Source to specify a DSN.
6. In the Machine Data Source window, choose the NetworkData DSN.
7. When Access presents the Link Tables window, choose ComputerApps.
8. In the Select Unique Record Identifier dialog box, press and hold Shift while you select the Computer and App fields.

You can now use Access to edit the information in the ComputerApps table by building an Access form, as Figure 3 shows, or by double-clicking the ComputerApps icon in the Tables window. Tools such as Business Objects' Crystal Reports or Access's built-in reporting functionality let you build reports based on the NetworkData database as you populate its tables.

   Previous  1  [2]  3  Next