compact and repair

[Guest]

I have quite a large database which gets used on many different users of one
computer and is networked to another computer and gets used there only on the
main user.

How often should i run compact and repair - every week ? what is the code to
do this automatically?

 

[Arvin Meyer [MVP]]

That is relative with the amount of bloat and the frequency of computer
crashes (which can cause corruption). Our main server database gets
compacted about twice a week, we have another which is compacted about once
a week. Some of my client's databases are compacted daily, others as little
as once a month. Unfortunately, "many different users" doesn't give me much
to work with.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[Gary J. Dikkema]

But is there a way to do this in code?

That is relative with the amount of bloat and the frequency of computer
crashes (which can cause corruption). Our main server database gets
compacted about twice a week, we have another which is compacted about
once
a week. Some of my client's databases are compacted daily, others as
little
as once a month. Unfortunately, "many different users" doesn't give me
much
to work with.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[Douglas J. Steele]

Yup. There's the CompactDatabase method in DAO, or you can use JRO
(Microsoft Jet OLE DB Provider and Replication Objects).

http://support.microsoft.com/?id=230501 is an example of the latter.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

 

[Gary J. Dikkema]

I should have been a little clearer...

I have linked tables where my Access program loads and unloads them... is
there a simple way to them compact these databases and reclaim the wasted
space?

The invoking program is set to compress on close.

Thanks again!

Gary D

 

[Arvin Meyer [MVP]]

Sorry, I've been out of town. Yes you can compact external databases in
code, but you need to do it from a database that is not linked to it. Just
use a "maintenance" database and open it when you close the current
front-end. The syntax is:

DBEngine.CompactDatabase C:\Path\Oldname, C:\Path\Newname
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access