How to Verify Data Integrity After Access Database Migration

The conversion is done. Access says it succeeded. But how do you actually know every record made it across, your relationships are intact, and nothing quietly got dropped or truncated? This guide gives you a systematic approach to post-migration data verification — from a quick 10-minute sanity check to a thorough audit for production databases with years of business data.

Before You Start: Keep the Original

Before running any verification, make sure the original .mdb file is archived as a read-only backup in a separate location. Don't overwrite it. Don't rename it yet. Keep it intact until you've been running the .accdb in production for at least 30 days without issues. The cost of disk space is nothing compared to the cost of discovering a problem and having no baseline to recover from.

Step 1: Table Row Count Comparison

The first and fastest check is a row-by-row comparison between the .mdb and .accdb. You want the record count in every table to match exactly.

In the .accdb, run this VBA in the Immediate Window (Ctrl+G in the VBA editor) to get counts for all tables:

Dim db As DAO.Database, tbl As DAO.TableDef
Set db = CurrentDb
For Each tbl In db.TableDefs
    If Left(tbl.Name, 4) <> "MSys" Then
        Debug.Print tbl.Name & ": " & DCount("*", "[" & tbl.Name & "]")
    End If
Next tbl

Run the same snippet against the original .mdb (open it in a separate Access window). Copy both outputs into a spreadsheet and compare. Every table should have the same count in both files. Any discrepancy needs investigation before you retire the .mdb.

Step 2: Spot-Check Critical Records

Row counts tell you whether records are present, not whether they're correct. Pick 10–20 records from your most important tables — ideally records you know well, like recent transactions, key customer records, or reference data — and visually compare them between the .mdb and .accdb.

Things to check in each spot-checked record:

• All field values match (especially numeric and date fields)
• Long Text / Memo fields aren't truncated
• Currency fields have the same precision
• OLE embedded objects opened correctly (if applicable)
• Lookup fields resolve to the same values

Step 3: Check Referential Integrity

If your .mdb enforced referential integrity on relationships, verify that the .accdb has those relationships and constraints intact. Go to Database Tools → Relationships to see the relationship diagram. Compare it visually with the .mdb's relationship diagram — they should be identical.

To find any orphaned records (records in a child table with no matching parent), run this pattern for each relationship:

-- Find orders with no matching customer:
SELECT Orders.*
FROM Orders LEFT JOIN Customers ON Orders.CustomerID = Customers.CustomerID
WHERE Customers.CustomerID IS NULL

In a healthy migration, this query should return zero records. If it returns records, either the data had pre-existing integrity problems (which would have been present in the .mdb too — worth checking), or something went wrong in the conversion of that table.

Step 4: Verify Calculated and Aggregate Values

For databases with business-critical summaries — total revenue, inventory counts, account balances — run your key summary queries against both the .mdb and .accdb and compare the results. These totals are your strongest signal of overall data completeness. If the total revenue figure is the same in both files, you have high confidence the underlying data migrated correctly.

Create a simple validation query spreadsheet:

Query	.mdb Result	.accdb Result	Match?
Total orders this year	4,832	4,832	Yes
Total revenue YTD	$1,247,883.40	$1,247,883.40	Yes
Active customer count	312	312	Yes
Open inventory items	1,945	1,945	Yes

If any aggregate values don't match, drill down into the discrepancy before going live with the .accdb.

Step 5: Test All Forms and Reports

Data can be intact but forms and reports can still break due to VBA issues, ActiveX control changes, or reserved word violations in queries. Open every form that users rely on and:

• Verify it opens without errors
• Add a test record, edit it, and delete it (or save and undo)
• Check that all dropdowns and lookup controls populate correctly
• Run every button and VBA event handler

For reports, open each one and verify it renders the correct data for a date range or filter you can cross-check against the .mdb output.

Step 6: Compact and Repair

After verifying data integrity, compact the .accdb. The conversion process doesn't compact the file, and a freshly converted database often has internal fragmentation. Go to Database Tools → Compact and Repair Database. This also runs an internal consistency check — if Compact and Repair reports errors, investigate immediately before putting users on the database.

When Something Doesn't Match

If row counts or aggregate values differ between .mdb and .accdb, your recovery path depends on the type of discrepancy:

• Counts differ by a small number: Check for records with problematic characters in text fields (null bytes, control characters) that may have been handled differently by ACE.
• A whole table is empty: The conversion may have failed silently on that table — re-run the conversion and check for errors on that specific table.
• Aggregate values differ: Run the difference query to find the specific records that are present in one but not the other.

The original .mdb is your recovery baseline. Don't discard it until every check passes.

For the complete migration walkthrough, see the Complete Guide to Access Database Migration. For a comprehensive post-migration checklist covering VBA, security, and performance, see the Access Migration Validation Checklist.

← Back to all posts