Occasionally we come across a database which has corrupt data, in this situation when running the data migration process you may either not get all the rows migrated, or you may see the following error message.

If this happens you will be able to see in MUST the name of the table which contains problem data.

The remainder of this article provides a strategy to identify and correct the corrupt data.

In the example, we will assume that the table Orders has corruption. After running a conversion with MUST open up your database containing your data.

Ensure you have a simple numerical field

The table Orders, contains an autonumber key called OrderID.

If your table does not have a sequential key field, then add an AutoNumber field for example called ID, because we need this to start identifying the corruption.

Get the linked table to SQL Server

The MUST application file you have been using contains links to SQL Server, and we are going to need the link to the new SQL Server table. At this point you can either create your own link or import the link from the MUST application.

Use the external data option in Access to import the linked table from the MUST application, in our example we are using the 64-bit version of MUST for Access 2010.

Then select the sql server table.

Now you will have both the local table and the linked SQL Server table.

Identifying the Corrupt Data

To identify the corrupt data we proceed as follows.

Create an append query to add the data from the Access table to the SQL Server table.

Two points to notice in this query we are going to append records using our ID field to work our way through the data, in the above example we start by appending records with the key between 1 and 30. The second point to notice is that we are not appending the Order ID field.

You can also test this append query by trying to append all the records (you should again get the reserved error warning).

Now we work our way through the records appending them in blocks until we reach a point where the corrupt record will not append, and gives the reserved error message.

At this point delete, or otherwise correct the corrupt record, and verify that you can append the remaining records.

You can now repeat the entire upsizing process with MUST, and migrate everything to a clean empty database.

MUST+SQL allows you to upsize into multiple schemas splitting up an application into manageable chunks or bringing together multiple databases and simplifying security. Click below to improve your development productivity.

Top of Page