siskinds
asked on
Compact and Repair with Access 2003 on a Windows 2003 / Citrix Terminal Server
Background:
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Sic: in exclusive mode
Is your server running Windows Server 2000? If so, what service pack?
ASKER
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.