ALL Consulting
[ back to RBDMS home ]

The data tables for RBDMS are stored in a separate Access mdb file (RBDMSD97.mdb) or in an ODBC data source. The RBDMS Database Attachment Utility displayed below is used to "connect" RBDMS to its data source.

If the data source is an Access mdb file, then select the "Access mdb" toggle button. If the data source is an ODBC data source such as SQL Server, then select the "ODBC" toggle button. Depending on the toggle button selected, you will be prompted for an "Access mdb file" or a "ODBC Data Source Name." For a mdb file, use the builder button to select the database with a File, Open dialog box. For an ODBC data source, select the data source from the list of ODBC sources in the dropdown combo box.

The RBDMS Directory box is used to specify the location of the home directory.

For the path to the mdb file and the RBDMS home directory, specifying the path with the Universal Naming Convention (UNC) is preferable. To use the UNC within the File Open dialog form, be sure to specify the path by expanding the network neighborhood.

Back to Top

RBDMS makes extensive use of codes in the database to specify types of actions, results of actions, types of equipment, etc. Rather than creating a new table for each type of code, a master codes table is used for storing all code types and a description of each.

Using a master codes table gives RBDMS users increased flexibility. Administrators can easily add new types of equipment or testing methods by accessing the RBDMS Codes form displayed below to add a record to the Codes table.

Each type of code is identified by another code. For example, the form displayed above lists all of the codes available for "ACTION." If another type of action needed to be added, simply add a new line (code = 6) and a definition. Be sure that the code is the appropriate data type for the associated field and, if it is text, that is doesn’t exceed the maximum field length. The tag and value are optional fields that are used to store text and/or numeric information when needed for special codes. If a code will not to be used anymore, turn its active status off. If it is required, check its required field.

The ability to change codes should be reserved for the RBDMS administrator. Adding, updating, or deleting codes should be done with great care. It is possible to make the output of some reports such as the EPA 7520 invalid by improper adjustments to the codes.

Back to Top

The report system in RBDMS was designed to be easily extensible. New reports can be added and selection and sorting criteria can be added and updated using the "Edit Report Filters" option on the Admin menu. As with all of the Admin menu options, this form should only be used by someone who is qualified to make updates to RBDMS.

The "Edit Report Filters" menu displays the "Report Design" form. Each report is displayed as a record on the main form. A subform in datasheet view displays selection and sorting criteria that will be available for each report.

The "Name" field is the name property of the report. Opening the list box for the combo box will display all report names in the current system. To add a new report to the RBDMS report form, perform the following steps:

1. Use the Access report design tools to create the report.
2. Open the "Report Design" form with the "Admin/Edit Report Filters" menu pick.
3. Use the navigation bar to move to a new record.
4. Select the report you created from the drop-down combo box for the name field.
5. Fill in the remainder of the fields to describe the report and selection criteria (see below for details).

New and existing reports can be added and updated by modifying the information stored in the records. A description of each field and its purpose is listed below:

Main Form

1. Name: Name of the report object to be used.
2. Title: Description of report to be displayed in the list box on the report filter form.
3. Get Filter: If the report needs a specialized form to create the record filter, specify it here. Otherwise, leave it blank.
4. Group: Specifies the report group in which this report will be grouped. Current report groups include: INSP (Inspections), REF (Reference), WELL (Wells), UIC, VIOL (Violations), APP (Permits/Applications).
5. Sort: Specifies the position of the report in the list for the group.
6. Description: Description of report to be displayed in the box at the bottom of report list form.

Report Criteria

1. Criteria: Specifies the control name to use for this criterion. Each criteria field for a report must use a different control. Ten combo boxes (C1 through C10), ten text boxes (T1 through T10), and ten date boxes (D1 through D10) are available.
2. Label: Label the text to be displayed next to the control.
3. Repeat: Allows the use of a single criterion to be compared to multiple criteria. Simply increment the counter for each comparison.
4. Sort: Specifies the display and tab order for the criteria fields.
5. Order By: Check this box if you want to include this field as an option for sorting.
6. Type: Specifies the data type of the information being entered. The exception is "V" for verbatim. This means that the selection criterion includes field names and comparisons. Other types include: T "text," N "numeric," D "date/time," and B "boolean."
7. Field: Specifies the field name to compare. These names must match the names available in the report record source.
8. Comp: Comparison operator to use such as "=", "<", "<=", ">=", ">" and "Like."
9. Optional SQL: Include additional SQL selection criteria to be added.
10. ComboSource: Used with combo box controls to specify the items to be included in the drop down list.
11. Column Widths: Used with combo box to specify the width (in 1/1440 units/inch) of each column in the drop down list.
12. Bound Column: Specify the column number from the list to use as criteria.
13. Ltop: Label top position on form (in 1/1440 units/inch).
14. Ctop: Control top position on form (in 1/1440 units/inch).
15. Lleft: Label left position on form (in 1/1440 units/inch).
16. Lwidth: Label width (in 1/1440 units/inch).
17. Cleft: control left position on form (in 1/1440 units/inch).
18. Cwidth: control width (in 1/1440 units/inch).
19. Status Bar Text: text to display in the status bar when this control has the focus.

9.0 RBDMS Startup Implementation

Converting an existing data management system to RBDMS or starting RBDMS from scratch can be a daunting task, and populating the RBDMS tables with data is a significant effort. This topic is intended to give you some guidelines for developing your own RBDMS implementation plan. Try to break up the implementation into smaller, more manageable tasks. Some suggested startup tasks are as follows:

Note: When populating "Pool,"other tables are necessary prior to its completion.

You do not have to populate all fields in each of the tables. Some database fields may be updated later or may not be needed at all for your state. All of the tables listed above do not need to be populated if you do not intend to use the corresponding field in the Well table. For example, the Pool table can be left empty if the pool numbers will not be entered into the Well table. Please refer to the Table Relationships topic for a graphic of table relationships and referential integrity checks.

If you are converting from an existing data management system, you can eliminate a significant amount of manual data entry by using Access append queries. For fields that use different codes than those used by RBDMS, the codes will need to be translated to RBDMS codes. A table called tblTranslate has been created in RBDMS.MDB to store translated codes.

The COMPANY table stores information on drillers, operators, etc. Each company is specified by a unique key composed of an integer part to specify the company (e.g., 1001 might be AMOCO) and a number after the decimal point to indicate the location for the company (e.g., 1001.01 might be AMOCO in Houston, TX). If the company information exists in paper form only, then you will need to use the Company form in RBDMS to enter the information manually.

Once relevant code and reference data has been populated in RBDMS, other operational and well specific information may be populated. This includes information such as individual wells, tests and inspections performed on those wells, permit applications, etc.

The WELL table is the core table in RBDMS. Most other tables have relational linkages to the Well table. It’s unlikely that you will have all of the information for the fields that exist in the WELL table. The only required fields are the API_WELLNO and the owning/operating Company number. You should consider building this table with the information that is readily available and adding other items as appropriate.

The sequence of building additional tables will depend on availability of data and agency priorities. Tables for internal MIT's, external MIT's, Area of Review's, Compliance, and UIC Permits can all be developed independently.

Back to Top

An adequate backup procedure must be implemented to protect from potential disasters such as theft, fire, flood, hardware failure, operator error, etc.

A SQL Server task has been created to automatically backup the RBDMS database to a data file every evening. The backup file is then copied to tape by the CIS group. The RBDMS Administrator should check the task history periodically to ensure that the backups are being performed properly. The transaction logs are being truncated at each checkpoint. If you wish to apply the transaction logs after a restore to recover additional data, then the truncate after checkpoint option should be turned off and the transaction log dumped after the backup each evening.

The RBDMS Administrator will need to add users, groups, and security as needed. The administrator should also monitor database and log space usage and increase the size when needed.

The two primary reasons for performing a restore of the database are hardware failure that makes the disk unreadable or operator error, such as deleting all records in the Well table. To restore the RBDMS database, repair any hardware as needed and use the restore option in the SQL Enterprise Manager to restore the previous nights backup. Keep in mind, however, all data changes since the previous backup will be lost.

Other RBDMS files such as RBDMS97.MDB need to be backed up only after updates such as adding a new form or report to the RBDMS97.MDB file. The System.mdw (Access security) file should be backed up after new users or groups are created or edited.

Back to Top

11.0 SQL Server Maintenance

In addition to the backup, two additional tasks have been scheduled each evening. A "dbcc checkdb (RBDMS)" command is run to check and repair any problems with the RBDMS database. A stored procedure to update table statistics is also run to improve the ability of SQL Server to optimize queries. The administrator should check the history for these tasks periodically to ensure that they are operating correctly.

The RBDMS Administrator will need to add users, groups and security as needed. All table security privileges should be assigned in SQL Server unless you are connecting to an Access mdb database. To simplify system administration, grant access to tables by groups and then assign users to individual groups.

The administrator should periodically monitor database and log space usage and increase the size when needed.

Back to Top

12.0 Enhancing Performance

The performance of RBDMS is dependent on the software and hardware environment in which it operates. Microsoft makes the following recommendations for optimizing Access 97 performance.

* Note that the optimal setting for each item may vary with the type of computer on which you run Microsoft Access. It is usually best to change only one setting at a time and then monitor database performance for improvement.

1. Make more memory available by closing applications that you are not using.
2. Make sure your Microsoft Windows virtual memory (swap file) setting is large enough. In general, the virtual memory setting should be no less than 50 MB. It should be more if you will be running several memory-intensive applications simultaneously.
3. Periodically run a disk defragmentation utility to keep files in contiguous clusters on your hard disk, making file access quicker in general. If you do not defragment your hard disk, the time it takes for Access to retrieve your files may increase if it has to search several physical locations on the disk to retrieve the entire file.
4. Increase the RAM on your computer. Microsoft Access 97 requires a minimum of 12 MB with Windows 95 and 16 MB with Windows NT. Realistically, you should have at least 24 MB with Windows 95 and 32 MB with Windows NT. Additional RAM will further improve performance.
5. Create only as many indexes as necessary. Although indexes can speed access to data, it is possible to "over index" a table so that it is slow adding, deleting, and updating records.
6. Create indexes for joined fields.
7. In a multiple-field index, use only as many fields in the index as necessary.
8. Use Rushmore query optimization in your queries whenever possible. For detailed information on how to do this, search for "Rushmore technology" then "Optimizing Queries with Rushmore Technology" using the Microsoft Access Help menu.

The performance of RBDMS is dependent on the software and hardware environment in which it operates. Microsoft makes the following recommendations for optimizing Access 97 performance.

* Note that the optimal setting for each item may vary with the type of computer on which you run Microsoft Access. It is usually best to change only one setting at a time and then monitor database performance for improvement.

1. Make more memory available by closing applications that you are not using.
2. Make sure your Microsoft Windows virtual memory (swap file) setting is large enough. In general, the virtual memory setting should be no less than 50 MB. It should be more if you will be running several memory-intensive applications simultaneously.
3. Periodically run a disk defragmentation utility to keep files in contiguous clusters on your hard disk, making file access quicker in general. If you do not defragment your hard disk, the time it takes for Access to retrieve your files may increase if it has to search several physical locations on the disk to retrieve the entire file.
4. Increase the RAM on your computer. Microsoft Access 97 requires a minimum of 12 MB with Windows 95 and 16 MB with Windows NT. Realistically, you should have at least 24 MB with Windows 95 and 32 MB with Windows NT. Additional RAM will further improve performance.
5. Create only as many indexes as necessary. Although indexes can speed access to data, it is possible to "over index" a table so that it is slow adding, deleting, and updating records.
6. Create indexes for joined fields.
7. In a multiple-field index, use only as many fields in the index as necessary.
8. Use Rushmore query optimization in your queries whenever possible. For detailed information on how to do this, search for "Rushmore technology" then "Optimizing Queries with Rushmore Technology" using the Microsoft Access Help menu.

Back to Top

13.0 Using NetMeeting for Support

Microsoft NetMeeting 2.0 is a powerful tool that enables real-time communications and collaboration over the Internet or corporate Intranet, providing standards-based audio, video, and multipoint data conferencing support. NetMeeting provides a mechanism for remote support and training for RBDMS. From a Windows 95 or Windows NT desktop, users can communicate over the network with real-time voice and video technology. They can share data and information with many people through true application sharing, electronic whiteboard, text-based chat, and file transfer features.

Application sharing: A user can share a program running on one computer with other participants in the conference. Participants can review the same data or information, and see the actions as the person sharing the application works on the program (for example, editing content or scrolling through information.) Participants can share Windows-based applications transparently without any special knowledge of the application capabilities.

The person sharing the application can choose to collaborate with other conference participants, and they can take turns editing or controlling the application. Only the person sharing the program needs to have the given application installed on their computer.

Shared clipboard:The shared clipboard enables a user to exchange its contents with other participants in a conference using familiar cut, copy, and paste operations. For example, a participant can copy information from a local document and paste the contents into a shared application as part of a group collaboration.

File transfer:With the file transfer capability, a user can send a file in the background to one or all of the conference participants. When one user drags a file into the main window, the file is automatically sent to each person in the conference, who can then accept or decline receipt. This file transfer capability is fully compliant with the T.127 standard.

Whiteboard:Multiple users can simultaneously collaborate using the whiteboard to review, create, and update graphic information. The whiteboard is object-oriented (versus pixel-oriented), enabling participants to manipulate the contents by clicking and dragging with the mouse. In addition, they can use a remote pointer or highlighting tool to point out specific contents or sections of shared pages.

Chat:A user can type text messages to share common ideas or topics with other conference participants, or record meeting notes and action items as part of a collaborative process. Also, participants in a conference can use chat to communicate in the absence of audio support. A new "whisper" feature lets a user have a separate, private conversation with another person during a group chat session.

Back to Top

The RBDMS development team has adopted the Hungarian notation system recommended by Microsoft. The Hungarian notation system uses prefix and suffix codes on object names to identify object characteristics, for instance all form names should be prefixed with "frm".

Some RBDMS objects were created in Access before the adoption of Hungarian notation and were "grand-fathered" in.

The following is a list of common prefixes:

Access Basic Variables

Form Objects