How To Connect Excel And Access To Rivendell Database
From Rivendell Wiki
Contents
Overview
Rivendell provides a small number of pre-built reports. This page describes how to connect the Rivendell database to Microsoft's Excel and Access products, so you can generate your own reports.
Rivendell uses MySQL for its database. Microsoft products don't speak MySQL directly, but they do understand ODBC ("Open Database Connectivity"). ODBC is a standardized method of working with various types of databases, including MySQL. We'll use an ODBC "connector" to talk to the Rivendell database.
These are the steps you'll need to take:
- Install Microsoft's Visual C++ 2010 Redistributable Package
- Install the MySQL ODBC connector
- Define a data source in Windows for the Rivendell database
- Connect Excel or Access to the Rivendell data sousrce
Note: These instructions are for Windows 7 Professional. Other version are probably similar.
Install Visual C++ 2010 Redistributable Package
The current MySQL ODBC Connector requires Microsoft's Visual C++ 2010 Redistributable Package. Install both 32- and 64-bit versions. See this MySQL page: Installing Connector/ODBC on Windows.
Install the MySQL ODBC Connector
The MySQL project provides ODBC connectors here: MySQL Download Connector ODBC. Note that there are two connectors for the Windows platform, 32-bit and 64-bit. Install them both: I've found that even though Win7 is a 64-bit operating system, something along the way requires a 32-bit connection.
As an alternative, you can install the MySQL Workbench on your Windows system, which includes the connectors and several other useful features.
Define a Rivendell Data Source
Once the connector is installed you'll need to define a "data source name" (DSN) for Rivendell. DSNs are what you use to actually work with the database. They provide a simple name that hides all the connection details. Take these steps:
- Click the Windows Start button and enter odbc in the search field. One of the results will be Set up data sources (ODBC). Click that item to open the ODBC Data Source Administrator. You can also locate the program via
Control Panel / Administrative Tools / Data Sources (ODBC).
- Click the Add button to open the Create New Data Source window.
- Select the MySQL ODBC Unicode Driver, then click the Finish button to open the MySQL Data Source Configuration window.
- Enter a Data Source Name (Rivendell) and an optional Description (Rivendell MySQL datatabase) for the new source. The name will be shown in the ODBC Data Source Administrator screen, and can be anything that is meaningful to you.
- Select the TCP/IP Server option, then enter the name or IP address of your Rivendell MySQL server (SilverSurfer). This is the Hostname value in the [mySQL] section of your Rivendell rd.conf file. The default port is 3306; change this only if your server requires a different value.
- Enter the User and Password required to access the Rivendell MySQL server. These are the Loginname and Password values in the [mySQL] section of rd.conf; the default values are rduser and letmein.
- Enter rivendell in the Database field. This ensures that only this specific database can be accessed via this data source.
- Nothing in Details (advanced settings) needs to be changed.
- Press the Test button to verify that you can connect to the Rivendell MySQL server using this configuration. You should see a pop-up window reporting Connection Successful. Dismiss the pop-up, then click OK to save the new data source configuration and return to the Data Source Administrator. The new Rivendell source will be shown in the list.
- Click OK to dismiss the Data Source Administrator window.
Accessing Rivendell Data in Excel
Creating a New Data Connection
Using an Existing Data Connection
Accessing Rivendell Data in Access
