[AccessD] ...monitoring be mdb size

Andy

...thanks much ...I did find some code over on Dev's site that looked like 
it might be adapted to what I wanted ...but your's looks like it should do 
the job nicely ...I'll give it a shot ..tks again.

William Hindman

----- 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 7:53 AM
Subject: Re: [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
>
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>