Access databases

MS Access satellite databases

MS Access databases are used to create forms, labels, queries, reports, to import data, and as easy ways to edit data directly in the tables. They are secondary to the primary database in MS SQL Server, and are used because they offer a more user-friendly front end to the database than the MS SQL Server applications.

Tables within satellite MS Access databases are linked to the MS SQL Server database MBARI_Samples (ODBC name "sample1") using Linked Table Manager (an add-in found under the Tools menu). One of the satellite databases, filename "sampleForms.mdb" (part of the SamplesDB web on cyclone, use FrontPage to find it), has the tables to be linked on the server cyclone so that it will operate properly from any other computer (important: don't relink them from your own computer). Any number of these satellite databases can be created. They require that users have MS Access running on their machines (NT or PC), and their ODBC set up to find sample1.

Forms: Forms to add and edit data were created in the Access database sampleForms.mdb . The form "frmInventoryEntry" contains subforms for editing data and creating new collection group, collection event, and sample records. Using this form enforces referential integrity between the various tables in the database, whereas working directly in individual tables does not.

Labels: Labels containing information from the database may be made using MS Access database reports. A label report "Rock Labels" (for rock specimens) was created in sampleForms.mdb (find it under the Reports tab). The steps for making labels with it are to first modify the query "qryLabel1" (find it under Queries tab) to return just the specimens for which labels are to be made, preview the report, and print labels out on appropriate paper. Labels may also be made using MS Word mail merge on the data in ODBC sample1 (the MS SQL database MBARI_Samples), by Macintosh and other users without MS Access.

Queries: Customized queries can be built with MS Access to query on other criteria or return other information than the web-based queries have been set up to do. Use the Query Wizard; save only those that you expect to use over and over or that were difficult to construct.

Reports: Data from the database can populate custom reports, such as summaries of hazardous materials at a storage location. Use the Report Wizard in MS Access.

Import data: The table MSSQL_IMPORT, found under the Table tab in the MS Access satellite database "sampleForms.mdb", is the temporary table into which data is imported from Excel and upon which the stored procedure "migrate_import" operates.

»Next: Source files