script access database repair and compact

[Carl Parker]

I have an Access database that is being corrupted on a weekly basis. It is being used by multiple people. Is there a way to script a scheduled repair then compact in 2K?

 

[simongallop]

In Acc2K compact automaticaly repairs the database.

The way that I would do it is to use scheduler to call the access database. Not sure if you can do it directly or whether you need to go through a .bat file. At present I call compact.bat to run at 1am and within compact.bat I have the following:

"c:\program files\microsoft office\office\msaccess.exe" "C:\MyFolder\path\MyDB.mdb" /compact

So long as there is no one in the database it ought to work.

The other way is to create a function:

Function Compact_DB()
Call Application.Run("Compacter.DoCompact", False)
End Function

and run this when the last one leaves!

HTH

 

[DJN]

Compact database Some sites

Here are some links that might help:

"Compact A List" is a free MS Access database that allows you to backup, repair, and compact a saved list of Access database files. It also saves the results of these actions in a table for later viewing, making it suitable for batch or scheduled execution.

http://www.peterssoftware.com/cal.htm

How to Compact Databases at a Scheduled Time

http://support.microsoft.com/default.aspx?scid=kb;en-us;158937

Also, there's a nice utility for scheduling compacting operations from Calvin Smith called "CompactIt" at:

http://home.sprintmail.com/~calvinsmith/compact.htm

You can also use the TSI SOON utility to close a database, compact it, and then open it again (it also copies NT file permissions to the new file):

http://www.trigeminal.com/lang/1033/utility.asp?ItemID=8#8

Another utility is Compact Current MDB From Code:

http://briefcase.yahoo.com/lylefair

FMS, Inc has a product called Total Visual Agent 2000 that handles scheduled compacting and much more:

http://www.fmsinc.com/products/Agent/index.html

Here's some more information about reducing database size:

http://www.peterssoftware.com/t_dbsize.htm


Hope this helps


David

 

[Carl Parker]

THANKS

You guys are soooooooo cool. Is there a forum like this for 2000 server and citirx issues.