Front End Database Growing. 9

[Ed2020]

Several of the systems I support have a strange feature that I cannot fathom:

The systems are split into multiple MDB files - a front end application MDB and a backend (or multiple backend) data MDB.

The front ends contain *no* data at all - they only contain forms, reports, modules and queries. However during use the front end MDB files are steadily growing to ridiculous sizes. A front end of only ~2MB can grow to several hundred MB for no apparant reason.

Compacting the front end recifies the problem temporarily, but the problem persists as users start using the system again.

This problem is causing space problems on our network drives.

I have never experienced this in either Access 97 (a far less buggy product in my opinion) or Access 2002/XP.

Can anyone shed any light on what is causing this?

Purple stars available to anyone who helps!

Ed Metcalfe.

Please do not feed the trolls.....

 

[Escolar]

I have another solution. This works well on a network, but could also be used for a single user.
Basically, you copy a compacted version of the CODE MDB from the network drive to local drive C each time you run the database. Then you run the code from drive C. and the shared files remain on the network.
Since the code is usually small, the copy does not take long.

Here is a fully working script file which tests for a version upgrade and does a copy if one is found.
To solve your bloat problems, you could change the code to enforce a copy every time it is run, instead of on a version change.

-----------------------------------------------------
'* Script : InfoNET_Prod.vbs
'* Purpose: To start InfoNET Local Access Database against INfonet ORACLE tables
'* Usage : Run this script
'* Author : Richard Parkes
'* Created: 18/07/2003
'* Editors: Richard Parkes
'* Amended:

'* Turn off error trapping
On Error Resume Next
'Option Explicit
'window.event.srcelement.style.cursor=3D"wait"


' Define the global variables
Dim WSHShell ' used as the shell object
Dim WSHFileSys ' used as the file system object
Dim Shell ' used as the application object
Dim Net ' used as the network object
Const ForReading = 1, ForWriting = 2, ForAppending = 8
Dim Localdir
Dim Version

' Create a shell object and a file system object
Set WSHShell = CreateObject("WScript.Shell&quot
Set WSHFileSys = CreateObject("Scripting.FileSystemObject&quot
Set Shell = CreateObject("Shell.Application&quot
Set Net = CreateObject("Wscript.Network&quot

SourceDirectory = "Q:\database\Access2000\Infonet"
ApplicName = "InfoProd2k.mdb"
Localdir = "C:\InfoNetLocal"

Version = "V2P61_1" ' We 26/Nov/2003 RVP - Added CrossRef to activity
Changes = " Added Cross/Ref field to Activity. RP 26/11/2003"


Start1 = "About to initialise version "
Wait1 = " - this will take a few minutes then application will start automatically. "



' Check version
If Not WSHFileSys.FileExists(Localdir & "\" & Version & ".TXT&quot Then
WSHFileSys.CreateFolder(Localdir)
WSHFileSys.DeleteFile(Localdir & "\*.txt&quot
Dim OF
Set OF = WSHFileSys.OpenTextFile(Localdir & "\" & Version & ".TXT",ForWriting,True)
OF.Write "CompCert Access " & ApplicName & " Version: " & Version
OF.Close
' delete the old version
WSHFileSys.DeleteFile Localdir & "\" & ApplicName,True
End If

' Make sure the mdb file is there.
If Not WSHFileSys.FileExists(Localdir & "\" & ApplicName) Then
WSHShell.Popup Start1 & ApplicName & " " & Version & Wait1 & Wait2 & VBCRLF & " Changes: " & Changes,,Applicname & " Version Upgrade"
WSHFileSys.CopyFile SourceDirectory & "\" & ApplicName,Localdir & "\" & ApplicName,True
End If

' Run MSACCESS
'

WSHShell.Run Localdir & "\" & ApplicName
-------------------------------------------------------

 

[jryan224]

Note: The "compact on close" is a feature of ACCESS 2000 and is not available in ACCESS97.