Running compact and repair after 50uses 1

[RamziSaab]

is there a way to do the do a automatic compact and repair on close after 50 uses automatically on close of the database

 

[ajdesalvo]

Place the following function in a Module. Then either call the Function from a Macro called AutoExec, which runs every time you start up the database, or place it in the open of a form that is opened once per session. Like a Log On screen, or a Switchboard that opens once.

The Function CompactCheck() needs a table I called "MDB Open Count". It has one field named "Open Count", which should be an Integer.

Hope this Helps!


Function CompactCheck()

Dim OpenCount As Integer

DoCmd.SetWarnings False

DoCmd.RunSQL "Update [MDB Open Count] Set [Open Count] = [Open Count] +1 ;"

OpenCount = DLookup("[Open Count]", "MDB Open Count&quot

If OpenCount > 2 Then
DoCmd.RunSQL "Update [MDB Open Count] Set [Open Count] = 1 ;"
Application.SetOption "Auto Compact", True
Else
Application.SetOption "Auto Compact", False
End If
DoCmd.SetWarnings True

End Function

Anthony J. DeSalvo
President - ScottTech Software
"Integrating Technology with Business"

 

[madrue]

i understand your reply above but am unsure as to how you call the function in the macro.
also, Ramzi mentioned to have it work after 50 counts, is this set in the code above or would it need to be put in, if so where?

thank-you for your help.

Rue

 

[Glohamar]

I am not sure with the macro, it has been a while since I have used them, but you will want to creat a new macro with the name Autoexe, Access auto runs that macro by default. Give it a Macro Name and for the Action you will want to select RunCode. At the bottom next you will want to type CompactCheck() and save it with the Autoexe name.
The code he is using has the compact happening after two times. Change the number 2 in this code line "If OpenCount > 2 Then" to what ever you want. For RamziSaab it would be 50.
If you follow ajdesalvo directions, you will have no problems getting this to work, but if you do, post back and I will help. I am using this for my front-end DB's and it works great.

HTH
Dave

 

[bradles]

Have a star Anthony, that's an excellent function.

 

[grobermatic]

Hi all,

Does this only work on certain versions of Access, I'm on Access97 and I get the runtime error:

'Auto Compact' is not a valid name.

Cheers

Craig

--------------------------------------------------------------------------------------------------------
"Time-traveling is just too dangerous. Better that I devote myself to study the other great mystery of the universe: Women!" .. Dr E. Brown (1985)