Cross Section Viewer

Convert Database

The Convert Database feature upgrades legacy Microsoft Access Cross Section Viewer databases to the new SQLite format. Simply browse to the existing Access database and then specify a file location for the new SQLite copy.

Convert Database

This feature will copy the contents of an existing Microsoft Access database including all cross section definitions, surveys, selections, long profiles etc. Once you launch the conversion routine, a black command line window will appear while the process is running, showing progress messages. Note that this feature can take several minutes if the original database contains a considerable volume of data.

The conversion also produces a log file describing the steps performed. This file is stored adjacent to the original database and called conversion_YYYYMMDD_HHmmSS.log.

Convert Progress

Prerequisites

The conversion routine requires that you have Microsoft Access (or at least the free Microsoft Access Runtime) installed on your computer. The conversion will use whichever version of Microsoft Access that you have on your computer and it will work with both the 32 bit (x86) or 64 bit (x64) versions.

Duplicate Cross Sections

The old version 1 of the Cross Section Viewer used a Microsoft Access database that allowed multiple occurances of a particular cross section within the same survey. In other words, an individual survey might collect the same cross section twice. This is necessary to support longitudinal calculations without ambiguity about which cross section occurance to use at a particular river distance.

Version 2 of the Cross Section Viewer precludes this and only allows a single occurance of each cross section within an individual survey.

Attempting to convert a legacy Access database that possesses this issue will produce the following message. The conversion will be aborted and no data will be imported into the new SQLite Cross Section Viewer database.

Convert Progress

Identifying the Problematic Data

  1. Browse to the folder where you attempted to generate the output SQLite database using the conversion tool.
  2. Locate the log file, making sure that you open the latest file if you have attempted the conversion multiple times. Each file contains the date and time in the file name.
  3. Review the log file and look for the lines that refer to the duplicate cross sections. They will look like:
2019-07-30T14:10:25.7454508-07:00,message,SurveyID 7 contains duplicate occurances of CrossSectionID 125.

Solution 1: Assign duplicates to new cross sections

Assign the duplicate cross section data to new monumented cross sections with very similar river distances. For example imagine that you have a cross section at river distance 1234.56 for which there are two sets of measurements within a single survey. The goal is to assign duplicate copies to a new river distance (e.g. 1234.57) leaving only one copy at the original river distance.

  1. Open the original database in Microsoft Access.
  2. Open the CrossSections table and find the record that matches the CrossSectionID specified in the log file. Note down the value in the RiverMile column.
  3. Insert a new record into the CrossSections table and user a number for the RiverMile that is very close to the original value. (i.e. if original is 1234.56 then use 1234.57).
  4. Note the CrossSectionID for the new record.
  5. Open the CrossSectionSurveys table and filter the records to the relevant SurveyID and CrossSectionID from the log file.
  6. Edit one of the records and assign it to the new CrossSectionID obtained from the preceeding step.
  7. Repeat this process until all cross sections mentioned in the log file are assigned to new cross sections.

Solution 2: Assign duplicates to new surveys

  1. Open the original database in Microsoft Access.
  2. Open the Surveys table and add a new record with an appropriate title such as Duplicate Cross Section Data. Note the SurveyID assigned to this new record.
  3. Open the CrossSectionSurveys table and filter the records to the relevant SurveyID and CrossSectionID from the log file.
  4. Edit one of the records and assign it to the new SurveyID obtained from the preceeding step.
  5. Repeat this process until all cross sections mentioned in the log file are assigned to new surveys. Remember that the new surveys must also compliy with the rule that they only contain each cross section only once.

Final Check

As a check that either of the two solutions worked, you can perform the following query that should return no records once all the duplicates are resolved:

SELECT SurveyID, CrossSectionID, Count(CrossSectionSurveys.SurveyID) AS CountOfSurveyID
FROM CrossSectionSurveys GROUP BY SurveyID, CrossSectionID HAVING (Count(SurveyID) > 1)