Solved Can't compact due to folder permissions

[Darrell]

I've just started getting this lovely message when I try to compact my database...

"The compact and repair operation has been cancelled. You might not have adequate permissions to the folder that the database is located in. You need full permissions to the directory the database is located to compact and repair. Contact your system administrator for more information."

Which seemed a bit strange as it's been in the same network folder for years and I can edit / delete any other file on that folder.
Copied the database to my desktop and it works fine, so I'm assuming that something has gone screwy with the network settings.

So I raised a ticket with our IS team and what a waste of time that was. They may as well have asked my to unplug my pc and plug it back in again for all the help they've been. They have absolutely no idea and are wasting a lot of my time by connecting remotely and just clicking on things at random.

So I'm hoping that someone here can help me instead...?

 

[GPGeorge]

As a matter of fact, it is a best practice to ONLY perform compact and repair options on the local computer anyway.
So, rather than pursue this, I would recommend adopting the best practice.

 

[The_Doc_Man]

The way that a Compact & Repair is done is that Access first creates a blank DB file and then, one entity at a time, copies everything over to the blank DB. The tables that have primary keys are copied in key order. Indexes get rebuilt. During this process, you have a file visible in the working folder called DB1 or a similar name. Once the C&R is completed successfully, the old DB is deleted and the new DB is renamed to match the prior name. If the C&R fails, the old DB becomes an "automatic" backup. The new DB contains a special table that enumerates problems with the C&R.

For this series of actions, you need MODIFY permission on the DB itself and MODIFY on the folder that contains it, plus (at least) PASSTHRU (an advanced permission) on all folders starting from the root folder and down the tree of folders to the folder in question. The advanced permissions are DELETE FILE, CREATE FILE, WRITE FILE, WRITE FILE (to folder), and perhaps a couple of more of the advanced settings having to do with file attributes - ALL of which are provided if you have the broad-brush permission MODIFY on the folder and the files therein.

As GPGeorge points out, best practice is to copy the file to the local computer, make a backup (just in case the C&R goes belly up), then do the C&R locally, and finally copy the result back to the shared folder.

However, I'm a bit confused, to be honest. Unless your users only touch the shared file as READ-ONLY, you shouldn't be able to share the file anyway, because the first user to open the DB has to create the .LDB or other locking file (CREATE FILE), the second and subsequent users must modify it (WRITE FILE), and the last person to disconnect from it for the day has to delete the .LDB or other locking file (DELETE FILE). Further, that locking file MUST be in the same folder as the shared file, and that is also where a "default" C&R occurs. So if you can't do a C&R, there is another problem.

Now, if someone still has the file open, that would stop you COLD - not only for the C&R, but also if you wanted to rename the file. You should test for the file to be closed at the time, as evidenced by the lack of the locking file. However, if there has been a "dropped lock" somewhere, the locking file would not exist but a Windows FILE lock could still exist. Your IT staff can tell if a file is opened by a user. Get all of your users to exit from the DB, then check for the locking file to be gone. After that, try the C&R. If it fails, exit from Access and double check that the locking file is not in the shared folder. Then ask your IT staff to see if the DB file is locked by a Windows process. Ask him/her to unlock the file.

 

[Darrell]

copy the file to the local computer, make a backup (just in case the C&R goes belly up), then do the C&R locally, and finally copy the result back to the shared folder.

Yip that is exactly what I have been doing for the last week.

Unless your users only touch the shared file as READ-ONLY, you shouldn't be able to share the file anyway,

There is only ever 2 of us that use it and we sit next to each other so we have a good idea of who is using it and when.

So as stated, we have been operating like this with the same file (set to C&R upon close) in the same folder for many, many years with no issue, except for in the last week. So, it's more of an inconvenience, and I'm just curious as to why it used to be fine but now isn't.

 

[The_Doc_Man]

You say "except for in the last week." IF... the problem started on a Wednesday, the odds greatly favor that you got caught by a Windows Patch because (as I learned with the U.S. Navy), Microsoft pushes Windows patches every other Tuesday night. When we had something that "went south" on us on a Wednesday, we checked the Windows Patch History right away to see if any security patches or app-specific patches got tossed into the mix. Odds were, if it was a Wednesday, it was patch aftermath day.

 

[Darrell]

Microsoft pushes Windows patches every other Tuesday night.

Thanks for the input Doc, I don't know why but I didn't even think about those updates. I should have, as pretty much every problem I've had over the last 10 years has been from M$ 'improving' something
Will follow the recommendations to compact on local drive, thanks guys.