Internet-based Biodiversity Database Workshop April 24 - 27, 2000


MS Access Approach to BCD

Robert Leitch

Systems Administrator, New Mexico Natural Heritage Program

The NMNHP BCD database was developed in MS Access by my predecessor (Keith Elliot) and several NMNHP staff members from the AREV BCD via exported text files. There is no direct file interaction between the AREV BCD and our database. Corrections & Updates are made to the Access version and an export file is created and sent to the TNC for updating and data collection. The AREV version is used very little for daily NMNHP operations but the core database fields (with some modifications & additions) are used as the basis for the NMNHP Access BCD database. The NMNHP network consists of Windows NT 4.0 Workstations running MS Access 97 on a Windows NT network. Several other MS Access databases are also tied into the Access BCD (considered our 'Core' database) and the data is also linked via ODBC to our website (http://nmnhp.unm.edu) and to several Internet based, searchable web pages on the NMNHP website.

MS Access was chosen as the platform for the NMNHP database to extend the existing design of the AREV BCD and to allow for future enhancements, links to other NMNHP databases, interaction with our GIS ESRI applications (ArcInfo and ArcView) via direct database links (Access to Access) and via ODBC (ColdFusion and Crystal Reports). The next question for the NMNHP is whether to upgrade the existing Access BCD to MS Access 2000 or port the database to either MS SQL Server or Oracle. Any of the options will require additional programming and conversion with Access 2000 being the easiest to Oracle being the hardest (due to new learning curve). Our staff is most proficient in MS Access.

MS Access was chosen as the platform for the NMNHP database to extend the existing design of the AREV BCD and to allow for future enhancements, links to other NMNHP databases, interaction with our GIS ESRI applications (ArcInfo and ArcView) via direct database links (Access to Access) and via ODBC (ColdFusion and Crystal Reports). The next question for the NMNHP is whether to upgrade the existing Access BCD to MS Access 2000 or port the database to either MS SQL Server or Oracle. Any of the options will require additional programming and conversion with Access 2000 being the easiest to Oracle being the hardest (due to new learning curve). Our staff is most proficient in MS Access.

The NMNHP website has several searchable Animal and Plant databases that are either subsets of the MS Access BCD database or directly connected to the NMNHP BCD database. For security reasons, the actual databases used by our website have been relocated to the web server and connected to ColdFusion and Crystal Reports via ODBC. This combination allows the NMNHP to have a searchable web-based internal database with links to external Internet sites. Our web server is powered my MS Internet Information Server (IIS 4.0) on a Windows NT server.