Microsoft Access as a Front-end to MySQL

For years many companies and organizations used Microsoft Access for database management. This DBMS allows efficient retrieval and processing the data. Also, it provides an easy-to-use interface to create tables, define fields, data types and indexes making it accessible to even newbie developers. Finally, MS Access includes unique query builder that allows to design complex queries without extensive SQL knowledge.

Modern it infrastructure applies advanced requirements to database management system that MS Access cannot meet. Many organizations and companies migrate their databases to more powerful DBMS due to the following limitations of MS Access:

• max database size is 2 gigabytes
• total number of database objects 32,768
• number of fields in a table 255
• max length of table/field name is 64 symbols
• number of indexes in a table 32

On the other hand, MS Access combines user-friendly interface, tight integration with Office applications and powerful development environment known as Visual Basic for Applications (VBA). At the same time, the product supports linking to external data sources that makes it ideal Front-end for many advanced DBMS such as MySQL.

To use Microsoft Access as a front-end to MySQL it is required to create linked tables in MS Access database to existing tables in MySQL database. When a query is run against MS Access linked tables, the corresponding ODBC driver will execute the same query on the target MySQL database.

Follow these steps to create linked table in MS Access:

• Open MS Access database that must be linked to MySQL
• Choose ODBC Database' on 'External Data' tab
• Then 'Get External Data' dialog box will appear. Select option 'Link to the data source by creating a linked table' in that dialog window.
• Then 'Select Data Source' dialog box will appear. Select 'Machine Data Source' tab and double-click the ODBC DSN to use for linking tables. Click 'New' to create new DSN for ODBC and follow the instructions from section Create a Connector/ODBC DSN below
• ODBC data source may require to log in. In this case, enter login name and password as well as extra information that may be required.

After completing those steps, MS Access connects to the specified MySQL database and lists the tables to link. Choose individual tables or click 'Select All'. If the source MS Access database already has a table with the same name as the linked one, MS Access appends numeric suffix to the linked table name.

If MS Access displays no tables to select, it might be caused by the fact you did not specify the target database when defining DSN properties. To fix the issue, reconfigure the DSN providing the database to connect to (see Create a Connector/ODBC DSN section for details), or choose a database when you log in to the DSN.

If Microsoft Access is failed to recognize primary key for a table automatically, it will ask you to choose a column (or a combination of columns) to be used as unique identifier for each row from the source table. Now you can use MS Access interfaces and build queries to the linked tables just as you would for any native tables.

Create a Connector/ODBC DSN

Windows provides ODBC Data Source Administrator tool to create DSN and check ODBC driver installation. Here are the steps to create and configure a DSN using this tool:

• Open the ODBC Data Source Administrator
• Select 'System DSN' tab to create a system DSN that is available for all users. Or click 'Add button to create a User DSN that is available for the current user only.
• Select the MySQL ODBC ANSI or Unicode Driver in the appeared 'Create New Data Source' dialog window and click 'Finish' button.
• Now it is required to configure the specific fields of the DSN being created through the 'Connection Parameters' dialog.
• Enter the name of the data source to access in the 'Data Source Name' box. It can be any valid name that does not exist as a data source yet. To identify whether 32- or 64-bit ODBC is used for the DSN, include this information into the DSN identifier.
• Enter some text that could help identify the connection in the 'Description' box.
• Enter MySQL server host name or IP address in the 'Server' field. It is 'localhost' by default.
• Enter user name and password for this connection in the corresponding fields.
• The the list of databases that the user has permissions to access is automatically populated in the 'Database' pop-up window.
• To connect to MySQL server using TCP/IP port other than default 3306, specify custom port number in the corresponding field.
• Click OK to save the DSN

Click 'Test' button to verify the connection using the specified parameters. If the connection succeeds, you will see the 'Connection Successful' dialog. Otherwise, 'Connection Failed' dialog window will appear.