Compact and Repair with Access 2003 on a Windows 2003 / Citrix Terminal Server


We have an Access database stored in a folder on the local file system of a Windows 2003 Server.  2 users access this database file (which is an Access 2000 file, default format in Access 2003) via a CItrix XenApp published applicaiton (Access 2003), or through remote desktop.  

The current file size is approx 788 MB.  Activity with the database causes it to grow to 2 GB (possibly within a week or two.  In order to manage the size of the database file, one of the users regularly performs a compact and repair from within the Access 2003 GUI (tools, database utilities, compact and repair database).

Other items to mention, is that each of these user account on the database server has a disk quota limit of 4 GB.

Now for the problem description:

Every so often (approximately 1 time per month, or 1 time per 1.5 months), a problem occurs after a compact and repair.  The user does not receive a message that an error occurred with compacting and repair, but post compact, the database file becomes inaccessible.  

When I say inaccessible, I mean the file cannot be opened by anyone.  Any attempt to open the file results in a message from Access stating that it can't find the specified file (suggesting perhaps a permission problem).   Sure enough ,when I check the properties of the file, only the general tab exists, the security, details and other tabs are not there.  A copy of the db is created during the compact and repair, and named "db1.mdb" in the same folder as the original access file, and is accessible.

A day or so later, the original file eventually disappears.  Almost as if the server housing the file took 24 hours to delete the file, and the reason it is inaccessible above is because it has started removing the file from the file system.

What I'm looking for is the following:

1)  What is causing this to happen?
1a)  Is it possible that the user is doing a compact and repair when someone else has the file opened, and that is causing the delete process of the compact and repair to fail (my understanding of compact and repair, is that access compacts the db to a new file, deletes the original and then renames the new file to match the old).
2)  How do I prevent this from happening again?

If there is any other information that I can provide to help answer the two questions above, please let me know and I'll be happy to include it.

Who is Participating?
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.

I would think doing the compact/repair with a command line switch in exclusive would resolve the problem.

"Full path/name of MSAccess" "Full path/filename of database" /excl  /compact

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
Sic: in exclusive mode
Chuck WoodCommented:
Is your server running Windows Server 2000? If so, what service pack?
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

siskindsAuthor Commented:
Our server is running Windows Server 2003 Standard Edition - SP2

Can you explain the differences between running the compact and repair from the GUI, and using the command line to do it in exclusive mode?  I would like to see how it fits the symptoms.
Chuck WoodCommented:
In either case, everyone (except the user doing it from the GUI) must be out of the database.

Have you though about setting the database to Compact on Close?
>explain the differences between running the compact and repair from the GUI, and using the command line<

The big difference is running exclusive mode. It prevents others from using the db.

Running it from command line would also allow you to schedule it at night when no one else is using the db.

Note: you probably don't wait to use compact on close. Some impatient users may think the db crashed and either shut down their computer or use task manager to stop it (it has happened to me).
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.