Dan Waters
dwaters at usinternet.com
Sat Aug 28 08:42:43 CDT 2004
Lambert,
The network was in the process of being slowly upgraded to modern switches
(from 10 gbit to 100 gbit). The 80 Mb file was being sent as a single
piece, not broken into separate pieces. As a result, some of the printers
were dropping off-line. I was told that if the printers could not make
contact with the network within 0.25 seconds, then the printer would drop
off-line automatically. The 80 Mb file would take longer than that to get
across the network.
Actually - my memory has finally come back to me. It was actually a FE that
had not been decompiled and recompiled that had grown to 80 Mb, and was
later reduced to 20 Mb. 20 Mb did not cause the printers to drop off-line.
This was the story I got, and I believe that the IT folks who told me this
also believed it. Does this sound plausible?
And you're right - this was something of an effort to lay the problems at
the door of Access, when it was really the IT Access developer who had
forgotten to decompile and recompile.
Thanks,
Dan
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Heenan, Lambert
Sent: Thursday, August 26, 2004 8:51 AM
To: 'Access Developers discussion and problem solving'; 'Dan Waters'
Subject: RE: [AccessD] Compact BE on Close - An Example
Dan,
I'm curious to know what "network problems" were laid at the door of Access
just because the BE grew to 80 Mb (not a particularly huge file).
Lambert
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [SMTP:accessd-bounces at databaseadvisors.com] On Behalf Of Dan Waters
> Sent: Thursday, August 26, 2004 8:30 AM
> To: 'Access Developers discussion and problem solving'
> Subject: RE: [AccessD] Compact BE on Close - An Example
>
> 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
--
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com