[AccessD] ...monitoring be mdb size

Well there's not a lot to it really
=================================

Function CompactRepair()
Const strOldMDB As String = "X:\y\z\your.mdb"   'Amend as necessary
Const strNewMDB As String = "X:\y\z\Compacted.mdb"

Dim strSubject As String
Dim lngOldSize As Long
Dim lngNewSize As Long
Dim lngChange As Long
Dim lngOldSizeKB As Long
Dim lngNewSizeKB As Long
Dim lngChangeKB As Long
Dim lngOldSizeMB As Long
Dim lngNewSizeMB As Long
Dim lngChangeMB As Long
Dim db As Database
Dim strSQL As String


On Error GoTo Repair_Err
lngOldSize = FileLen(strOldMDB)
DBEngine.RepairDatabase strOldMDB

On Error Resume Next
Kill strNewMDB
On Error GoTo Compact_Err
DBEngine.CompactDatabase strOldMDB, strNewMDB
lngNewSize = FileLen(strNewMDB)
lngChange = lngOldSize - lngNewSize

lngOldSizeKB = lngOldSize / 1024
lngNewSizeKB = lngNewSize / 1024
lngChangeKB = lngChange / 1024
lngOldSizeMB = lngOldSizeKB / 1024
lngNewSizeMB = lngNewSizeKB / 1024
lngChangeMB = lngChangeKB / 1024

strSubject = "Repair & Compact: SUCCESS " & lngOldSizeMB & "mb To " &
lngNewSizeMB & "mb (saving " & lngChangeMB & "mb)"

'My email routine takes two parameters - body and subject. You'll do your
own thing no doubt.
Call YourOwnEmailRoutine("Repair and Compact of " & strOldMDB & " ran
successfully" & vbCrLf & vbCrLf _
& "Old Size " & Format(lngOldSizeKB, "#,##0") & "kb" & vbCrLf _
& "New Size " & Format(lngNewSizeKB, "#,##0") & "kb" & vbCrLf _
& "Reduction Of " & Format(lngChangeKB, "#,##0") & "kb" & vbCrLf & vbCrLf _
& "NOTE THAT THIS HAS ONLY BEEN RUN ON AN OFF-LINE COPY OF TITUSDAT.MDB FOR
INFORMATION PURPOSES." _
, strSubject)

'I also keep a record in a stats table
strSQL = "INSERT INTO COMPSTAT (CST_DATE, CST_FROM, CST_TO) VALUES(Now()," &
lngOldSizeKB & ", " & lngNewSizeKB & ")"
Set db = CurrentDb
db.Execute strSQL

CompactRepair_Exit:
On Error Resume Next
db.Close: Set db = Nothing
Exit Function

Repair_Err:
strSubject = "Repair & Compact: FAILED"
Call YourOwnEmailRoutine("Repair of " & strOldMDB & " failed with error " &
Err & vbCrLf & vbCrLf & Err.Description, strSubject)
Resume CompactRepair_Exit
Resume 0

Compact_Err:
strSubject = "Repair & Compact: FAILED"
Call YourOwnEmailRoutine("Compact of " & strOldMDB & " failed with error " &
Err & vbCrLf & vbCrLf & Err.Description, strSubject)
Resume CompactRepair_Exit
Resume 0

End Function
==========================================


--
Andy Lacey
http://www.minstersystems.co.uk



--------- Original Message --------
From: "Access Developers discussion and problem solving"
<accessd at databaseadvisors.com>
To: "Access Developers discussion and problem solving"
<accessd at databaseadvisors.com>
Subject: Re: [AccessD] ...monitoring be mdb size
Date: 02/04/07 10:25


Andy,
me too, please, if is possible...
Many thanks in advance,
Ervin
----- Original Message -----
From: "Andy Lacey" <andy at minstersystems.co.uk>
To: "Access Developers discussion and problem solving"
<accessd at databaseadvisors.com>
Sent: Monday, April 02, 2007 11:34 AM
Subject: Re: [AccessD] ...monitoring be mdb size


> Hi William
> Only just spotted this. You've probably done it by now, but..
>
> I have an MDB which runs on a nightly schedule. What it does is slightly
> different from yours in that it measures the difference in size between
> the
> the live BE (well a copy actually) and one which it Compacts. It then
> emails
> me telling me by how much the BE would shrink if I threw everyone out of
> the
> system to run a compact. It's only a few lines of code and nothing you
> couldn't do in your sleep but you're welcome to it if it's any use.
>
> --
> Andy Lacey
> http://www.minstersystems.co.uk
>
>> From: accessd-bounces at databaseadvisors.com
>> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
>> William Hindman
>> Sent: 31 March 2007 22:23
>> To: Access Developers discussion and problem solving
>> Subject: [AccessD] ...monitoring be mdb size
>>
>>
>> ...I want to monitor the size of my be mdb's and gen an
>> e-mail if they grow
>> by a certain percentage in a given time frame ...has anyone
>> already done
>> this and have a solution they're willing to share?
>>
>> William Hindman
>>
>


________________________________________________
Message sent using UebiMiau 2.7.2