Microsoft Access

From MythTV Official Wiki
Revision as of 13:29, 27 September 2008 by Davidsegall (talk | contribs)

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search

MythTV occasionally requires changes to its database that are difficult, or even impossible, using the standard MythTV or MythWeb interface. Microsoft Access provides a convenient front end to the database that enables changes to be made using a spreadsheet style view of the contents. It can also be used to produce neatly formatted reports of your music collection or even to examine and change the structure of the MythTV database.

Microsoft Access provides a powerful tool for use with any database that has an ODBC driver. You may prefer to use OpenOffice Base which provides most of the capabilities of Access for databases with a JDBC driver and has the advantage that it is cross-platform and open source.

The following paragraphs describe how to connect to your mythconverg database from a computer on your local network using Microsoft Access.

Enable access to the database from remote computers

The MySQL database is set up by default to refuse access to remote computers. If you have not already done so you will need to change this according to the instructions under "Modifying access to the MySQL database for multiple systems".

Obtain the MySQL ODBC driver

Go to the MySQL connector page and click the Connector/ODBC link then choose a Windows installer or setup link. This will install the ODBC driver and configure the Windows ODBC datasource administrator to enable it.

Set up the data source

From the Windows Start Menu choose Control Panel->Administrative Tools->Data Sources (ODBC)
Click the System DSN tab, press the Add button and select the MySQL ODBC driver. This brings up the configure window; an example is shown below.

Adding an ODBC data source

The default database password is usually mythtv but you should check it on the first page of the Frontend General Setup menu. You will need to change the Server to the name or IP address of your MythTV backend. You do not need to change anything on the Connect Options or Advanced tabs. Press the Test button and, hopefully, you will see the message "Success; connection was made!". If not press the Diagnostics button to provide some assistance. Press OK to continue.

Open mythconverg in Access

Open and name a new database in Access then select File->Get External Data->Link Tables from the menu. Choose ODBC Databases from the file type drop down in the Open File (Link) dialog. Select the Machine Data Source tab in the Select Data Source dialog and choose the Data Source Name you set up in the preceding paragraph. Now press "Select All" in the Link Tables dialog. Check, or leave blank the Save Password box depending on your security concerns. You will be asked to supply a unique key field for some tables. I chose the first field.

Be careful and backup your database

You now have a simple way of altering your database. Be aware that Microsoft Access saves changes to the database without a specific Save command so use it carefully. A careless change may mean that you have to reconstruct the entire database. You can minimize the pain by making regular backups as described in User Manual:Periodic Maintenance.