troubleshooting Question

Compact Repair Removing Tables from the ACCDB

Avatar of mlcktmguy
mlcktmguyFlag for United States of America asked on
Microsoft OfficeMicrosoft AccessWindows OSWindows Server 2012
17 Comments1 Solution184 ViewsLast Modified:
Compact Repair Removing Tables from the ACCDB

Short version: I recently upgraded my office machines to Win 10 Pro.  Now, when I pull an accdb from my client’s machine to my office machine, then run a compact/repair on the transferred data, on my machine, tables are lost.

This is new behavior since I upgraded my office machines to Win 10 Pro.  I have been working with this client for 10 years and many times have pulled accdb’s from their machine to my machines and compacted them with no issues.  I tried this on another machines in the office that was recently upgraded and the compact/repair also removed tables.

Here’s the current configuration.

Client Machine            Windows 2012 R2      Access 2013
Office machine            Windows 10            Access 2013

Compacted ACCDB size      1.03GB


Much longer story with much more, probably too much, detail
I am adding new functionality to a client’s Access 2013 application.  I wanted to test with their current data so I pulled it to my office machine from their server.  The first thing I did on my office computer was run a compact/repair.  At the end of the compact repair there were tables missing from the accdb.  I thought maybe the data was corrupted so I created a blank database, pulled in all of the tables from their original, non-compacted DB and then ran the compact repair on the new DB.  Same result, tables were removed.

I connected back into the client’s server backed up the accdb, then ran a compact/repair on their server.  No issue whatsoever.  All tables intact.  On their server I also created a blank DB and imported all of the tables, no issue whatsoever.

Next I tried completely rebuilding the accdb on the client machine before downloading it to my machine.  I created a blank accdb and imported all of the client tables.  For good measure I ran a compact/repair on the new accdb.  No issues there either.  I pulled the new accdb from the client computer to my office computer, ran the compact/repair and lost tables.

The short story is the rebuild and compact/repair work without issue on their server but neither works without issue on mine.

When I try importing the accdb that was transferred to my machine into a blank accdb I get errors ‘system resources exceeded’.   Most of the tables are intact in the new accdb but others are missing.

Since ‘upgrading’ to Win 10 Pro I noticed this elsewhere when working with the clients files on my machine.

I run monthly process for one of my clients in the Access 2013 application I developed for them.  Since it’s a long process I”
1.      compact/repair the data on their server,
2.      zip up the data on their server,
3.      transfer the data to my machine,
4.      unzip the data on my machine
5.      run the process on my machine,
6.      compact/repair the data on my machine,
7.      zip up the data on my machine,
8.      send it back to their machine and
9.      unzip on their machine.

The application has been in production for 8 years and I have followed this workflow without issue every month during that time.

This month when the process completed running and I ran step 6 (compact/repair) the DB size shrunk to approx. 512MB.  That would usually be a good thing but in shrinking the DB the compact repair also removed some tables.  The DB was not complete.

At that point I had to rerun the process on my machine, zip the files without compact/repair and send them back to their server.  This is doable but I never had this issue before.  When I transfer the file back to their server and run the compact/repair everything is fine.  It is only when I run the compact/repair on my machine that data is lost.
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 17 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 17 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