Learn how to connect Microsoft application such as Excel and other to MySQL and PostgreSQL database server running on Linux. This is an excellent information / howto.
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.
Connecting Office Applications to MySQL and PostgreSQL via ODBC
{ 2 comments… read them below or add one }
Chris,
How can you deal with the fact that ODBC connector for MYSQL stores the password and login in plain text within the Windows Registry? Is there some other means to specify the password during the actual connection, within code, as opposed to preconfiguring it within the Registry?
Seems like a pretty huge security hole, since most people can run regedit and easily determine the login credentials to the database.
Best,
-Karl
Karl,
Under Linux / UNIX you store mysql database connection password and username in php / perl file in clear text format. Same applies to Windows too. I know it is bad design. However you can block access to file using unix file level security . Same way regedit can be blocked and only administrator can run the command.