Colby, John
JColby at dispec.com
Thu Aug 26 10:24:53 CDT 2004
The times sound implausible anyway. A 25mb file across a 100 mBIT LAN will
take AT LEAST 100/8 seconds to transfer. In fact it will take longer since
LANS send data packets with header info etc, and there will inevitably be
collisions. In any case a 25mb file simply cannot be transferred across a
100 mbit LAN in less than .25 seconds.
JWC
-----Original Message-----
From: Heenan, Lambert [mailto:Lambert.Heenan at aig.com]
Sent: Thursday, August 26, 2004 11:05 AM
To: 'dwaters at usinternet.com'
Cc: Access-D Email (E-mail)
Subject: RE: [AccessD] Compact BE on Close - An Example
Thanks for the background Dan.
This sounds like another good reason to have the front-end on each user's
workstation, as well as being sure to compact the latest version before
releasing it :-)
But at least the LAN is up to 100 mbit (I don't think you meant gigabits),
if only I could say the same in my shop. We'll be on 10 mbit for ever. One
thing strikes me as odd though and that's the 0.25 second time out for the
server printers. A quarter of a second! Seem mighty brief to me, but then
I'm no network specialist.
Lambert
> -----Original Message-----
> From: Dan Waters [SMTP:dwaters at usinternet.com]
> Sent: Thursday, August 26, 2004 10:09 AM
> To: 'Heenan, Lambert'
> Subject: RE: [AccessD] Compact BE on Close - An Example
>
> 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: Heenan, Lambert [mailto:Lambert.Heenan at AIG.com]
> 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