MS Access 2007 Compact and Repair error

DFS replication
1 file server is read only

Error: Could not use "I:\FileName.accdb"; file already in use

IT team suggested open in exclusive mode  (tried didn't work)
the files are dev accdb nobody else is in them.
Security Read/Write/Modify/execute etc but not full control like admin

temp solution is copy file to C:\ compact and repair their then copy back to network folder (argh)
Anna WickAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

The compact and repair operation creates a new empty database and imports all objects into it.  It then deletes the original database and finally renames the new database to the name of the old database.

That means you need create and delete permission also.

Compacting a database on the server takes an excessive amount of time.  I'm not sure why but copying the db to a local drive, compacting it, and copying it back to the server is significantly faster than CR on the server.  Apparently, there is a lot of back and forth with data as the CR runs and all that has to go from the server to memory on the PC running the compact and back again.
Anna WickAuthor Commented:
Yes all users have create and delete permissions

I work with a lot of different access tools, multiple custom dbs for each client. that's a lot of copy CR and back to file server. If I could trust my PC not to die again I could move all my dev there.

just seems to me that now with all MS environment, there should be a solution.
If you are worried about backups, I would suggest a Carbonite subscription.  I think it is still around $55 per year which makes it an excellent bargain.
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
temp solution is copy file to C:\ compact and repair their then copy back to network folder (argh)
You should never perform a Compact operation on a file across the network - too many things can happen that can irretrievably corrupt your file. Instead, copy the file to your local drive, compact it, and then copy it back to the original location.
Gustav BrockCIOCommented:
We also sports DFS replication, and I've seen exactly the same. I found that the reason is that the workstation is too fast.

So, I always pull the file to a local folder (could simply be Desktop) and open it from there.
When done, I close the file, create a copy, opens and compacts the original, close it, and copy this back to the network folder.

This is quite simple really, and have never failed for me.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Anna WickAuthor Commented:
didn't get a new solution just conformation to what I was already doing
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.