Personal tools

How To Connect Excel And Access To Rivendell Database

From Rivendell Wiki

Jump to: navigation, search

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:

  1. Install Microsoft's Visual C++ 2010 Redistributable Package
  2. Install the MySQL ODBC connector
  3. Define a data source in Windows for the Rivendell database
  4. 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).

ODBC directory before.png

  • Click the Add button to open the Create New Data Source window.


Add ODBC source.png

  • Select the MySQL ODBC Unicode Driver, then click the Finish button to open the MySQL Data Source Configuration window.


MySQL data source configuration screen.png

  • 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.


ODBC directory after.png

  • Click OK to dismiss the Data Source Administrator window.


Accessing Rivendell Data in Excel

Creating a New Data Connection

Excel from data connection wizard.png

Data connection wizard 1.png

Data connection wizard 2.png

Data connection wizard 3.png

Data connection wizard 4.png

Excel import data.png

Excel after import.png

Using an Existing Data Connection

Excel get existing connection.png

Excel select existing data connection.png

Accessing Rivendell Data in Access

Access new database.png

Access external data ODBC database.png

Access select link to data source.png

Access select Rivendell source.png

Access link tables.png

Access after opening cart table.png