I have several DBs on my network drive that no longer shrink in size when I compact them. All of the DBs in question are derived from the same parent DB (they were copy and pasted from an "original" DB). These DBs do shrink in size if I copy and paste them onto my local drive and compact them there. Other DBs on the network drive that wasn't derived form the same parent DB compact without a hitch on the share drive. Has anybody ran into this problem before?

Are you sure they are not in use at the time you try to compact them?
How exactly are you going about comapct them?

They're not being used when the are being compacted. Compacted by going to "Tools" → "Database Utilities" → "Compact and Repair Database"

What are your permissions in the folder that houses each database?

Can you create, modify, delete files in each folder?

It's not a permissions issue. If I copy and paste another DB (one that's not derived from the "original DB") into the same folder, I can successfully compact that DB

This post has been edited by guerillaunit: Feb 22 2012, 04:00 PM

I have this same exact problem.

I always split the database so each user has their own local front end. The back end file only has the tables but I put a button on a toolbar that has the "compact and repair..." button. Users SHOULD be able to leave their respective front ends, open the back end and click that button and have the database compact. It doesn't work. It warns them that someone's in the database. I have to instruct them to copy the database to their desktop, run it and click that button, then move it back to the shared folder.

It's not likely to be a permissions issue, because users have full permission on the shared folder. If they didn't, they wouldn't be able to get far using the application. That said, if there's somewhere I could look or alter the permissions to make this work correctly that would be helpful.

While I'm glad that I'm not the only one with this problem it would be great if someone knew a solution! (IMG:style_emoticons/default/smile.gif)

SHADOW

I have made it a point to never C/R over a network... in doing so, all the stuff must travel to the client, then get C/R'd then back to the network.

I'm not sure details on shadow copies, but possibly this is a factor?

Also, you may want to have a quick read on this: Compact and Repair Database over a Network

Not a ton of help, but I think if you make it a practice to copy the files over before C/Ring, then copy them back, you'll be a lot safer on a few different levels. The operation can be automated relatively easily with some script or VB.

hth

Jack:

Thanks for the info.

I would understand if it just took a long time to compact and repair because you're going over the network but it just doesn't work (on some networks). According to that Candace Tripp page, it's a real no no.

I would love to have an easy and reliable way to copy the file to a local location, compact and repair, then send it back home over the network. Most users get really stumped by the concept of transferring the back end and then back when you're done.

Thank you

SHADOW

Personally, I don't think I'd leave this up to a user, especially if this is a shared DB. Too much room for error. How would the user know if others were actively using the DB when they are C&R'ing?

niesz is right, this is not something the end user should be doing or responsible for. I don't even let my end-users know where the back-end is store, little alone mess around with it.

@jleach - Thank you for sharing that information. I was unware of the risk. I've never had a problem, but will no longer temp fate after reading the warning. Better safe than sorry.

My work has hundreds of dbs that we compact across the network with no issue. I've wanted to script something to do all the copying etc to make a local compact easier. It really does this anyway. The only issue I've seen is that Access sometimes gets confused when putting the temp db from the local machine back over the network copy. My big issue with copying the file down to compact is that I really need to make the original network copy go away somehow, delete or rename, or a user may get into it while the maintenance is happening.

Ken

I can second the sentiments of Candace Tripp. I've personally experienced corruption when performing C&R across a network.

Never really found the specific issue. The DB was rather large ... >500MB. I've also heard that non-Microsoft networks (like Novell) have a really bad track record for this sort of thing.

So I've learned to err on the side of caution, and just not do it. If I need to, I copy the BE down to the local workstation, then C&R, then move back. It's a lot faster, too than doing it across a network.

Walter:

Personally, I don't think I'd leave this up to a user, especially if this is a shared DB. Too much room for error. How would the user know if others were actively using the DB when they are C&R'ing?

I select ONE user from the office I trust the most and I tell her to wait until the .ldb file is gone before doing the compact/repair.

I have almost 250 offices using my application across the country and I can't possibly log in to each to compact for them (IMG:style_emoticons/default/smile.gif)

SHADOW

This post has been edited by shadow: Feb 23 2012, 12:45 PM

In such cases I implement an automated compact routine that does so every x days, or when the file size grows by x %. I still wouldn't put this in the realm of user responsibility.

But if it has worked for you up until now without any issues, no reason to change. Different strokes for different folks.

Daniel:

In such cases I implement an automated compact routine that does so every x days, or when the file size grows by x %. I still wouldn't put this in the realm of user responsibility.

That is a VERY good idea! How do you accomplish this? How can the routine make sure that no one is in the database?

SHADOW

The routine simply checks for the existance of an .ldb file (the locked database file, which is present in the same folder as the db in question, with the same filename).



Additionally, when I do this I place my own file in that same directory, sort of a reverse. In my FEs that connect to it, if this maintenance file exists, it doesn't allow any connection (generally this is just a notice to the user that the backend is undergoing maintenance, followed by a shutdown of the FE - when the whole op is complete, the maintenance file is removed).

If you automate this on the server, the process looks something like this:



This of course assumes that you have Access installed on the server. If this must be done from a client, the pseudo-code is very much the same, though you'll want to ensure much more time for file transfers (to the point where you'll likely want loops with checks for the destination file existence along with a timeout set ungodly high just in case). The important thing here is to NOT MOVE THE LIVE DB UNTILT THE C/Red REPLACEMENT IS BACK ON THE DRIVE!

Example:


I personally have a real tendancy to take this stuff way overboard, but thereagain on the other hand I don't run into any sort of data or file corruption problems. That stuff is my #1 concern... I can't imagine the loss if one of these files went heels up on me. I backup my backups and keep them in different physical locations miles apart. I'm not even kidding...

Anyway, whether or not you feel you need to implement it as cautiously as I do, that's pretty much the basic rundown. The rest is simple FileCopy, Dir() and Name funcitons/statements in VBA, and of course the CompactDatabase call itself. Just remember to keep that one live version safe until you're sure the replacement is good to go.

Cheers,