Resolving common errors

The most common types ‘failed database structure checks’ are produced as a result of:

1. a table having been deleted or an additional table has been included (such as an Access ‘error table’ left in the upload – see below)
2. the pre-set format of a field having been changed (such as ‘short text’ being changed to ‘integer’ or a 2-character limit being changed to 255)

These will result in schema error messages with details given such as the below:

1. “SchemaRow(table_name=FILENAME_IMPORTERRORS'” – where an ‘error table’ has been left in the upload file at submission
2. “-SchemaRow(table_name='IDS001MPI', column_name='NHSNUMBER', data_type='CHARACTER VARYING', character_maximum_length=10)_

+SchemaRow(table_name='IDS001MPI', column_name='NHSNUMBER', data_type='INTEGER', character_maximum_length=None)_”

–  where the field type has been changed to ‘integer’ with the character limit removed (the change being highlighted by ‘+/-‘ characters), this can also happen if records aren’t appended on import to the existing tables in the IDB (if whole tables are copied in or created as new in a format the doesn’t exactly match the IDB)

If you receive a failed database structure check error message from validation of your submitted file or are concerned that the structure of your IDB file may have changed, it is possible to use a report in Access to check to ensure that your IDB file’s schema matches the original IDB schema. This will restore individual changes that have occurred within the structure and properties of the IDB, avoiding the need to re-import all of the data files into a fresh (clean) copy of the IDB. To compare an IDB file with the original IDB download the following steps must be followed:

1. Download the latest IDB template from the TRUD website (see direct links, according to the data set, above under ‘Accessing the IDB’)
2. Ensure all tables in your IDB file have the same names as the original IDB template downloaded and that there are no additional tables
3. Ensure each table has the same columns and column (field) names as the IDB template
4. Ensure each column has the same data type and constraints (e.g. length) as the IDB template – this can be done via a report from access. 
   1. Open a database in Access
   2. From the database tools menu run database documenter
   3. Select the tables tab in Documenter and then select all tables. 
   4.  Click options and under include for table untick all 3 options
   5. 1. Under include for fields select names, data types, sizes
      2. Under include for indexes select nothing
      3. Click ok and then click ok in Documenter to produce the schema documentation for the open database
      4. Once the object definition report has been produced, you can export the report as a text file using the text file button on the ribbon at the top of the application
      5. Repeat for the second database
      6. Compare the text files for your database and the IDB template to identify any differences

Error tables are produced within Access if the data to be imported doesn’t match with what is specified during the import process. The most common of these, a ‘type conversion error’ – such as in the below screenshot - relates to conflicting date formats as discussed above under step 6 of ‘Using the Access Import Wizard’. It’s important that the date format specified under the advanced settings of the import wizard matches with the dates formats in the file being imported when viewed in a plain text reader. This should be checked, and the settings amended on the import wizard, to prevent this error from occurring.

The type of field doesn’t exist error shown in the below screenshot is caused by the field names (headers) identified in the imported file not matching the field names in the import destination table. This could be caused by:

• the field names in the import file (first row) or destination table not matching or having been altered; or,
• the wrong import file having been selected for the destination table; or,
• the wrong destination table having been selected to append the records to at the first stage of the import process

This should be checked and the import then re-attempted.