Schedule compact & repair for Front-end DB

[Glohamar]

My Access (2000) front-ends grow everytime they are opened and closed. I have checked to be sure I am closing all recordsets, and I am. I had a couple of users call me saying that their databases were not working anymore. I did a compact and repair and everything was fine. I know I can C&R at each closing, but that takes several seconds to do (still using 333MHz machines with 128MB RAM) and the user does not like that. So I am wondering if I can schedule a front-end DB to compact and repair on a users machine after being used for x number of days? Like after 7 days of time has passed, the next time the front-end is closed it would C&R.

Any suggestions?

Thanks
Dave

 

[mgolla]

Just set the Front-end to compact on close

Goto:

Tools->Options

Click on the: General Tab

Check: Compact on Close at bottom left

HTH
Mike

 

[JeremyNYC]

Dave,

Do you use temp tables in your front end? If so, that's what's causing the bloat. I put temp tables in a separate database, and I create a new copy (from a template database I store in the same place on the server) every time I need to put data in the temp tables.

If that's not it, you might consider just copying a new version of the front end to the user's PC every time it's opened. My website has a section on deploying databases that details the way I go about this.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[Glohamar]

Thanks for the quick replies. I know about the Compact on close, but that takes several seconds to run and the users do not like it with the slow machines.

Jermey, I am not using any temp tables, everything is on the back-end. I was hoping that I would not have to copy a new version everytime the user uses the DB.

Thanks for the replies, I was just checking to see if it was possible.

Dave

 

[JeremyNYC]

I do know that I've seen code that compacts the current database, though it uses some trickery to do it. I haven't used it, so I couldn't tell you exactly how it's done.

On the other hand, you could probably find a way to write a batch file that copies a new version of the database every week, or something like that, so that you would keep reasonably fresh copies on the PC and you wouldn't have all that traffic all the time.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[baderms]

http://www.fmsinc.com

has a scheduler for access databases called Total Visual Agent. It's a little pricy, but it just what your looking for.

 

[Glohamar]

Thanks for the great info everyone.
Jermey, I like your idea and I think that is the direction I will go.
baderms, that is great software, but you are right, a little pricey, but thanks anyway.

Dave

 

[mp9]

Total Visual Agent is a little pricey... so check out the Handy Access Launcher (HAL) instead. It's freeware and enables you to schedule compacts, repairs and macro runs. The latest version can be found at

http://www.geocities.com/waddly/accory.html

Hope this helps.