Automatically Compact Database Monthly

[lhite]

I'm not very good with code, but what I'd like to do is set up some code that runs when a database is opened. The code would check the value in a table (created for this purpose) that would hold the date that the database was last compressed. If it was in the current month nothing should be done, otherwise the current date would be added and the database would be compacted.

Does this sound reasonable?

 

[MichaelRed]

Hmmmmmmmm,

It souinds reasonable, but there are many more questions / issues to be considered -in additions to the anamotapeia (sp?). A partial (and simplistic 'laundry list' might include:

Is this a simgle / multiple user db?

Is it a unified or split db?

Is (if it is split) the BE an Ms. A. database (.mdb), or is it some other 'brand' (SQL Server, Oracle, ...).

What is the nature of the db (e.g. daily transactions adding data -but seldom if ever deleting; primarily a static 'look up' something and generate report(s) on the results of the selection; high volume addition, modification and deletion of records; what is the typical sixe of the db before and after compacting on the monthly basis {I am, of course, ASSUMING you are doing this manually now}).

I'm quite sure this is not the complete list of appropiate inquiries, but it may provoke some thoughts on the question - as opposed to 'knee-jerk' responses.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[lhite]

The database I'm thinking about is a single-user database, however I was hoping to be able to apply this module to other databases as well. The main tables contain corporate data that is accessed by an ODBC connection. I am constantly creating new queries, and occasionally a Make-Table query to capture a set of data for quicker access to the data. Other databases I would like to apply this to are both single and multiple-user databases, where records are added and edited.

 

[thegameoflife]

I used this to compact and backup my DB. I took out the timer event and changed it to an OnOpen autoexec DB. Then had the Scheduled Tasks wizard launch the DB when needed.

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

 

[lhite]

Thanks. I'll try it out next week.

 

[MichaelRed]

For general use, I would suggest that you limit the routine to just a 'suggestion' type of interface. A msgbox might be appropiate, and even this should be limited to the membbers of the Admin group. Many users will react badly to messages and automatic operations which suggest drastic activities are (or are about to be) underway. Also not that to do the compact, you must have hte db open for exclusive use, as Ms. a. will not do a compact with multiple users. So, the ACTUAL routine will need to force all current users off of the db (including yourself) and reopne it. Any activity BEYOND the 'pop-up' suggestion in the typical multiuser environment is -at best- doomed and potentially has carrear (or at least immediate employment) implications. Imagine the 'boss's admin assistant opening the db some early morning to hurridly generate a report he has requested and having to deal w/ first of all even the suggestion 'pop-up', much less having the db 'freeze' while the compaction is done. Worse scenarios run rampant through my imagination.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over