![[logo] US EPA](https://webarchive.library.unt.edu/web/20090510172651im_/http://www.epa.gov/epafiles/images/logo_epaseal.gif){kind=logo}
Section 7: Advanced Users
Reminder
Make frequent copies of your data file (BE_HSEAT.mdb).
- 7.1 HealthySEAT Database Configuration
- 7.2 Configuring HealthySEAT for Multi-user Operation
- 7.3 Importing Shared Data
- 7.4 Integrating HealthySEAT Data with Other Applications
7.1 HealthySEAT Database Configuration
HealthySEAT was developed using Microsoft Office Access 2003, and makes use of two distinct databases – the front end database and the back end database. Both databases are in Access 2002/2003 format.
7.1.1 The Front End Database
The front end database contains all forms, queries, reports and programming code. It is called HSEATv2.mdb and is found in the installation folder (c:\Program Files\HealthySEAT2 by default). This database is compiled (i.e., locked such that users cannot add any additional tables, queries, reports, modules, macros or forms) for two primary reasons:
- To prevent users from inadvertently editing it and causing HealthySEAT to stop working properly.
- To preserve the core functionality of HealthySEAT so that future updates can be supplied to the HealthySEAT user community.
Because HealthySEAT is in the public domain, an uncompiled version of the HealthySEAT front end database may be obtained from EPA. However, be aware that changes to the HealthySEAT front end will not be preserved should future HealthySEAT updates be downloaded and installed from the EPA web site. As a result, users requesting an uncompiled HealthySEAT front end database will be required to acknowledge the potential ramifications.
7.1.2 The Back End Database
The back end database contains most data tables, including those that store data for the assessment guidebook, master checklist, custom checklists, facilities, assessors, assessments, recommendations, etc. It is called BE_HSEAT.mdb. In a single-user setup, the back end database is generally located in a subfolder of the installation folder. In a multi-user installation, however, the back end database will likely be located in a shared network folder (see Section 7.2).
The back end database is not compiled or protected. It may be opened using a full (i.e. non-Runtime) version of Microsoft Office Access 2003. Users may add new objects to the back end database. However, caution should be exercised when editing existing tables. Existing fields in existing tables should not be changed, as these changes could adversely affect the HealthySEAT application.
When an updated version of HealthySEAT is installed, the existing back end database is updated as needed to support new or modified functionality. Thus, the back end database is preserved, along with any new tables, queries, reports, or programming code that may have been added.
Because of the importance of the data contained in the back end database, it is recommended that it be backed up regularly, especially before any user modifications are made to it (see Section 8.4).
7.2 Configuring HealthySEAT for Multi-user Operation
HealthySEAT supports multi-user operation, even though it is set up for single-user operation by default. The only difference between single-user operation and multi-user operation is the sharing of a back end database.
Whenever HealthySEAT is installed, whether for the first time or as an update, a new (empty) back end database corresponding to the current HealthySEAT version is installed locally in a subfolder of the installation folder. The name of the subfolder containing the new back end database corresponds to the HealthySEAT version (e.g., V2). The first time HealthySEAT is opened after installation, it will establish a connection to the new (empty) database.
To configure HealthySEAT for multi-user operation, the back end database must be located in a folder accessible to all HealthySEAT users. Then, each user must configure their HealthySEAT application to link to the shared back end database. The “Re-establish Database Connection” option on the “Customize for District” menu was designed for this purpose (see Section 4.3.1). This must be repeated each time an updated version of HealthySEAT is installed, or if the back end database file is relocated.
7.3 Importing Shared Data
Facility, Additional Facility Data, Assessor, User, and District Remediation Contacts information can be imported or linked into the HealthySEAT back end database from a variety of sources (e.g., Excel, Access, SQL Server, Oracle). There are a variety of ways to do this, though it should only be done by someone experienced with databases and with Microsoft Access.
The table that stores facility information is called tblFacilities. The fields in tblFacilities are described briefly in the following table:
| Field Name | Data Type | Description |
|---|---|---|
| Facility_ID | autonumber | The value of this field is automatically determined by Microsoft Access when a new record is created |
| Facility | Text (255) | The primary facility identifier (it must be unique) |
| Facility_ID2 | Text (255) | The alternate facility identifier |
| District_ID | Long Integer | The identifier of the District (defaults to 1) |
| Address | Text (255) | Line 1 of the facility mailing address |
| Address2 | Text (255) | Line 2 of the facility mailing address |
| City | Text (255) | Name of the city, for mailing address |
| State | Text(2) | Two-letter postal abbreviation for the state or U.S. Territory, for mailing address |
| Zip | Text (2) | ZIP code, for mailing address |
| Phone | Text (50) | Main phone number for facility |
| Fax | Text (50) | Main fax number for facility |
| Salutation | Text (50) | Salutation for primary contact (e.g., Dr.) |
| Primary_Contact_First | Text (50) | First name of primary contact |
| Primary_Contact_Last | Text (50) | Last name of primary contact |
| Primary_Contact_Title | Text (100) | Title of primary contact |
| Facility_Contact | Text (50) | Full name of facility contact |
| Facility_Type | Long Integer | A number indicating the type of facility: 1=Multi User 2=Elementary School 3=Middle School 4=High School 5=Bus Depot 6=Administration 7=Industrial 8=Other 9=0=Career/Vocational |
| Facility_Email | Text (255) | Email address for primary contact |
| Enrollment | Memo | Additional facility information (e.g., enrollment, capacity) |
| Active | Yes/No | Indication of whether facility is active (defaults to Yes, or -1) |
| Primary_Contact_Phone | Text (50) | Phone number of primary contact |
| Facility_Contact_Title | Text (100) | Title of facility contact |
| Facility_Contact_Phone | Text (50) | Phone number of facility contact |
| Facility_Contact_Email | Text (255) | Email address for facility contact |
| Physical_Address | Text (255) | Line 1 of the facility physical address |
| Physical_Address2 | Text (255) | Line 2 of the facility physical address |
| Physical_City | Text (255) | Name of the city, for physical address |
| Physical_State | Text (2) | Two-letter postal abbreviation for the state or U.S. Territory, for physical address |
| Physical_Zip | Text (50) | ZIP code, for physical address |
| Physical_County | Text (100) | County name, for physical address |
The index to additional facility information can also be loaded directly. The table is called tblFacility_AdditionalInfo. This information must be loaded after the facility information, however, because one of the required fields in tblFacility_AdditionalInfo (called FacilityID) links directly to the Facility_ID field (an autonumber field) that is populated by Access when loading facility data.
The table that stores additional facility information is called tblFacility_AdditionalInfo. The fields in tblFacility_AdditionalInfo are described briefly below:
| Field Name | Data Type | Description |
|---|---|---|
| ID | autonumber | The value of this field is automatically determined by Microsoft Access when a new record is created |
| Facility_ID | Long Integer | The primary facility identifier (it must be unique) |
| FileRef | Text (255) | User-defined field that can store up to 255 characters of data |
| Location | Text (255) | User-defined field that can store up to 255 characters of data |
| Description | Text (255) | User-defined field that can store up to 255 characters of data |
| SortOrder | Text (50) | User-defined field that can store up to 50 characters of data |
IMPORTANT: When importing data into tblFacility_AdditionalInfo, each record must have its Facility_ID field set to the Facility_ID of the corresponding facility record in tblFacilities.
The index of users can also be loaded directly into HealthySEAT. The table that stores user information is called tblUsers. The fields in tblUsers are described briefly below:
| Field Name | Data Type | Description |
|---|---|---|
| ID | autonumber | The value of this field is automatically determined by Microsoft Access when a new record is created |
| zUserName | Text (255) | The User Name that the user will use to login to HealthySEAT |
| zPassword | Text (32) | MD5 digest of user’s password (value of 4cb9c8a8048fd02294477fcb1a41191a corresponds to “changeme”, which is the default password) |
| Fname | Text (50) | User’s first name |
| Lname | Text (50) | User’s last name |
| zLevel | Long Integer | User’s permission level (1 = Admin, 2 = Assessor, 3 = Viewer) |
| Title | Text (50) | User’s title |
| Organization | Text (50) | User’s organization membership |
| Phone | Text (50) | Phone number of user |
| Fax | Text (50) | Fax number of user |
| Text (255) | Email address of user | |
| Address1 | Text (50) | Address Line one |
| Address2 | Text (50) | Address line two of User (Mail Stop) |
| City | Text (50) | City of User |
| State | Text (50) | State of User |
| Zip | Text (50) | Zip code of User |
| LastLoginDate | DateTime | Date-Time stamp that shows user’s last login date and time |
The index of assessors can also be loaded directly into HealthySEAT. The table that stores assessor information is called tblAssessors. The fields in tblAssessors are described briefly below:
| Field Name | Data Type | Description |
|---|---|---|
| ID | autonumber | The value of this field is automatically determined by Microsoft Access when a new record is created |
| Assessor_ID | Long Integer | Not used. Defaults to 0. |
| UserID | Long Integer | Not used. Defaults to 0. |
| Fname | Text (50) | Assessor’s First Name |
| Lname | Text (50) | Assessor’s Last Name |
| Level | Long Integer | Not used. Defaults to 0. |
| Title | Text (50) | Assessor’s Title |
| Organization | Text (50) | Assessor’s Department/Organization |
| Phone | Text (50) | Assessor’s main phone number |
| Fax | Text (50) | Assessor’s fax number |
| Text (255) | Assessor’s Email address | |
| Address1 | Text (50) | Line 1 of assessor’s address |
| Address2 | Text (50) | Line 2 of assessor’s address (Mail Stop) |
| City | Text (50) | Name of city, for assessor’s address |
| State | Text (50) | Two letter state code, for assessor’s address |
| Zip | Text (50) | ZIP code, for assessor’s address |
| CC1 | Text (255) | Name of person(s) to receive carbon copies of correspondence from this assessor. |
| CC2 | Text (255) | Name of person(s) to receive carbon copies of correspondence from this assessor. |
| CC3 | Text (255) | Name of person(s) to receive carbon copies of correspondence from this assessor. |
| CC4 | Text (255) | Name of person(s) to receive carbon copies of correspondence from this assessor. |
| CC5 | Text (255) | Name of person(s) to receive carbon copies of correspondence from this assessor. |
| CC6 | Text (255) | Not used. |
| Active | Yes/No | Indicates whether assessor is active (Yes (default) = -1; No = 0) |
| Assessor | Text (255) | Assessor’s full name (First + Last + Suffix) |
| MobilePhone | Text (50) | Assessor’s mobile phone number |
| RespArea | Text (100) | Assessor’s area(s) of responsibility |
| Suffix | Text (50) | Suffix for assessor (e.g., CIH #1234) |
The index of district contacts for remediation can also be loaded directly into HealthySEAT. The table that stores contacts for remediation is called tblRemediationContacts. The fields in tblRemediationContacts are described briefly below:
| Field Name | Data Type | Description |
|---|---|---|
| DCRID | autonumber | The value of this field is automatically determined by Microsoft Access when a new record is created |
| ContactName | Text (50) | The full name of remediation contact |
| Organization | Text (50) | Remediation contact’s Department/Scope of Responsibility |
| Phone | Text (50) | Remediation contact’s phone number |
| Fax | Text (50) | Remediation contact’s fax number |
| Text (50) | Remediation contact’s email address | |
| Address1 | Text (50) | Line 1 of address |
| Address2 | Text (50) | Line 2 of address (Mail Stop) |
| City | Text (50) | Name of city |
| State | Text (50) | Two letter state code |
| Zip | Text (50) | ZIP code |
| Active | Yes/No | Indication of whether Remediation contact is active (Yes (default) = -1; No = 0) |
7.4 Integrating HealthySEAT Data with Other Applications
Because the HealthySEAT back end database is open, HealthySEAT data can be integrated with a wide variety of other applications and databases. In fact, custom functionality, queries, reports, etc. can be developed by building a separate application that links to the HealthySEAT back end database tables.
Caution is needed when manipulating data in existing tables in the back end database, however, as this could interfere with core HealthySEAT functionality.