In cases where business applications have been built on open source databases, it may be necessary to connect other Windows applications, such as Microsoft Access or Excel, to these databases for reporting or business intelligence purposes.

One potential application of this process is to use Excel as a front-end for data analysis. Data can be pulled from views or tables and then further analyzed, graphed, and the like. Even pivot tables can be used to create even more powerful reporting solutions.

This how-to walks through this process using Excel as an example application. Although in this example, the MySQL and PostgreSQL servers are running on Linux, the steps are no different if the software is running on Windows. These steps are:
1. Setting up authentication
2. Installing the ODBC Drivers
3. Configuring the data source
4. Importing the data.

Server Configuration: Authentication Requirements
Relational database management systems often store sensitive information and so require some sort of authentication and authorization in order allow data retrieval. Before one can connect, one must make sure that the user account to be used exists and has appropriate permissions. Those who do not run their own servers can skip this section and expect their IT staff to be able to handle the appropriate user rights issues, but these steps are included for those who may not have that luxury.

Download