How to fix MS Access table that grows dynamically until it hits the 2 GB limit?

Question

I have an Access table that "appears" to exceed its 2 GB limit, but isn't really that big. In fact, the size for the entire .mdb file is only 4 MB.

The table is clearly corrupted, but in a way that makes it difficult to deal with. When I open it up, it seems to dynamically grow, with chunks of rows being repeated. The table has a composite primary key, so in theory, it should be impossible to have duplicate rows.

If I open up the table in MS Access and attempt to go to the last record (or just wait a while), I eventually get an error message saying the 2GB limit has been hit, and "#Error" appears in every cell.

If I attempt to do a Compact and Repair, it seems to hang, with the side effect that a 2GB Database.mdb file gets created.

I have dealt with corrupted tables before, but never one that dynamically replicates its own rows. Any suggestions how to address this?

Answer 1

Here are a few diagnostic measures and solutions:

1. Check a VBA process happening in background: behind a form in OnOpen or OnCurrent events, button triggers, a called function =somefunction() or macro DoCmd.OpenMacro. This sounds like a loop may be running across all rows. If unaware where to start, find button triggers, after update event, exit routines on forms that shows a status bar message at lower right or cursor is showing the hourglass/spinning wheel.
2. Check iterative action queries (Append, Update, and Make-Table queries, ADO/DAO Recordset Updates).
3. Decompile/Recompile VBA code and Compact & Repair the database.
4. Create a new database and import the tables from the possibly corrupted copy.