This page describes various advanced database administration tasks, including the tools available via the Admin Tools button on the main menu, as well as database application settings, security settings, and PDF filing conventions.
These tools are avaialble by clicking the Admin Tools button on the main application menu. Most of these tools work on selected reports or resources only. To use them, first make a database selection; if nothing is selected, the buttons will be dimmed.
Update Status Codes
This tool will update database status codes en masse. Simply select the new status code you wish to have applied to the selected records.
Validate Data
This tool will run the same data validation routine that runs every time you finish editing a database record, except that it runs on all selected records. Rather than presenting validation errors in a dialog box for each record, a report is produced showing the errors for each record. This can then be printed if desired.
Check PDF Status
This tool checks for the presence of PDF files for your selected records. For large selection sets, it may take a long time to run, because the application has to check individually for the presence of each PDF file. After the tool has finished running, it will leave selected only those records that are missing PDFs.
Check GIS Status
This tool looks for correspondences between GIS shapes and database records. To use this tool, you must first create and save a selection set in the GIS. Further instructions are provided on the GIS editing page.
Import Resource Records
This tool allows you to import records from the ICDB submittal template. Please see the Processing resource submittals page for more information.
Summary Reports
This tool creates a summary report of Info Center activity over a time period you specify, listed by county. The summary includes the total number of resources/reports; the highest numbers assigned by county; the number of new records added over the selected time interval; updates or additional citations processed; and numbers of records edited without having had updates/additional citations processed. Click the Summary Reports button, then specify the starting and ending dates. This tool does not use a selection set; it examines the entire database.
Relink Tables
This tool allows you to update the table links to the back-end database, if the database has been moved or renamed, or you wish to use a different copy of the back end database.
There are a number of global settings for the database application, which can be customized for each Info Center. These exist in a settings table in the main back-end database. You may need to edit these settings occasionally; however, keep in mind that these settings affect ALL USERS of the application. Please be very careful when editing. Do not rename, delete or add any settings; only edit the values for the settings in the SettingValue column.
To access the settings: at the main application menu, press F11 to show the navigation pane. Find the table called 'atblSettings' and double-click to open it.
BackendVersion - The version of the back-end database that you are using. DO NOT EDIT this value unless specifically instructed.
ICName - The IC's full name, as it should appear on the main menu and the header area of printouts.
ICNameShort - The IC's abbreviation, as it should appear on printout footers.
ICUsesReportPLSS - Set to "True" or "False." If you select 'False', data validation will skip over the township/range fields for reports.
ICUsesResourcePLSS - Set to "True" or "False." If you select 'False', data validation will skip over the township/range fields for resources.
ICUsesResourceUTM - Set to "True" or "False." If you select 'False', data validation will skip over the UTM fields for resources.
NextNumberLookupSkipGaps - Set to "True" or "False." Set to "True" if you want the application to skip over gaps in resource and report number sequences when suggesting new numbers to assign. If you set it to "False," the application will attempt to assign the lowest unassigned number.
NextNumberLookupSpanCounties - Set to "True" or "False." Set to True if your IC uses county-specific report prefixes but still assigns a single range of numeric values per report document.
OHPDataAvailable - Set to "True" or "False." Set to "True" if your IC has an OTIS Access file from OHP. If you do, you must also set the OHPDataPath setting below.
OHPDataPath - Enter the complete path and file name for the OHP database file. The file path must be the same for all database users. If it is accessed through a mapped network drive, that drive mapping must be exactly the same for all users (ie., not the H:\ drive on one user’s computer, and the I:\ drive on another). If the path or file name is not valid, users will see an error message to that effect when launching the application.
PDFLibraryPath - Enter the complete folder path to the PDF library. If it is accessed through a mapped network drive, that drive mapping must be exactly the same for all users (ie., not the H:\ drive on one user’s computer, and the I:\ drive on another). When a user first opens the application, if the folder specified by this setting cannot be reached, the application will display a message to that effect, and no PDF files will be available to the user.
The application can use either a Microsoft Access database for its primary storage, or a SQL Server database. For each of these options, user permissions and user logging are controlled differently. SQL Server offers more control over user permissions as well as better performance.
The logging function of the application uses the Windows user name. Regardless of which back end format you are using, if you want to keep track of who made additions or edits to the database, each user will need their own Windows user account.
Access back-end databases. For Access back end databases, user permissions are determined entirely by the user's permissions on the Windows file system where the back end is stored. To allow read-only access to the database for a given user or group, you must modify Windows' network sharing settings so that that user or group has read-only access.
SQL Server databases. SQL Server can use either its own internal authentication system, or Windows authentication. In the latter case, you can simplify administration by creating database user accounts that map to already-created Windows security groups. For example, you can create a single user account for IC staff that maps to a security group to which only staff belong, and a second user account for read-only access, mapped to another security group for walk-in IC users.
However database users are set up, you grant access to the database by granting the user membership in a database role. For read-only access, assign the user membership in the 'db_datareader' role. For normal read-write access, assign the user to the 'nwic-users' role. This will provide read-write access to all tables, except for the settings and lookup tables, which should need to be modified only by a database administrator.
For ICs using an Access back-end database, it is a good idea to periodically run the 'Compact and Repair' process on the back-end file. This keeps the file size down, and improves performance. Do the following:
Close the database application if you have it open. Other users should also exit.
In Windows Explorer, navigate to the location of your back-end database file.
If you don't have a recent backup copy of the back-end file, make one before proceeding.
Double-click on the back-end file to open it in Access. From the Database Tools menu, select Compact and Repair Database.
Close Access. You can now return to the database application.
For resources, the database application allows you to view matching records in OHP's OTIS system. OHP will periodically supply a version of the OTIS data in a stand-alone, read-only Access database. As updates become available, you can update the file simply by copying the new file over the old one.
This stand-alone Access file needs to be placed in a consistent, known location that you will remember, so that you can update the file in the future. You must set the location of the file in the database settings table, under OHPDataPath, and also set the OHPDataAvailable setting to "True" (see Settings above). If the file specified in the settings table cannot be reached, the application will display a message to that effect, and the OHP data will be unavailable. If the path to the OHP data folder is entered incorrectly in the Settings table, or the OHP database folder gets moved to a new location, the Settings table must be edited to show the correct path.
PDF files must be named consistently so that the database application can find and retrieve them. The application uses a single set of rules for looking up PDF files from the file system, so each IC must follow the convention in order for the application to retrieve them. If you need to rename a lot of files to meet the convention, you can use the free Ant Renamer software.
Top-level PDF library folder. Both reports and resources should be filed under a single folder, with subfolders named ‘reports’ and ‘resources’. This folder must be specified in the settings table, under PDFLibraryPath (see Settings above). When the application launches, if the folder specified in the Settings table cannot be reached, the application will display a message to that effect, and no PDF files will be available to the user. If the PDF library folder gets moved to a new location, the Settings table must be edited to show the correct path.
Reports folder. For ICs that use a single numbering sequence for the entire service area regardless of county, all report PDFs should be filed within a folder named according to the IC's abbreviation (NWIC, NCIC, NEIC). The files should be named according to their numeric identifier (e.g., ‘127.pdf’) , without leading zeroes or IC-specific report prefixes. Note that depending on your Windows settings, the ‘pdf’ suffix may be hidden.
For ICs that use the standard county-based numbering scheme, there should be a folder for each county in the service area, with its full name (‘El Dorado’, ‘Placer’, etc.), not an abbreviation. Within each of these folders, file PDFs appropriate to each county. The naming convention is the two-letter county code, followed by a hyphen, then the numeric identifier, padded with zeroes to five digits (e.g., ‘ED-00127.pdf’).
Resources folder. For all ICs, there should be a folder for each county, with its full name, not an abbreviation. Within each of these, file PDFs appropriate to each county. The naming convention is the numeric county code, padded to two digits if required, followed by a hyphen, then the numeric identifier, padded with zeroes to six digits (e.g., ’09-001234.pdf’). Do not include the ‘P-‘ prefix.