Repair and Compact

[StefanOl]

Dear Sir or Madame

Is the a System call in VBA that do the same thing or simillar
as the "Compact and Repair" that you can do manually in the
Access menue system ?

I would like to have the ms access client to do this every time it starts.



Regards Stefan

 

[vbaInet]

Is there a need for this? In my opinion (and in the context in which you've asked), there isn't a need if you've setup the database "correctly" with the right indexes, closing objects, PKs and FKs etc. You should only need to perform this operation as the developer or admin. I don't even think you can do so in a multi-user environment if more than one user is logged in.

Nevertheless, here's what I found:

http://www.tek-tips.com/faqs.cfm?fid=4998
http://www.developerfusion.com/code/4142/compact-repair-ms-access-database/

I don't know if it applies to your situation.

 

[rainman89]

You can set compact on close in the database properties. The repair is not really necessary

 

[StefanOl]

Dear Sir


> rainman89
That might be a better idea, to do it on exit, thanks a lot for the tip

> vbaInet
The reason is that when I work with the ms access client the
ms access client file itself (the one on the desktop) grows from
9 MB to 1.9 GB.
I suspect it is some "recover" mode in ms client that keeps track
of what tables I create and destroy on the fly during the VBS execution.

Perhaps a "compact" on exit might solve this issue.


Regards

 

[vbaInet]

> vbaInet
The reason is that when I work with the ms access client the
ms access client file itself (the one on the desktop) grows from
9 MB to 1.9 GB.

That's a rather huge growth. Maybe you need to revise your database and check up on the "recover" you speak of.

 

[DCrake]

This situation tells me certain things

1. the database is not split
2. If split then plenty of deletes taking place on local tables
3. database is not normalised
4. Make table queries may exist which bloats databases

 

[ghudson]

Delete the records in a table, then append the new records instead of using make table queries.