We help IT Professionals succeed at work.
Get Started

Compact Repair Removing Tables from the ACCDB

156 Views
Last Modified: 2020-02-22
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.
Comment
Watch Question
President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017
Commented:
This problem has been solved!
Unlock 1 Answer and 17 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant

An Experts Exchange subscription includes unlimited access to online courses.

Get Started
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE