[AccessD] Compact BE on Close - An Example

Christopher Hawkins clh at christopherhawkins.com
Thu Aug 26 10:59:40 CDT 2004


Sure, I'll hunt it down.  It's on one of the zip disks in that stack
over there...*pointing to a mountain of storage media*.

CountCurrentUsers?  Is that a native Access function or something you
wrote yourself?  I haven't heard of that one.  But then again, I"m
not as active in Access as I was a few years ago, so I may not be hip
to the newest stuff.

-Christopher-

---- Original Message ----
From: dwaters at usinternet.com
To: accessd at databaseadvisors.com, 
Subject: RE: [AccessD] Compact BE on Close - An Example
Date: Thu, 26 Aug 2004 07:29:57 -0500

>Chris, 
>
>The function called CountCurrentUsers will prevent the CompactBE 
>procedure
>from running until the last user is exiting.
>
>I like your VB utility approach.  My system also has a 'shutdown' 
>time
>period during the night so that backup processes work well.  This 
>would be a
>good time for a scheduled compact.
>
>There's nothing retentive about it!  I've had a 20 Mb BE grow to 80 
>Mb -
>this caused the IT dept. to start an investigation as to why they 
>were
>having network problems.  They weren't aware that they needed to 
>compact the
>BE regularly.  So, a method to automatically compact the BE avoids 
>the whole
>issue.
>
>Chris - would it be possible for you to post the VB utility code, or 
>to make
>it available to the folks on this list?
>
>Thanks!
>Dan
>
>-----Original Message-----
>From: accessd-bounces at databaseadvisors.com
>[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of 
>Christopher
>Hawkins
>Sent: Wednesday, August 25, 2004 8:14 PM
>To: accessd at databaseadvisors.com
>Subject: RE: [AccessD] Compact BE on Close - An Example
>
>What do you do with multi-user apps?  You don't want ot compabt the
>back-end while othr users have an open connection, do you?
>
>Maybe I'm retentive, but I wrote a VB utility that compacts a
>database.  It runs as a scheduled service on the server.  My clients
>usually have it set to fire at midnight or thereabouts.
>
>-C-
>
>---- Original Message ----
>From: dwaters at usinternet.com
>To: accessd at databaseadvisors.com, 
>Subject: RE: [AccessD] Compact BE on Close - An Example
>Date: Wed, 25 Aug 2004 09:12:27 -0500
>
>>Hello to All!
>>
>>I've believed for a while that setting Compact On Close for the FE 
>>would
>>also compact the BE.  But a little testing disproved that.  For the 
>>BE to
>>auto-compact, it must actually be opened by the Access application 
>>and then
>>closed.
>>
>>With some help from Stephen Bond, I was able to put together this 
>>code to
>>compact the BE
>>when the FE is closed by the last person.
>>
>>Two things about the BE: First - you must set its Compact On Close 
>>option to
>>true.  Second - avoid User Security on the BE or your users will be 
>>asked to
>>enter their user name and password as they are shutting down their 
>>FE.
>>
>>You should be controlling the FE so that pushing an Exit or Quit 
>>button is
>>the only way the FE can be closed.  (Discussed in previous posts.)  
>>When you
>>push that button, call the CompactBE procedure immediately before 
>>using the
>>DoCmd.Quit method.
>>
>>I've used this successfully at one customer site.  I hope this will 
>>work for
>>everyone - if you see any problems with this then speak up!
>>
>>Thanks!
>>Dan Waters
>>ProMation Systems, Inc.
>>
>>PS - I'm going to try to see if this is publishable information.
>>
>>
>>
>>'-- Paste the following into a Standard Module titled 'Compact BE' 
>>
>>Option Compare Database
>>Option Explicit
>>
>>Public Sub CompactBE()
>>On Error GoTo EH
>>
>>    Dim stgPath As String
>>    Dim blnExclusive As Boolean
>>    Dim appAccess As Access.Application
>>    Dim varReturn As Variant
>>    
>>    If CountCurrentUsers > 1 Then
>>        Exit Sub
>>    End If
>>    
>>    stgPath = "\\ServerPath\SystemFolder\BackEnd\BE.mdb"
>>    '-- Keep users out while the BE is compacting
>>    blnExclusive = True
>>    
>>    Set appAccess = New Access.Application
>>    
>>    varReturn = SysCmd(acSysCmdSetStatus, "Compacting BE")
>>    
>>    appAccess.OpenCurrentDatabase stgPath, blnExclusive
>>    '-- When the BE closes it will auto-compact.  And it happens 
>>quickly!
>>    appAccess.CloseCurrentDatabase
>>    appAccess.Quit
>>    
>>    varReturn = SysCmd(acSysCmdClearStatus)
>>    
>>    Exit Sub
>>    
>>EH:
>>    Application.Echo True
>>    Call GlobalErrors("", Err.Number, Err.Description, "Compact BE",
>>"CompactBE")
>>
>>End Sub
>>
>>Public Function CountCurrentUsers() As Integer
>>On Error GoTo EH
>>
>>    Dim con As New ADODB.Connection
>>    Dim rst As New ADODB.Recordset
>>    Dim stgAccessVersion As String
>>    Dim intUserCount As Integer
>>    Dim stgUserName As String
>>    
>>    stgAccessVersion = SysCmd(acSysCmdAccessVer)
>>
>>    '-- The user roster is exposed as a provider-specific schema 
>>rowset _
>>        in the Jet 4.0 OLE DB provider.  You have to use a GUID to _
>>        reference the schema, as provider-specific schemas are not _
>>        listed in ADO's type library for schema rowsets
>>
>>    Select Case stgAccessVersion
>>        Case "9.0"
>>            '-- This is from MSKB 198755 and is specific to Access 
>>2000
>>            con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data
>>Source=\\ServerPath\SystemFolder\FrontEnd\FE.mdb"
>>        Case "10.0"
>>            '-- This is from MSKB 285822 and is specific to Access 
>>2002 or
>>2003
>>            Set con = CurrentProject.Connection
>>        Case "11.0"
>>            '-- This is from MSKB 285822 and is specific to Access 
>>2002 or
>>2003
>>            Set con = CurrentProject.Connection
>>    End Select
>>
>>    Set rst = con.OpenSchema(adSchemaProviderSpecific, ,
>>"{947bb102-5d43-11d1-bdbf-00c04fb92675}")
>>    
>>    If rst.EOF = False Then
>>        Do While rst.EOF = False
>>            stgUserName = Left$(rst.Fields(1), InStr(1, 
>>rst.Fields(1),
>>Chr(0)) - 1)
>>            If stgUserName <> "Admin" Then
>>                intUserCount = intUserCount + 1
>>            End If
>>            rst.MoveNext
>>        Loop
>>        CountCurrentUsers = intUserCount
>>    Else
>>        CountCurrentUsers = 0
>>    End If
>>    
>>    rst.Close
>>    Set rst = Nothing
>>
>>    Exit Function
>>    
>>EH:
>>    Application.Echo True
>>    Call GlobalErrors("", Err.Number, Err.Description, "Count 
>Current
>>Users", "CountCurrentUsers")
>>    
>>End Function
>>
>>
>>-- 
>>_______________________________________________
>>AccessD mailing list
>>AccessD at databaseadvisors.com
>>http://databaseadvisors.com/mailman/listinfo/accessd
>>Website: http://www.databaseadvisors.com
>>
>
>Respectfully,
>
>Christopher Hawkins
>Managing Developer
>http://www.christopherhawkins.com
>
>
>-- 
>_______________________________________________
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com
>
>-- 
>_______________________________________________
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com
>

Respectfully,

Christopher Hawkins
Managing Developer
http://www.christopherhawkins.com





More information about the AccessD mailing list