Upgrading / Database Conversion
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 Enviro Data SQL Conversion Support 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 Enviro Data 2010 or Older Conversion Support.
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.
First off, this should not be an issue because typically your Settings file is not replaced during a Repair Install, which is the recommended procedure. However, it is common that the first time you open the new Viewer, it will update your settings file but not change your settings.
Any custom queries or settings created in Enviro Date are in the Settings file and this file needs to be converted for use in the new version.
- Open the Version 8 EDITOR,
- Click on Attach Database,
- Click on Attach Settings,
- Select the C:\Enviro\Edata2012\Settings2012.mdb file,
- You will see a message informing you that Enviro Data needs to update the Settings file.
- Click “Yes”.
- After the update finishes, rename the file to something more relevant to Version 8 and move it to the C:\Enviro\Edata8 folder.
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.
The Enviro Data database file structure changes with each minor version upgrade -- 8C to 8D for example. This is done to enhance functionality. This means that any older Version 8 or 2012 databases you have been using need to be converted into the new format. This is typically done automatically the first time you open an older database in a new version and a copy is made of the old database plus a new one is created with "(Version8)" appended to the end of the file name.
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, a subset and/or preparing lab reference files with the older version database.
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. This can be done by simply opening the EDITOR and use Attached Database button to convert the older database. This will create a new file with (Version8) added to the end of the filename and also create a backup of your old file. The original file will remain unchanged.
Convert a SQL Server Database
Any SQL Server databases will need to be converted to the Enviro Data 8 table structure that matches the version being used. This conversion adds several new tables and fields to the existing database using scripts. Geotech can perform the upgrade for you during a short Teams meeting or, provide the scripts to your IT Dept. Please contact support@geotech.com to set up this meeting.
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. 2008 database will need to be converted into the 2010 format using the 2010 Editor and a 2010 format will need to be converted using a 2012 Editor before trying to convert it into the Ver. 8 format. If you need assistance with this, please contact support.
Upgrading an Access Database
When you upgrade Enviro Data, you will use the new Enviro Data 8 EDITOR to upgrade your database and, the conversion and the EDITOR must both be run from the EData8 folder with its associated files. Simply open the EDITOR and use Attached Database button to convert you existing current database. 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 support@geotech.com to set this meeting up.
An Enviro Data database contains a complex set of tables each with specific structures and requirements for their fields. Each minor upgrade, 8C to 8D for example, Imay have new tables, fields, in some cases increased referential integrity and 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 format it is in, you need to determine that first. You can do that by opening the database in Access, pushing the F11 key on your keyboard (which opens the table listing) and then opening the Versions table. The table's version number should be shown in the first record. If it is an Enviro Data 2012 table, it can be updated simply by attaching to it in the Editor and following the prompts. Databases used with version 2010 or older will need to be converted in steps to the next version and then to version 2012 before upgrading to Version 8.
If your database is older than Version 2012, please contact Geotech Support. for assistance.
If you are using a recent version, and simply upgrading to a newer version. simply open your new Editor, attach to your database, and follow the directions.
Checking Structure Updates
To ensure that everything is converted correctly, Enviro Data 8 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 that the 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 do 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 Geotech encounters 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. the non-unique descriptions were modified, and the database was able to be converted.
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 will prevent a related table to not be converted with other tables dependent on it. Most lookup tables contain a field for both a code and for the code’s description. And all these descriptions must be unique in Version 8. This was not required in Ver. 2012.
Another example is where a RegUnit in the RegLimits table has a code for a unit that does not exist in the ReportingUnits table. To identify 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 that exceeds the 50 characters limit that the field can hold. This means the RegType (the description), is truncated and is now identical to 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 and make them 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 assistance, please contact Geotech Support.
Enviro Data remembers the last database attachment, stores queries saved by user along with other important control information. This information is kept separate from the program files with much of it stored in the Setting8.mdb and other important files so that it is not overwritten during installation of an Enviro Data program upgrade.
Typically, when installing an new update, the Enviro Data InstallShield Wizard will ask what type of install you wish to perform. In order to preserve your current settings, you must choose Repair.
