asked on 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.
Neither have I heard of this behaviour.
Run the process on another workstation/laptop of yours having an updated Office install. If success - as suggested by Anders - reinstall Office on your primary machine.
Another option, if it is the same tables that get deleted, is to make a CSV copy of the data in these tables, delete the tables, recreate them from scratch, and import the CSV copy in the new tables.
That will ensure that some "old stuff" is not being carried over.
What version of Access is installed on your machine?
Jim.
Mark Edwards: Do you have a link to any discussion of Compact/Repair removing records?
Here’s the current configuration of m,y machine and the current machine
Client Machine Windows 2012 R2 Access 2013
Office machine Windows 10 Access 2013
Compacted ACCDB size 1.03GB
Couple things
On this:
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.
Just to be clear, an error is an error. The output is no good even if you don't see anything wrong or not.
On this:
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.
Only time I've ever seen that happen was due to bad NIC drivers. But you've already side-stepped that issue by zipping. Bad NIC drivers would cause the unzip to fail the CRC/Hash check done when unzipping.
So something on your machine for sure.
I would do a repair of office, followed by applying updates.
Jim.
I have all of my clients trained to run a daily compact/repair on their data. This morning I got a call from client that lost about a quarter of their data after running the compact/repair. This client has been following the morning compress routine. They had run the morning C/R on the last three work days with no issues. Today they lost tables.
This client also had the same issue a week ago, after I had posted this question on EE so I didn't add it to my post.
I have been doing research on this and came upon the linked really old post in another forum. It may not be even remotely pertinent but the eventual resolution was a change in the buffer size. How do you change the buffer size.
"out of resources" can be caused by setting buffers to high.
To set it, you use:
dbEngine.SetOption dbMaxBufferSize, xxxxx
in code where xxxxx is a number from 0 to 65535. You can also set it via registry.
Zero is the default setting, which runs a calculation on what to make the setting. That calculation has not been documented in many, many years, and it's not known if it's changed or not.
Some have set MaxBuffers to 65535 to get around other problems in the past, only to find out that with recent versions it causes out of resources. This happens with 32 bit apps where the process address space for Access is limited to 2GB.
In the past, this was always discussed as setting the Buffer size, but what it actually does is set a high water mark of when buffer clean-up will be triggered. So if you make it too high, Access actually can run out of resources before it is able to be trimmed back.
Jim.
"C:\Windows\System32\cmd.e
at the command prompt and in early testing it seems to work, with some adjustments.
When I tried it 'as is' is I got a message 'MSACCESS.EXE" cannot be found.
My MSACCESS.exe is in C:\Program Files\Microsoft Office 15\root\Office15
So, at the command prompt I first entered
cd "C:\Program Files\Microsoft Office 15\root\Office15"
then I entered
"C:\Windows\System32\cmd.e
Microsoft Access Opened up and I selected the DB I wanted to compress. The compress ran and was resultant compressed DB was completely intact, no tables missing.
My next step was to try and create a batch file to do all of these steps automatically. So far I have not been successful. My current effort looks like this
cd "C:\Program Files\Microsoft Office 15\root\Office15"
"C:\Windows\System32\cmd.e
but this doesn't open MSACCESS. Not sure why. If you can help with creating the bat file I would appreciate it.
What I'd like to do is create a bat file for each of the DB's I have to compress (CompressDB01.Bat and CompressDB02.bat). Any time I want to compress them I would just run the appropriate bat file. The bat file would change directories, execute your command and open the appropriate DB. I have tried multiple variations of a bat file and none of them work.
My DB's are in directory "I:\My Documents\Access_Databases
Also, is there somewhere I can find out more about the 'Afinity' setting. I would like to understand why it is resolving the issue. I did some research and found that it is setting the number of cores for the processing application to use. Why does that help? Does this mean that my clients have the afinity set in their system?
cd "C:\Program Files\Microsoft Office 15\root\Office15"
"C:\Windows\System32\cmd.e
but when I combine them into a bat file, the bat file opens and closes quickly. Access is not started.
Do it as:
C:
CD
Then the command.
Jim
And by the way you can do the compact by using the /compact switch on the command line.
As for the affinity setting what it does is restrict the processors that an executable can run on.
Using the value of one means it can only run on the first processor. Why this allows the Compact to run correctly means there is a bug related to the process and multi threading. Why it shows up on some systems and not others nobody has determined. Probably depends on a number of factors.
last if this is repeatable, I’m sure Microsoft would like to have a look at this.
Jim
I'm not sure what prompted you to suggest the afinity issue but it appears to be spot on. I've tested many time since I got the bat file sorted out and all of the compresses were without issue. If I don't set the afinity they all have issues.
This missing table issue only started happening when I upgraded to Win 10 Pro 2 months ago. I had been using this same work flow including the compact repair on my office machines for that client over 9 years without issue. On these same machines for several years.
The afinity fix worked on both of my office machines that had recently been updated to Win 10 Pro. Both of which were having compress issue since the Win 10 Pro upgrade.
Thank You
Glad to hear you got it sorted.
Jim
If that doesn't help, consider a full re-install of office.