DG ENVIRONMENT
ARTICLE 8 WORKBOOK
WRc Ref:
21 JAN 2007
ARTICLE 8 ACCESS TOOL WORKBOOK
Report No.:
21 Jan 2007
Authors: Y Gordon-Walker
Contract Manager: A Gendebien
Contract No.:
RESTRICTION: This report has the following limited distribution:
Any enquiries relating to this report should be referred to the authors at the following address:
WRc Swindon, Frankland Road, Blagrove, Swindon, Wiltshire, SN5 8YF.
Telephone: + 44 (0) 1793 865000Fax: + 44 (0) 1793 865001
Website:
The contents of this document are subject to copyright and all rights are reserved. No part of this document may be reproduced, stored in a retrieval system or transmitted, in any form or by any means electronic, mechanical, photocopying, recording or otherwise, without the prior written consent of the copyright owner.
This document has been produced by WRc plc.
DG Environment
CONTENTS
1.Introduction
1.1Background
1.2Access Tool Overview
1.3Access Tool Structure
1.4Notation
1.5Key steps in creating the XML files
1.6Related Documents
2.INStallation and set-up of the access tool
2.1Installation and set-up guidance notes
2.2New Users
2.3Existing Users
2.4Opening the database
2.5Start-up form
2.6Importing from Article 5
2.7Edit Set-Up information
3.data ENTRY
3.1Saving Changes on the Forms
3.2Mandatory data
3.3Text Fields
3.4Numeric fields
3.5Drop-down lists(Code Lists)
4.Filling in the forms
4.1Completing Article 8 form entry
4.2Defining Surface Water Quality Elements and Groundwater Parameters
4.3Defining surface water programmes
4.4Defining Surface Water Investigate Programmes
4.5Defining Groundwater Monitoring Programmes
4.6Defining Parameters for Groundwater Monitoring Programmes
5.Validation
5.1Validation
5.2Validate All Option
5.3Advise on the use of the validation key
5.4Monitoring Station ‘Orphan’ Records
6.DIRecT TABLE DATA ENTRY
6.1The extent of the direct entry data entry tool
6.2Importing data directly into the tables
6.3Accessing Surface Water Monitoring data
6.4Associated Surface Water Monitoring Programme data
6.5Associated Surface Water Quality Element data
6.6Accessing Ground Water Monitoring data
6.7Associated Ground Water Monitoring Programme data
6.8Editing the direct-entry tables (surface water and ground water)
7.creating the xml files
7.1Create XML File
7.2Schema Validation
7.3Stylesheet Conversion
8.Minimum schema requirements
8.1Monitoring.xsd
8.2SurfaceWaterMonitoringStations.xsd
8.3GroundWaterMonitoringStations.xsd
9.Hints and tips
9.1Validate as you enter data
9.2Use the Design Considerations field
9.3Locations of the Monitoring Station
9.4Manually editing XML files with Notepad
9.5Links to your own internet-based systems
10.Quality element and parameter identifiers
1.Introduction
1.1Background
The Article 8 Access Tool enables the data required to comply with the agreed Article 8 schema to be collated, validated and converted to XML for submission to the Commission.
The schemas were developed in conjunction with the schema review group (previously the GIS Activity 1 Group) which comprises representatives from a number of Member States and were based on the requirements defined in the reporting sheets Monitoring-reporting Guidance- V5.0.doc. The schemas had a number of reviews before the creation of the Access Tool could be started.
The tool was then built around the agreed schemas and made available for testing by the Member States. Following testing, a number of minor schema changes were made. Note: changes to the schema affect both the front and back end of the tool and the XML generation routines.
The Access tool was originally developed for Article 5 data, and has subsequently been extended to Article 8 data. The Article 5 functionality has been retained, and will be familiar to users of the original tool. The Article 8 functionality is a separate component of the tool.
1.2Access Tool Overview
The tool provides the user with 3 key items of functionality as shown in the diagram below.
Firstly, the tool provides an interface for data entry:
- forms based data entry for collation and validation of programme summary data
- direct-entry tables for collation of monitoring station data
Secondly, following validation, XML files can be generated that comply with the Article 8 schemas.
Thirdly, XSL stylesheets can be used to convert the XML files to more readable HTML reports.
1.3Access Tool Structure
The tool comprises a front-end forms interface linked to back-end databases.
The tool is split into three files:
- WFD Reporting 2006.mdb. This is the front-end of the database. It contains all the functionality: forms, queries, reports, modules and system tables which are integral with the functionality. No data are stored in this file.
- WFD Reporting 2005_be.mdb. This is the Article 5 back-end of the database. It contains only tables. All Article 5 data are stored in this file.
- Article 8 Reporting 2006_be.mdb. This is the Article 8 back-end of the database. It contains only tables. All Article 8 data are stored in this file.
The WFD Reporting 2005_be.mdb is the same back-end as used with the original front-end, WFD Reporting 2005.mdb. Users can continue to use WFD Reporting 2005.mdb with WFD Reporting 2005_be.mdb if they wish, but all the Article 5 functionality is retained in the new front-end, Article 8 Reporting 2006.mdb.Each component has its own set of data tables, stored in separate database files - there is no common data storage. This includes RBDs.
The tool has been developed in this way to allow for updates for future reporting requirements. Splitting the data storage allows existing data to be viewed and maintained and allows new databases to be added. Having a separate forms interface enables amendments to the code to be carried out without affecting the underlying data.
The underlying structure that supports the tool is shown in the diagram below.
Full installation instructions are given in the documents: ‘Access Tool User Guide.doc’ and ‘Access Tool Getting Started.doc’. These are repeated in Section 2 of this document for completeness.
Note: If you used the tool for Article 5 reporting, do not install the blank ’WFD Reporting 2005_be.mdb’ database as this will overwrite your existing data. Instead follow the instructions to ensure that the existing data is maintained.
1.4Notation
In the text of this document:
- Database files are identified by bold font, for example WFD Reporting 2006.mdb.
- Forms are identified by their caption, in italic text, for example Select River Basin District.
- Command buttons on forms are identified thus: <Close>.
- Other controls on forms are identified by their label, in single quotes, for example ‘Run Stylesheet’.
- Tables are identified by Times New Roman font, for example tblCommonLists.
- Keyboard keys are identified thus: <Esc>.
1.5Key steps in creating the XML files
- Install the tool
- Link databases
- Edit the setup information
- Set country code
- Set paths to schemas, stylesheets etc
- Import the River Basin Districts codes from Article 5 or define new codes.
- Enter data for the monitoring programmes using the forms
- Upload data for monitoring stations directly to the appropriate tables
- Run validation
- Create XML files
- Create HTML reports if required.
1.6Related Documents
There are a number of related documents that should be referred to while using this workbook. These are:
- Access Tool User Guide 7th ed.doc
- Access Tool Getting Started.doc
- Access Article 8 Summary of forms.doc
- Access Article 8 Tables.xls
- Monitoring-reporting Guidance- V5.0.doc
- Article 8 Schema Guide.doc
- Monitoring.xsd
- SurfaceWaterMonitoringStations.xsd
- GroundWaterMonitoringStations.xsd
- WFDCommon.xsd
- Changes to Article8 Reporting 2006_be.mdb.doc
- Changes to v37.doc
All of these documents are available for download from the following location:
ftp Location:ftp.wrcplc.co.uk
username:wfdsupport
password:wise
and also from CIRCA library at
2.INStallation and set-up of the access tool
2.1Installation and set-up guidance notes
- The Access database tool can be installed on any number of PCs. You do not require a WISE username and password in order to use this database.
- The database requires Microsoft Access 2000 (or 2003) to be loaded on the host PC.
- The Microsoft MSXML is required in order to generate the XML files and to run the XSL stylesheet. This should be installed automatically with Access 2000 but can be downloaded from the following link if necessary.
- If you have used the original Article 5 tool, you can continue to use the original back-end file WFD Reporting 2005_be.mdb with the new version of the tool. Make a back-up copy of your existing WFD Reporting 2005_be.mdb before installing the new tool.
2.2New Users
Those who have not used the original version of the tool should install these three files in the same folder.
- WFD Reporting 2006.mdb. This is the front-end of the database. It contains all the functionality: forms, queries, reports, modules and system tables which are integral with the functionality. No data are stored in this file.
- WFD Reporting 2005_be.mdb. This is the Article 5 back-end of the database. It contains only tables. All Article 5 data are stored in this file.
- Article 8 Reporting 2006_be.mdb. This is the Article 8 back-end of the database. It contains only tables. All Article 8 data are stored in this file.
2.3Existing Users
Users of the original toolshould installWFD Reporting 2006.mdb and Article 8 Reporting 2006_be.mdbin a folder, and then move a copy of their existingWFD Reporting 2005_be.mdb into this folder. Existing users who do not wish to use the Article 5 functionality of the new tool can, if they prefer, install an empty copy of WFD Reporting 2005_be.mdb in the folder.
2.4Upgrading to v37
If you have been using the Article 8 tool and have populated the Article 8 backend database, you should be aware that a few minor changes to the table definitions have been made. These should be made manually to your backend database to ensure that the tool works correctly. The changes are described in the document ‘Changes to Article8 Reporting 2006_be.mdb.doc’ and are summarised here:
tblGWMonitoringStations
- For Field Name Monitoring, change Field Size from 1 to 2
- For Field Name Ind_supply, change Field Size from 1 to 2
- For Field Name Irrigation, change Field Size from 1 to 2
- For Field Name Other_supply, change Field Size from 1 to 2
tblSWProgParameter
- For Field Name PARAMETER_CD, change Field Size from 7 to 250, and Description to Name or code of the parameter
tblSWMonitoringStations
- For Field Name Intercal_site, change Field Size from 1 to 2
- For Field Name Reference_site, change Field Size from 1 to 2
tblGWParameters
- Set fields PARAMETER_CD and GWP_CD as Key fields
2.5Opening the database
To open the database, open the front-end file, WFD Reporting 2006.mdb. If the back-end files are in the same folder, and have not been renamed, the front-end will automatically link with the back-ends.
The front-end first attempts to link to the Article 5 back-end, WFD Reporting 2005_be.mdb. If this file cannot be located, either because it has been moved or renamed since last used, an error message similar to the following will be displayed:
<OK> will open the Locate backend file browser window, which can be used to locate the back-end file:
When the back-end file has been located, <Open> will restart the automatic linking process.
<Cancel> will display the following error message:
The tool will then attempt to link to the Article 8 back-end, Article8 Reporting 2006_be.mdb. Assuming it is successful, the start-up form will be displayed. If no link has been made with the Article 5 back-end, the Article 5 functionality is disabled.
If the attempt to link to the Article 8 back-end fails, then similar error messages will be displayed, and the Article 8 functionality disabled.
2.6Start-up form
On successfully starting the tool, the start-up form enables the user to run either the Article 5 or Article 8 components.
Start-up form, WFD Access Tool
<Article 5> runs the Article 5 component of the tool, details of which are given in Section 4.
<Article 8> runs the Article 8 component of the tool, details of which are given in Section 5.
<Exit Tool> closes both the tool and Access.
Placing the cursor on the text ‘Test Back-ends’ and left-clicking displays a message box which gives the locations of the linked back-end files.
The location will be blank if no link had been made to the back-end.
2.7Importing from Article 5
<Article 8> on the start-up form opens the Article 8: Select River Basin District form, below.
Article 8: Select River Basin District form
This is the main control form for Article 8 reporting and gives six options:
- ‘Enter new River Basin District’
- ‘Import article 5 River Basin Districts’
- ‘Open existing River Basin District’
- ‘Delete existing River Basin District’
- ‘Create XML for existing River Basin District’
- ‘Edit Setup Information’
As with Article 5 reporting, Article 8 reporting is done on a River Basin District basis. The Article 8 back-end has its own list of RBDs (tblMonitoring). The EU-level codes and local names of RBDs defined for Article 5 (RiverBasinDistricts) can be imported from the Article 5 back-end using this option. This option is disabled if the tool is opened without linking to the Article 5 back-end.
If an Article 5 database is not available, then you must start by defining a new river basin district. The codes provided should correspond to those defined for Article 5 reporting if appropriate.
2.8Edit Set-Up information
Select the <Edit Setup Information> option to set the MemberState country code, metadata and paths to the schemas, stylesheets etc. Note that if in form validation there is a “No Member State error” this is corrected by using this function.
Before creating XML files, records of setup information must be created. Whichever form is used to begin the XML creation process, if setup information records do not already exist, the XML File Creation Record form, below, is displayed ready for data entry. Setup information is entered on this form to create the setup information records. The requirement for, and the process of, entering setup data for Article 8 XML files is the same as for Article 5 XML files so will be familiar to existing users.
Form XML File Creation Record
The information entered on this form is used to generate the metadata for the XML files, for example the creator, description and security classification, and also to define the paths to the schemas for schema validation. The paths to the stylesheets to convert the XML files to HTML formatted report are also held here. Once opened, if the user attempts to close the form with any of the mandatory data blank, a warning message will be displayed.
Warning message on closing form with missing mandatory data
A similar warning message is displayed if the given ‘Output File Location’ is not a valid folder on the host PC.
The user can choose to close the form with missing data, or with an invalid ‘Output File Location’, but will not be able to open form Create XML Files until correct data are supplied.
If the user attempts to delete either the ’Classification’ or the ‘Country Code’ values, the form will not allow any further action until the deletion is cancelled, either by pressing the <Esc> keyboard key or selecting a value from the respective drop-down list.
If the user attempts to delete any of the schema locations, the form will not allow any further action until either the deletion is cancelled, by pressing the <Esc> keyboard key, or new text is entered in the empty textbox.
By default the schema locations point to the namespace location of the schemas. This requires internet access in order to access the schemas for validating the generated XML files.
Once setup information records exist, they can be viewed and edited from either
- the Select River Basin District form, by
- selecting the ‘Edit Setup Information’ option
- <Continue>
- the Create XML Files form, by
- <Edit Setup Information>
If form XML Creation Record is opened with the Output File Location blank, a default folder is automatically loaded. This default is the folder in which the Access tool front-end file is stored.
<Edit> unlocks the schema location paths to enable them to be changed. This functionality is provided for instances when it is desired to create XML files off-line, using local copies of the schemas. After being pressed, <Edit> becomes <Lock>, which locks the schema location paths and changes back to <Edit>.
<Default> reinstates the default (on-line) schema location paths.
<Close> returns to the form from which the XML File Creation Record form was opened.
Note: The user is responsible for ensuring that any alternative paths entered in this way are valid.
Note: The user is also responsible for ensuring that the versions of the schemas held locally are the same as those held on the remote server.
Form Article 8: XML File Creation Record
Note: The Record Details provided for Article 8 do not have to be the same as for Article 5.
3.data ENTRY
The forms interface is used to collate data required for the schema: Monitoring.xsd. The forms define the data that should be provided to meet the requirements of the schema. For monitoring stations, empty tables are provided for the import of data. These table structures are then used to create the XML files. Forms are provided in the front-end to view and check the validity of direct entry data.