Difference between revisions of "Microsoft Access"
Davidsegall (talk | contribs) m |
Davidsegall (talk | contribs) |
||
| (5 intermediate revisions by the same user not shown) | |||
| Line 1: | Line 1: | ||
[[Category:HOWTO]] | [[Category:HOWTO]] | ||
| − | MythTV occasionally requires changes to | + | MythTV occasionally requires changes to its database that are difficult, or even impossible, using the standard MythTV or MythWeb interface. |
[http://office.microsoft.com/en-us/access/ Microsoft Access] provides a convenient front end to the database that enables changes to be made using a spreadsheet style view | [http://office.microsoft.com/en-us/access/ 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 | 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 [http://en.wikipedia.org/wiki/Open_Database_Connectivity ODBC driver]. | + | structure of the MythTV database. |
| + | |||
| + | Microsoft Access provides a powerful tool for use with any database that has an [http://en.wikipedia.org/wiki/Open_Database_Connectivity ODBC driver]. You may prefer to use [http://www.openoffice.org/product/base.html OpenOffice Base] which provides most of the capabilities of Access for databases with a [http://java.sun.com/products/jdbc/overview.html 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 | ||
| + | [http://www.mythtv.org/docs/mythtv-HOWTO-6.html#ss6.2 "Modifying access to the MySQL database for multiple systems"]. | ||
====Obtain the MySQL ODBC driver==== | ====Obtain the MySQL ODBC driver==== | ||
Go to the [http://dev.mysql.com/downloads/connector/ MySQL connector page] and click the Connector/ODBC link then choose a Windows installer or setup link. This will install the ODBC | Go to the [http://dev.mysql.com/downloads/connector/ 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. | + | driver and configure the Windows ODBC datasource administrator to enable it. |
====Set up the data source==== | ====Set up the data source==== | ||
| − | From the Start Menu choose Control Panel->Administrative Tools->Data Sources (ODBC)<br> | + | From the Windows Start Menu choose Control Panel->Administrative Tools->Data Sources (ODBC)<br> |
| − | Click the System DSN tab, press the Add button and select the MySQL ODBC driver. This brings up the configure window | + | 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. |
[[Image:Odbc.GIF|Adding an ODBC data source]] | [[Image:Odbc.GIF|Adding an ODBC data source]] | ||
| − | The default database password is mythtv. You will need to change the Server to the name or IP address of your MythTV backend. You do not need to change anything | + | 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 | 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. | 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. | ||
| Line 25: | Line 35: | ||
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 | 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 | + | 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 | 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. | security concerns. You will be asked to supply a unique key field for some tables. I chose the first field. | ||
| Line 31: | Line 41: | ||
====Be careful and backup your database==== | ====Be careful and backup your database==== | ||
| − | You now have a simple way of altering 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]]. | You can minimize the pain by making regular backups as described in [[User Manual:Periodic Maintenance]]. | ||
Latest revision as of 13:29, 27 September 2008
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.
Contents
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.
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.
