I have an Access 2007 database that averages about 1.2 GB (it fluctuates as it goes through weekly processing steps, so we compact it throughout the process). The issue is that the database always crashes when I compact at a certain point in the processing. Here's an overview of the process:
run queries and VB code via macro to update Table 1
run queries and VB code via macro to update Table 2
run queries and VB code via macro to update Table 3
attempt to compact database but it crashes
I suspect Table 3 to have some sort of corruption since the crash occurs after updating that table. After Table 3 is updated I cannot import or export it between databases without a crash. The only work-around I have found so far is to create (from scratch) an empty shell with the same design as Table 3, export the contents of Table 3 as a csv file (because it's 500K records), and import the contents of the csv file into the shell table. After doing this I can compact the database sucessfully and proceed to the next processing steps. However, the next week I experience the same behavior.
what would cause this and how do I resolve it?