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