mSysCompactError code -1907

microconcepts-tpa
microconcepts-tpa used Ask the Experts™
on
I am unable to successfully compact/repair my Access 2007 accdb. The file is fairly large but well below the 2gb limit. This seems to have just started. I have a nightly script that compact/repairs several accdb files. This script ran fine for 12-16 months. Now it seems to fail for all accdbs.

I tried manually compactiong with the same failure. I tried creating new accdb and importing the table, no help. I validated all my indexs, no help. These accdb are just the backend data, contains only table objects.

What I found recently, if I copy the accdb to my local drive, compact/repair runs fine. The source accdbs are all still functional, I just can not compact them. As we know, Access seems to bloat without compacting regularly.

I am concerned my files will reach the 2gb limit if I can not get this resolved. Any help would be appreciated.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
<<This script ran fine for 12-16 months. Now it seems to fail for all accdbs. >>

  So what's changed?   Server?  Updates applied?   Do you have enough disk space?

  With a little detective work, something in the enironment must have changed.  An import to a fresh DB should (not always though) have cleared up corruption in the tables.

Jim.
Database Architect / Application Developer
Top Expert 2007
Commented:
"if I copy the accdb to my local drive, compact/repair runs fine. The source accdbs are all still functional,"

So, this implies to me that you are doing the C&R directly *on* the server?  If so, this is a bad and dangerous idea anyway.  My nightly backup and C&R utility (~25 db BEs) ... among other things, copies all the BEs down to my local workstation, does the C&R *locally*, then copies the BE's back to the server. This has never failed in 4 years.

Have there been *any* permission changes to the folders on your server where these dbs are located ?

Author

Commented:
I can not see any changes to the permissions on the server shares. Automatic updates is not active, I update manually, so nothing gets chanced without knowing. Dbs on Server Storage 2003.

Odd how databasemx mentioned moving the dbs local. I just completed rescripting to do just that. I was originally concerned about the time involved to move data. My plan was to use this as a work around. Sounds like it may be the preferred method anyway.

I hate when things suddenly stop working. I'll deploy the new scripts tonight and check results in the am.

As for the root cause, does anyone think this is NOT a permission issue? I am not completely clear as to the difference between the Sharing tab and the Security tab.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
Doing a C&R over a network is risky because ... if there is ANY momentary glitch, db could get hosed.

Permissions:  C&R requires Deleting and Renaming ... just an FYI.  In general, the folder containing a db needs Full permissions ...

Author

Commented:
Thanks for the advise, I will update results in am.

FYI: Share permissions set for Full Control for all domain users. This has not changed.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
Can't say I've ever seen that error message/number.  How exactly are you doing the C&R and how is error rendered .... just curioius ?

Author

Commented:
C&R done via script:
msaccess {Source} /compact

As I typed that I remembered I changed the script:
Before: msaccess {Source} / compact {Destination}
  where {Destination} was on local machine

Even in 2007, C&R is still creating a temporary database.mdb file. Consecutively numbered if needed to keep file name unique. With my current script, and as it fails, I am left with these mdb files on the server. Thought was to 1-backup local, 2-C&R on the server onto same name. That would leave me a recovery plan if C&R trashes production accdb.

I see this error in the leftover mdb file. Each has a single table mSysCompactError. Table contains rows for each table. I'll attach a sample.
Capture.pdf
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
Seems the PDF is showing "You do not have the necessary permissions ...."

"Even in 2007, C&R is still creating a temporary database.mdb file. "
Not sure what you mean?  C&R always creates temp db - which becomes compacted db, then deletes original.

mx

Author

Commented:
Still do not know why the issue began, but the process to compact locally works. Thanks, MX for the tip.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
You are welcome.  Friday's Tip of the Day:
C&R is the single most important preventative maintenance operation you can do on an Access DB.  

mx

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial