troubleshooting Question

Access DB Crashing on Compact

Avatar of dianna Felton
dianna FeltonFlag for United States of America asked on
Microsoft ApplicationsMicrosoft Access
6 Comments1 Solution408 ViewsLast Modified:
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
compact database
run queries and VB code via macro to update Table 2
compact database
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?
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 6 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros