Compact and Repair Database automatically.

[ErikZ]

My database adds and deletes data frequently. As you know, this causes the size of the database to balloon up to a system halting size.

This is going to be an application that I hand out to users all over the company and I don't think they know/understand the problem.

I've been looking into getting the program to compress/repair itself, but I can't seem to find anything. You can't do it while the database is open, and you can't run the code to compress the database while the database is closed! I'm sure you should be able to do it when you exit it though. Has anyone solved this problem?

 

[ErikZ]

Heh, boy do I feel dumb. Found the answer in Help.

FYI there is a checkbox so the database compresses on close.

1 On the Tools menu, click Options.
2 Click the General tab.
3 Select the Compact On Close check box.

Hey, it's Monday.

 

[Paul7905]

Erik, I have a question re: your solution:

I tried this solution in my installation and the result was less than desirable.

First, the database resides on a server running NT 4.0.

Second, the compact on close ran as it should.

Third, after the compact on close ran, for whatever reason I am not sure, the NT security "permissions" on the database are lost and when the users try to open the database, they get an access denied error from NT.

I think it may have something to do with the fact that the Compact actually creates a new database and somehow in the process the security permissions on the database get hosed.

Have a solution for this or anyone else have a similar issue they solved ?

 

[ErikZ]

Well, our databases are very different. Mine will be emailed to each user and they will use it locally. So no permissions need to be set.

When I was researching the problem, I believe I came across the problem you're talking about. It DOES make a new copy when it compresses it. I'm assuming this is some sort of failsafe in case of catastrophic error (i.e. loss of power).

If your database is just running off the server, I'd suggest a two-part solution.

One: Kick off all the users and shut down at 2am.
Two: Have a scheduled command run at 2:15am to compress the database. It's a command line switch.

The only problem is that I don't know if this way also looses the permissions. I'm very interested in seeing your results.

 

[CC801340]

What is the command line switch to compress it??

 

[ErikZ]

Use Access help and do a search for:

Startup command-line options

You'll get a full list of switches and info on what they do.

 

[Paul7905]

Erik,

Yes, the user permissions to the database are still gone after the compact runs. After the compact runs, I can go in and look at the permissions on the database and they are all gone. I then have to set up the NT File permissions to make the .mdb available again.

Don't know why this happens, I figure there must be an NT workaround for this, just have not run across the solution yet. (many folks run compact utilities after hours that don't mention having this problem).

Paul

 

[ErikZ]

I'm not sure, but I think this is why.

They handle database security within the database using the built in security features. So that anyone can open up the database, but without the correct Access permissions it won't let them view or change anything inside.

But I've never had to deal with Access Database security, so you're better off asking them.

 

[AccessAce]

Paul7905,
The NT solution to your problem would be to grant permissions at the folder level, rather thanthe file level. I'm not sure you're still reading this thread, but try moving the database into a folder where the permissions can be set there. That way, when the new database gets created, which happens everytime you compact, it will have the correct permissions.

 

[BillPower]

Hi,

If you are using Access 2000 or 2002, you can find a Function to automatically compact your databases when the database grows to a predefined size that you specify at design time. Thread181-420875

 

[BillPower]

Gave the wrong thread there, should have been Thread181-421003.

 

[phcar]

If the front end DBase resides on the network, then all users will have to be logged out in order for the application to compact. Check the Access WEB for solutions re. automatically closing the database for all users and performing automatic compacting. The security issue sounds like you're not pointing the users to the workgroup when the file opens. Remember to use the /wrkgrp switch to open the workgroup prior to opening the application. Here's an example: "C:\Program Files\Microsoft Office\Office\MSACCESS.EXE" "S:\DBasefiles\SampleDBaseFront.mdb" /wrkgrp "S:\DBasefiles\SampleDBaseWorkgroup.mdw"