Connecting to MYSQL via ODBC

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.

OSI considerations
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).
More info here.