View source for How To Connect Excel And Access To Rivendell Database

== 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 ([http://en.wikipedia.org/wiki/Open_Database_Connectivity "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: [http://dev.mysql.com/doc/connector-odbc/en/connector-odbc-installation-binary-windows.html Installing Connector/ODBC on Windows].

== Install the MySQL ODBC Connector ==
The MySQL project provides ODBC connectors here: [http://dev.mysql.com/downloads/connector/odbc/ 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 [http://www.mysql.com/products/workbench/ 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 <code>Control Panel / Administrative Tools / Data Sources (ODBC)</code>.

[[File:ODBC directory before.png|400px]]

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


[[File:Add ODBC source.png|400px]]

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


[[File:MySQL data source configuration screen.png|400px]]

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


[[File:ODBC directory after.png|400px]]

* Click OK to dismiss the ''Data Source Administrator'' window.


==Accessing Rivendell Data in Excel==
===Creating a New Data Connection===
[[File:Excel from data connection wizard.png|400px]]

[[File:Data connection wizard 1.png|400px]]

[[File:Data connection wizard 2.png|400px]]

[[File:Data connection wizard 3.png|400px]]

[[File:Data connection wizard 4.png|400px]]

[[File:Excel import data.png]]

[[File:Excel after import.png|400px]]

===Using an Existing Data Connection===
[[File:Excel get existing connection.png|400px]]

[[File:Excel select existing data connection.png|400px]]

==Accessing Rivendell Data in Access==
[[File:Access new database.png|400px]]

[[File:Access external data ODBC database.png|400px]]

[[File:Access select link to data source.png|400px]]

[[File:Access select Rivendell source.png|400px]]

[[File:Access link tables.png|400px]]

[[File:Access after opening cart table.png|400px]]