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?
thenelsonConnect With a Mentor Commented:
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
Sic: in exclusive mode
Chuck WoodCommented:
Is your server running Windows Server 2000? If so, what service pack?
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

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 WoodConnect With a Mentor Commented:
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?
thenelsonConnect With a Mentor Commented:
>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).
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.