|
|
Hack 68. Use Access as a Front End to MySQL
MySQL is a widely used open source database program that often runs on Linux web servers, and Access makes a great front end for data entry and reporting. MySQL is a wildly successful open source SQL database that runs on most Linux-based web servers. It's the perfect database to store information for use in database-driven web sites because you can use the PHP programming language to read the data from MySQL and display it on web pages. In fact, the combination of Linux, Apache (an open source web server that runs on Linux), MySQL, and PHP is so popular that it's known by its initials: LAMP. However, MySQL doesn't hold a candle to Access when it comes to forms and reports. MySQL has no built-in form editor or report writer. Instead, you type commands at a command line or write programs (usually as part of PHP-based web pages) to enter, edit, and display information. But who wants to create programs that display data entry forms for each table? This is where Access comes in. Access can add a friendly face to your MySQL database. In the same way an Access database can link to tables in another Access database, you can link to tables in a MySQL database on your web server over a LAN or the Internet. Once the tables are linked, you (or your users) can use Access forms to enter or edit the MySQL data and Access reports to display it. 7.11.1. Installing the MySQL ToolsMySQL does have a Windows-based utility you can use for creating and editing the structure of the tables in your MySQL databases. The older version of the program is called MySQL Control Center, and it has been replaced by MySQL Query Browser. You can download either program from the MySQL web site (http://dev.mysql.com/downloads/) for free. The manual for MySQL Query Browser is available online at http://dev.mysql.com/doc/ query browser/en/, or you can download it as a PDF or Windows Help file. When you start MySQL Query Browser, you specify the server on which MySQL runs, your MySQL username, and your password. Once connected, you see a list of the tables for which you have access permission, and you can view or edit the data or structure of the tables, as shown in Figure 7-44. Figure 7-44. MySQL Query Browser
MySQL Query Browser is useful, but it's not the tool to give to your database users. For example, you can't create forms with data validation, combo boxes, or subforms, and you can't create formatted reports. For this, you need Access. For Access to connect to a MySQL database, you need to install the MySQL Connector/ODBC driver (also called the MySQL ODBC or MyODBC driver). This driver lets Access communicate with MySQL via Open DataBase Connectivity (ODBC). You can download the MySQL ODBC driver from (http://dev.mysql.com/downloads/) for free. After you install it, a new option appears when you link to external tables from an Access database. You don't need to install MySQL Query Browser on every computer on which your Access database will run; you need it only if you plan to use it to look at or change the structure of the tables in your MySQL database. But every computer on which your Access database runs needs the MySQL ODBC driver installed because Access uses the driver every time you open a linked MySQL table. If you plan to connect to a MySQL database over the Internet, your requests will probably need to pass through one or more firewalls. The MySQL ODBC driver communicates over port 3306, so this port must be open on all the firewalls between your computer and the MySQL server. You can specify a different port when you create the link from Access to MySQL, in case your MySQL server is configured to use a nonstandard port number. 7.11.2. Linking to MySQL TablesOnce you've got the MySQL ODBC driver installed, linking Access to MySQL tables requires two steps: making sure the tables contain the right fields and making the link. For the Access/MySQL link to work right when editing data into the tables, each table to which you link needs to have the following two fields (the names of the fields don't matter):
Most tables have these two fields anyway; good database design suggests using an AutoNumber field as the primary key for most tables. However, if your MySQL tables don't have these fields, you need to use MySQL Query Browser or some other tool to add them. Creating the links in Access is a snap. Choose File If you are linking to a MySQL database for the first time, click the New button in the Select Data Source dialog box, choose MySQL ODBC Driver from the driver list (it's near the end), click Next, specify a name for the DSN file you are creating to store the connection information, and click Finish. You'll see the MySQL ODBC Driver DSN Configuration dialog box, shown in Figure 7-45. Figure 7-45. Specifying connection information for a MySQL database
Fill in the hostname of the MySQL server, the name of the database that contains the tables to which you want to link, and your MySQL username and password. If your MySQL server doesn't communicate over port 3306 (the default), enter the port number, too. If you want to make sure your connection information is correct, click Test Data Source, and Access will try to connect to the MySQL database and tell you whether it succeeded. When you click OK, Access displays the Link Tables dialog box (the same dialog box you use when linking to tables in other Access databases). However, in addition to the list of tables, you can select the Save Password checkbox. This option is misnamed because Access stores the MySQL password no matter what; this checkbox actually controls whether it stores the MySQL username. If you don't select this option, you have to enter the MySQL username each time your Access database makes its initial connection to the MySQL database.
Linked tables from MySQL databases appear on the Tables list in the Access Database window with a blue-green globe icon rather than the usual box icon. You can't change the structure of linked tables, and you can't create relationships that enforce referential integrity between tables, but otherwise, you can use the data just as if it were in your Access database. If you change the structure of a table in the MySQL table, be sure to relink it by choosing Tools 7.11.3. Hacking the HackWhen you specify the information about a MySQL database, Access creates a DSN file and stores it in the C:\Program Files\Common Files\ODBC\Data Sources folder (assuming Windows is installed on your C: drive). Strangely, Access also stores the information in the database (MDB) file, so it doesn't read this DSN file again after it creates it. If you set up an Access database with MySQL links and then take the database to another machine, all you need is the MySQL ODBC driver installed. You don't need to bring along the DSN file, too. Access stores the MySQL connection information as a connection string that looks like this (the line breaks after each semicolon are included for readability only):
ODBC;
DRIVER={MySQL ODBC 3.51 Driver};
DESC=;
DATABASE=financial;
SERVER=data.gurus.com;
UID=odbc-margy;
PASSWORD=ziasti;
PORT=;
OPTION=;
STMT=;
TABLE=Categories
To see the connection string for a linked table, select the table in the Database window, click Design, click Yes when Access points out that the table structure will be read-only, right-click anywhere in the Design window, and choose Properties from the menu that appears. As you can see, both the username and the password (if you have chosen to save the password) appear in plain textso much for security. You can't edit the connection string because the table structure can't be edited. If you open a DSN file with Notepad or another text editor, you see the same connection string, but without the semicolons. You can edit the DSN file, but it won't affect existing linked tables; it affects only tables that you link using the DSN file in the future. 7.11.4. See Also
Margaret Levine Young |
|
|
Get External Data 