Technical Issue with Compact & Repair

[prstoessel]

I recently upgraded four desktops in our office to Windows 7 Professional (64-bit) and Office 2013 Professional (32-bit). The prior configurations were Windows XP Professional (32-bit) with Office 2003 Professional. I would like to add that my own workstation has been running Windows 7 Professional (64-bit) with Office 2013 Professional (32-bit) since last year and I am not experiencing the issues (described below) that the other four are.



There are still a few machines that need to be upgraded, but I have put it on hold until I figure out a perplexing issue that has come up. Our business utilizes Access in nearly everything we do. We have thousands of Access database files on our server that are opened/modified at will by one or more users. It has never been a problem to perform a compact & repair until now. All four users with the new configuration have the same problem. Occasionally, it will work. But most of the time, a message is returned by Access saying the filename is already in use. Please see attached screen shot.

An additional problem just pointed out to me (again, duplicated on all four new machines, but not my own) comes when any of the users attempt to import an object or data into Access. Whether the object is an Access query/form/macro/table, or if the user is importing a spreadsheet into a new table, the object does not become visible in the list unless the user closes and opens the database. It's as though it is hidden until the database is reopened. (Hidden objects are selected to be visible in the preferences, by the way.) If the user imports the object, he does not see it, but can actually use it in a query without closing/opening the database. So, it's there, just not visible.

Any help on either of these issues is appreciated.

Kind regards,
Paul

[ItsMe]

I have seen query defs and table defs created that did not appear right away. Never gave it much though. Like you say, the objects are there because the code works.

When I read your post, what jumps out at me is the fact that you can have several users open a DB exclusively. I suggest looking closer at mdb files being open in exclusive mode and the .ldb file not being read correctly over the network.

Not much help but it is all I can come up with. It is interesting the symptoms seem to be isolated to the W7 A2013 flavors. I would say look into trusted locations because of the new machines but, other than the fact they are new machines, the symptoms do not seem to be associated.

[prstoessel]

Thank you for taking the time to reply to my inquiry. Since we are a small business, I can easily confirm that the files were not opened in exclusive mode, nor was anyone else attempting to open it at the same time the C&R was performed.

We are on a Windows domain environment and these new machines have been properly added to the Active Directory. The fact that a C&R sometimes works, but mostly does not. If it was a permissions thing, I would expect it to not work at all. But it does raise a good point that there might be something else along those lines. I just wish I knew where to look.

[ItsMe]

Then I would concentrate on the fact that a file is being opened in exclusive mode and not returned to an available state. Take that statement as fact and look for .ldb files in the same directory as the mdb in question.

With that, consider your insight of who is connected to what file. Locate an ldb. Associate the file to the appropriate mdb. Consider whether or not someone or something is connected to the associated mdb. Move the ldb to a temp or archive folder. There should not be an ldb file if there are no connections to the associated mdb.

Using this approach will address the symptom. Unfortunately, by itself, it is not a solution to the problem. However, it could point you in the correct direction. If you are locating orphan ldb's then there is some hanky panky going on. Maybe a user is unplugging their pc from the wall immediately after initiating compact and repair.

The new machines may be a false positive.

[prstoessel]

Then I would concentrate on the fact that a file is being opened in exclusive mode and not returned to an available state. Take that statement as fact and look for .ldb files in the same directory as the mdb in question.

With that, consider your insight of who is connected to what file. Locate an ldb. Associate the file to the appropriate mdb. Consider whether or not someone or something is connected to the associated mdb. Move the ldb to a temp or archive folder. There should not be an ldb file if there are no connections to the associated mdb.

So I have taken the time to tinker with it a bit more. Each database that I attempted to C&R existed in its own directory, so there is no chance of an .ldb file getting confused with another. And, I made certain no other users were accessing the database, so there are no orphan .ldb files. It would appear that about 1 out of every 7 attempts to C&R I am successful.

If I copy the same database to the local drive it will C&R every time without fail.

[nancyma]

We've had this happen at random with our databases and the only solution we found was:
1. Create a new emply db with a slightly different name.
2. Import all the objects in the original database to the new one.
3. Delete the original one.
4. Rename the new one to the old name (so nobody has to change shortcuts or links)

Once we've done this, the compact & repair works and doesn't seem to hiccup again. We never did figure out what caused it; we only figured out this "fix" by trial & error.

Good luck!
nancyma

[ItsMe]

...If I copy the same database to the local drive it will C&R every time without fail.

This may be something worth pursuing. Single out a specific folder/mdb file. on the server. Go to a W7 machine and map a drive to that folder. Select, always connect at login, option. Log off Log back on. Goto myComputer and use the mapped drive to open the folder on the server. Be sure to open the folder using the mapped drive and then open the mdb this way. See if the statistics of failed C+R's differ. I am curious if how the workstations were joined to AD have anything to do with the symptom's.

You might also try adjusting permissions in AD for the user that is logging into the W7 machine, preferably not the Domain Admin. Give your user full admin rights to the same folder (maybe a test folder). Do this at the server if possible. Then, go to the Security tab for the folder properties and go to advanced. Give this user "Full Ownership" with "Inheritance". See if that makes a difference.

[prstoessel]

Thank you all for your replies. One common thread among the new machines not consistent with my own (remember, mine works and theirs doesn't) is the antivirus software. More specifically, the network security module. When this is disabled both on the file server as well as the client workstation, the problem goes away. Replicated the results on all four machines. This explains why it would work locally but not on the network.

For what it's worth, ItsMe, I had already performed that exercise on a peer machine's shared folder. Good troubleshooting idea. Great minds think alike.

Again, thanks one and all. Mark this one as solved.

[ItsMe]

Thanks for reporting back. I know it helps me to stay on top of things.