Microsoft Access 2000 works great as a general front end and IDE for client or server databases. You can use it to manage not just data, but also the table definitions, stored procedures, indexing, and other DBMS-related functions required of a DBA. Follow these steps to access a MySQL database (for the UNIX crowd) over the Web and manage the tables and data with Access.
Connecting to a database
First, keep in mind that Access 2000 is not a database; it is a tool for managing databases. If you run Access, and don’t plan on manipulating or viewing data, you’re probably running the wrong application. When you get to the Create/Open option in Access, examine the Open section. You are presented with a list of recently accessed files (even if you have since deleted some of them).
At the top of that list is the option More Files…. If this option is selected, you’ll be presented with the common dialog box for browsing the file system. This is where you can select either a local database, or a remote one. A variety of formats are available (e.g., dBase, Access, Excel, Paradox, and csv/text) in this dialog box. If you look at the very bottom of the file types list, you’ll see an option for ODBC Databases().Open DataBase Connectivity (ODBC) lets you connect to remote databases.
Connecting to a remote MySQL database
I chose MySQL to connect to a remote database server simply because I have quick-and-easy access to a MySQL server. However, connecting to any other remote database via ODBC and Access 2000 would work very much the same way.
One of the things to consider, when connecting to remote database servers, has to do with the OSI model. You need to look at OSI because when you’re dealing with remote connectivity, you must consider all the routers, gateways, domains, etc. that your connection will pass through; and the security measures implemented by each of them. Almost all firewalls (gateways and routers) allow Web traffic, but not all necessarily allow ODBC traffic.
On the network and data link layers (NIC), you must actually be connected to your service provider. But, on the transport and network layer (protocol), you need to be using the Internet’s TCP/IP. On the session and presentation layers (operating system) user/password authentication becomes more of a factor—especially if you’re dealing with Microsoft databases. Microsoft databases can make use of a proprietary, OS-level authentication and permissions scheme (e.g., Kerberos, NTLM/NTFS).
Also, on the application layer, the database server software authenticates the user and determines what permissions to grant or block. This is the only security scheme I’m concerned with, because the MySQL server is on the Internet, and it is not a Microsoft product. That means that my ISP has already authenticated me for the connection, and I won’t be using NTLM security.
Making the ODBC connection
If you’re going to make an ODBC connection to a non-Microsoft database server, you need the ODBC driver for that particular database software. The driver for MySQL is MyODBC. If you haven’t already, you’ll need to download it, install it, and add the server name and username/password and other properties of the connection to your database server (Figure A).
After you enter the connection properties for your server, Access tries to make the connection. Here is where another one of those OSI considerations comes into play: Firewalls can be set to block ODBC connections from one network segment to the next. For example, at the OS level, my McAfee firewall issues a warning whenever Access tries to connect via ODBC to my remote MySQL server. I also have to go through my DSL’s router/firewall, and it could block the traffic if my network administrator had chosen to block traffic on the ports used to communicate with a MySQL server over MyODBC.
Once you successfully connect to the database server, Access will present you with a list of tables, allowing you to choose which ones you want to use. To minimize the network traffic, you should choose only those tables you will be working with.
If you have numerous tables, each with a lot of records, and you try to link to all of them over a slow connection, each click of the mouse could take ten minutes or more. But by reducing the number of tables to which you are linking, you can cut down on the amount of clicks to the database and save time.
After the tables have been selected, Access displays the database tables much the same as if you were working with a local Access file. The only difference is that this displays little Earth icons that identify entries as links to a remote table (See Figure B).
Managing tables includes creating them, removing them, and changing their structure. Normally, this kind of activity requires an intimate knowledge of SQL. However, with Access’s interface, you can do all of this without knowing an ounce of SQL syntax. In fact, I’ve known folks who bill themselves out as Access programmers, who didn’t know anything about SQL. They’ve even successfully completed entire projects without anyone being the wiser.
The most basic SQL syntax for creating a table is:
CREATE TABLE webusers (id INT PRIMARY KEY, username VARCHAR, userpassword VARCHAR);
The syntax for dropping a table is even easier:
DROP TABLE webusers;
Access’s easier, GUI method of adding a table is to double-click on the Create Table In Design View option (shown at the top of the list in Figure B). You’ll then be presented with Access’s table design utility. Figure C shows how you can use this utility to create the same Webusers table as the one in the SQL example above. Note the little key icon next to the definition for the id field. This shows that that field is the primary key. Also, notice that Access uses the terms Number and Text instead of INT and VARCHAR.
By design, the table is added to your local view of the database. But it isn’t saved to the database server, because the primary purpose of Access (as a database front end) is to manipulate and modify the actual data, with little emphasis on table management.
Deleting and modifying tables
Unfortunately, in Access 2000, deleting a linked table does not delete the table, just the link. Also, there are very few modifications to table structures that you can make via the Microsoft Access GUI. Again, this is made up for in its data entry and viewing features.
For managing data on a remote server, Access is most useful for editing existing data, adding new data, and most importantly, creating views and reports based on that data.
Adding and editing data
When adding and editing data, you have two options. One is to simply double-click on the linked table icon (the Earth icon in Figure B). This opens up the table in a spreadsheet-like format. This can be costly if you have a lot of data, because the entire contents of the table have to be transferred back and forth across the network. The other method is to use Access’s Form utility (I like the wizard) to create good-looking, efficient data editing forms.
Another handy tool in Access 2000 is the Reports utility. You can use this to create reports that reference one or more tables, sort the data to your specifications, and display the results in word-processor style. One downside to this functionality is that the reports are formatted for your particular printer. If (as was the case with me) your printer is offline when you attempt to do this, you won’t be allowed to finish the report.