In 2017 the CHaMP Workbench was updated to use SQLite as the underlying database instead of Microsoft Access. There are several options available should you want to interact directly with the Workbench database directly (writing custom SQL queries etc):
Although SQLite is natively a command line application with no user interface there are several user-friendly products available that provide rich user interfaces. SQLite Studio is one such free user interface that possesses an excellent database browser and query functionality.
SQLite Studio does not need to be installed. You simply download the software, extract the zip archive onto your system and then run it. It works on both MacOS and Windows.
With a little effort, you can use Microsoft Access as the user interface to view and edit data in a SQLite database. To do this you have to set up an ODBC file source to the SQLite Workbench database and then add linked tablesin an Access database to the SQLite workbench database. Essentially Access becomes the user interface through which you interact with your data, but the actual data itself are stored in SQLite.
The following instructions describe the steps:
1) Download and install the 64 bit version of the SQLite ODBC Driver.
2) Open ODBC. This is a built-in piece of Windows software that allows you to connect to virtually any database format. The easiest way to open ODBC is to press the Windows key and then type “odbc”. Windows comes with both a 63 and 64 version and make sure that you select the 64 bit version of ODBC!
3) Make sure that you are on the “User DSN” tab and then click the
4) Scroll down and choose the
SQLite3 ODBC Driver.
5) Provide a meaningful name for this data source, something like “CHaMP Workbench” and then browse to the workbench SQLite database. All other settings can be left untouched.
6) Click OK to create the ODBC data source. You will return to the list of datasources with the new one selected.
7) Open Microsoft Access.
8) Create a new, blank Access database. You can save it anywhere but it makes sense to place it in the same folder as the SQLite Workbench database.
9) Click the
External Data tab.
10) Click the
ODBC Database button.
11) Click the Option to
Link to the datasource by creating a linked table.
12) In the
Select Data Source popup that appears, switch to the
Machine Data Source tab and select the ODBC data source that you created in the earlier step. Then click
13) A window will appear that shows all the tables in the CHaMP Worbkench SQLite database. Select the ones that you are interested in. Hold the
Shift key to multi-select, or click the
Select All button to link to all the SQLite tables.
14) Click OK.
The end result of all these steps is a very small Access database that links to the relevant tables in the SQLite database. You can query, add, edit, delete data in Access and the changes will actually occur back in the SQLite database. You can also use all of Accesses features to filter records, create new queries etc. Note that the Access database is extremely small. It doesn’t contain any actual data, but simply the links back to the SQLite database. (This is relevant if you want to email or share the Access database between colleagues or computers.)
SQLite Programming with Python
SQLite is natively supported by the Python programming language (documentation). Here’s a simple script to select all CHaMP Visits. Refer to the database schema documentation for a description of the tables within the database.
import sqlite3 conn = sqlite3.connect('C:/CHaMP/workbench.db') curs = conn.cursor() curs.execute('SELECT * FROM CHaMP_Visits') for row in curs.fetchall(): print(row) conn.close()
Here’s a more complex example that joins several tables together to retrieve the Thalweg Depth Coefficient of Variation (MetricID 40):
SELECT WatershedName, SiteName, VisitYear, V.VisitID, DisplayNameShort, MetricValue FROM Metric_Definitions D INNER JOIN Metric_VisitMetrics VM ON VM.MetricID = D.MetricID INNER JOIN Metric_Instances I ON I.InstanceID = VM.InstanceID INNER JOIN CHaMP_Visits V ON I.VisitID = V.VisitID INNER JOIN CHaMP_Sites S ON V.SiteID = S.SiteID INNER JOIN CHaMP_Watersheds W ON S.WatershedID = W.WatershedID WHERE D.MetricID = 40
SQLite Programming with R
The R programming language can also connect to SQLite databases.