Converting an Older Enviro Data Database

Introduction

Before beginning, please read this entire Introduction section down to Specific Steps for Conversion.

When first using a new version of Enviro Data, or if needing to upgrade an Enviro Data database used with an older version of Enviro Data, it must first be converted. This generally involves adding any new tables and/or new fields to your old database.

An Enviro Data database contains a complex set of tables each with specific structures and requirements for their fields. When newer versions are released, changes are generally made to the database structure including adding tables, fields and specific properties of fields. In Version 8, In order to protect the data integrity, we have also increased referential integrity by checking of the data in may tables during this upgrade. 

If you are trying to upgrade a database that you have not used in a while and, you are not sure what version it is in, you need to determine that first. You can do this by opening the database in Access and on your keyboard click the F11 key which opens the table listing. Make sure you are in the table list and then open the Versions table. The database's version number should be shown in the first record. However, if you have been actively using this database and have just upgrade your Enviro Data program you will not need to do this.

If this database is in the MS Access .MDB format, follow the directions below. However, if the databse to be converted is a SQL Server database, a special proceedure must be followed to upgrade it to the newest Enviro Data 8 table structure since the new tables and/or new fields must be added using SQL scripts. Geotech can perform the upgrade for you during a short Teams meeting. Please contact This email address is being protected from spambots. You need JavaScript enabled to view it. to set this meeting up.

Note that if your database is Version 2010 or older, you must first convert it to a 2012 version format. If you need assistance with this, please contact This email address is being protected from spambots. You need JavaScript enabled to view it..

Specific steps for Conversion

For version 2012 or earlier version of the 8 format, simply attach to the database in the latest version of the Editor. This is done by opening the Editor and clicking on Attach Database on the main Editor form to open the Attach Database form and select the target database usig the Select Access File button.

If the database you are attaching to does not need a conversion, you will simply be attached to it. If however, it does not meet the criteria for the new version of your Editor, you will get the following error message.

Note this in the example below, we are upgrading a Version 2012 database.

Click OK and you will see the Update Structure form. Clck OK to continue or Cancel to cancel the conversion.

Note that you may see slightly different working in the form above if you are converting a Version 8 database, but the rest of the steps are the same.

Click OK and Enviro Data makes a copy of your database is created in the same folder but with "GCSBackup" appended to the file name. For this example, the backup file was named EnvDData2012_GCSBackup.mdb

You will also be presented with a summary of the number of records in the main Enviro Data tables. Note that you will have a chance to review these in the next form.

Click OK to start the conversion. 

When the conversion has completed you will see a comparison of the tables from above along with the number or records actually converted.

CAREFULLY CHECK THE RECORD COUNTS FOR EACH TABLE AS THEY MUST MATCH. If they do not match, see the FAQ Why weren’t all of my tables converted when I upgraded to Enviro Data Version 8? - Geotech Computer Systems, Inc.

If these record counts match, the conversion was successful. The converted file is stored in the original folder with "(Version8)" appended to the name. In this example the new file was named EnvDData2012(Version8).mdb.

This error can occur after installing Enviro Data 8 on a computer with an older version of MS Office. Please see How do I fix an Object Library Error or Missing or Broken Reference Error? for a simple fix.

Any custom queries or settings created in Version 2012 are in the Setting2012 file and this file needs to be converted for use in Version 8.

1. Open the Version 8 EDITOR,
2. Click on Attach Database,
3. Click on Attach Settings,
4. Select the C:\Enviro\Edata2012\Settings2012.mdb file,
5. You will see a message informing you that Enviro Data needs to update the Settings file.
6. Click “Yes”. 
7. After the update finishes, rename the file to something more relevant to Version 8 and move it to the C:\Enviro\Edata8 folder.

An Enviro Data database contains a complex set of tables each with specific structures and requirements for their fields. In order to protect the data integrity, in Version 8 we have not only added some new tables and fields, but also increased referential integrity and also checking of your data during the upgrade.

Before starting, if you are trying to upgrade an old table that you have not used in a while and are not sure what version it is in, you need to determine that first. You can do that by opening the database in Access or Enviro Data, clicking the F11 key which opens the table listing and then opening the Versions table. The table's version number should be shown in the first record. An Enviro Data 2012 table can be updated simply by attaching to it in the EDITOR and following the prompts. Databases used with version 2010 will need to be converted using version 2012 before upgrading to Version 8.

If your database is older than Version 2010, please contact This email address is being protected from spambots. You need JavaScript enabled to view it.. for assistance.

To ensure that everything is converted correctly into Version 8, Enviro Data displays an Update Structure Summary showing the number of records in the main tables both before and after the conversion. The example below shows the main tables before being converted. (Note other tables are also being updated in the process but only the main ones are shown.)

If the structure in one or more of these tables violates data integrity, possibly because it had been modified, or some other problem, this will cause the conversion to be flawed but the resulting database will still be attached to the Editor. When this happens, you will see in the form shown below that one or more “Converted” tables will not contain the same number of records that the “Initial Record Count” showed.

YOU MUST PAY CLOSE ATTENTION TO THE UPDATE STRUCTURE SUMMARY FORM TO MAKE SURE YOUR DATA WAS CORRECTLY CONVERTED.

In the above case, an extra field had been added to the Analyses table by the client. Additionally, the RegLimitType table contained some records with descriptions that were not unique.

The issue we encounter the most when upgrading a database to the Ver. 8 format is where a client has modified one or more of the tables in Enviro Data by adding additional fields. In the case above, the extra field in the Analyses table was simply deleted.

A more difficult issue to find is a problem in a lookup table. If a lookup table fails to convert you will not see it listed but this problem may prevent a related table to not convert along with other tables dependent on it. Most lookup tables contain a field for both a code and for the code’s description and all descriptions must be unique in Version 8. This was notrequired  in Ver. 2012. One example of this is where the RegUnit in the RegLimits table has a code for a unit that does not exist in the ReportingUnits table. This can require creating a query with the relationship between the table in question and the appropriate lookup table.

So far, the other most common problem of this type has been in the RegLimitTypes table where limits were imported from a spreadsheet with a RegType longer than the 50 characters that this field holds. This means the RegType (the description), is truncated and may be identical to another one for a different reg. limit.

The easiest way to find these is to open the table in question, sort the description field alphabetically and look for long descriptions that match. The fix is to abbreviate them to 50 or fewer characters so they are unique. In the example below (from the same database as above) the Reg. Limits had been imported with some of their descriptions longer than 50 characters. When these were truncated, they became identical. See below, and how they were corrected.



This fix also applies to other lookup fields where the descriptions may not be unique. The issue is figuring out which tables to check. You can get a clue of which lookups to check based on which of main table (starting with Sites) that did not convert correctly. For example, if the Stations table had a problem, check the lookups shown under Stations using Manage Lookups in the Editor. In the example above, you probably don’t have to check every Station lookup, only those in the “Show Less” tab of the form.

View each lookup in Datasheet view sort the description field and look for identical values and correct any you find. Then move on to the next lookup table for the appropriate table. If you can’t find the problem there, you can click Show More and check more of the Stations lookups.

After a successful upgrade, we recommend moving the new Version 8 database into the C:\Enviro\EData8 folder if you are working on a local copy.

When you start the conversion process the source database is copied with “_GCSBackup” appended to the file name. Additionally, the source database is not touched at all, so you have two backup copies.

If after reviewing the above you still need asistance, contact This email address is being protected from spambots. You need JavaScript enabled to view it..

In the new Enviro Data 8 database file structure, changes have been made to several of the tables to enhance functionality. This means that any 2012 databases you have been using will need to be converted into the new format.

While we recommend using the EDITOR and VIEWER programs that are consistent with the database version in use, an Enviro Data 8 database can be used with the Enviro Data 2012B programs. However, there will be errors when creating a new database or subset, and/or preparing lab reference files with the older version.

If you have databases from Version 2010 or older, these will need to be converted into the next newer format before converting to Ver. 8. In other words, a Ver. 2010 database will need to be converted into the 2012 format using the 2012 Editor before trying to convert it into the Ver. 8 format. If you need assistance with this, please contact support.

Convert an Access Database

For any Access databases, you will use the Enviro Data 8 EDITOR to make the conversion and the EDITOR must be run from the EData8 folder with its associated files. Simply open the EDITOR and use Attached Database button to convert it. This will create a new file with (Version8) added to the end of the file name and also create a backup of your old file.

Convert a SQL Server Database

Your SQL Server databases will need to be converted to the Enviro Data 8 table structure to add several new tables, and new fields to existing tables using scripts. Geotech can perform the upgrade for you during a short WebEx. Please contact This email address is being protected from spambots. You need JavaScript enabled to view it. to set this meeting up.

In the new Enviro Data 8 database file structure changes were made to several of the tables to enhance functionality. This means that any 2012 databases you have been using will need to be converted into the new format.

While we recommend using the EDITOR and VIEWER programs that are consistent with the database version in use, an Enviro Data 8 database can be used with the Enviro Data 2012B programs. However, there will be errors when creating a new database or subset, and/or preparing lab reference files with the older version.

Convert an Access Database

For any Access databases, you will use the Enviro Data 8 EDITOR to make the conversion and the EDITOR must be run from the EData8 folder with its associated files. Simply open the EDITOR and use Attached Database button to convert it. This will create a new file with (Version8) added to the end of the file name and also create a backup of your old file.

Convert a SQL Server Database

Your SQL Server databases will need to be converted to the Enviro Data 8 table structure to add several new tables and fields to the existing database using scripts. Geotech can perform the upgrade for you during a short Teams meeting. Please contact This email address is being protected from spambots. You need JavaScript enabled to view it. to set this meeting up.

While we recommend using the EDITOR and VIEWER programs that are consistent with the database version in use, an Enviro Data 8 database can be used with the Enviro Data 2012B programs. However, there will be errors when creating a new database or subset, and/or preparing lab reference files with the older version. If you need to do these tasks, and do not have a Version 8 program available, contact Geotech Support for assistance.