[AccessD] Access "Bloat" - Basic Questions

Hi Brad,

What I do is this:

1) Set your BE file to Compact on Close.

2) Write a small Access file (named CompactBEXXX.mdb) which has a single
purpose.  When it is opened, an AutoExec macro runs code which opens and
closes your CompactBEXXX.mdb file.  When your BE closes it will compact.

3) Create a task in Task Manager to open your CompactBEXXX.mdb file at a
time when no one will be using it.

4) You may need to use your username/password to get the task to work.  If
you do, and you have a periodic requirement to change your password,
remember to go back into the task and reset the task with your new password.

I've been using this at all my customers for a few years, and it works well.

Here is the code.  You'll need to write an AutoExec macro which will run the
function CompactBE().

Good Luck!
Dan

'----------------------------------------

Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Public Function CompactBE()
On Error GoTo EH

    Dim stgPathBEFile As String
    Dim stgPathBELDB As String
    Dim appAccess As Access.Application
    Dim fso As FileSystemObject
    Dim stg As String
    Dim rst As DAO.Recordset

'    Stop
    
    stg = "SELECT BEFullPath FROM tblBEFullPath"
    Set rst = DBEngine(0)(0).OpenRecordset(stg, dbOpenSnapshot)
    stgPathBEFile = rst("BEFullPath")
    rst.Close
    Set rst = Nothing
    
    Set fso = CreateObject("Scripting.FileSystemObject")
    
    '-- If the BE file is in use then the BE can't be compacted
    stgPathBELDB = Replace(stgPathBEFile, "mdb", "ldb")
    If fso.FileExists(stgPathBELDB) Then
        Access.Application.Quit acQuitSaveNone
        Exit Function
    End If

    Set appAccess = New Access.Application
    
    '-- Open the BE - wait for 5 seconds
    appAccess.OpenCurrentDatabase stgPathBEFile, False
    
    Sleep 5000
    
    '-- When the BE closes it will auto-compact.
    appAccess.CloseCurrentDatabase
    
    Sleep 5000
            
    '-- Close this db in 5 seconds
    appAccess.Quit acQuitSaveNone
    Set appAccess = Nothing
    
    DoEvents
    
    Access.Application.Quit acQuitSaveNone

    Exit Function
    
EH:
    Access.Application.Quit acQuitSaveNone
    
End Function

'----------------------------------------

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Brad Marks
Sent: Tuesday, September 20, 2011 12:58 PM
To: Access Developers discussion and problem solving
Subject: [AccessD] Access "Bloat" - Basic Questions

My background is in the mainframe world where we never had to be concerned
about bloat.

Now I work in the Microsoft Access world and it appears that I have had my
first encounter with significant bloating.

We have an Access 2007 application that obtains data from several SQL-Server
tables (via ODBC) and from two Excel files.

This application creates a number of reports.  One of the reports needs data
from 2 SQL-Server tables and 2 Excel files.  Because of the complexity of
the data, we cannot simply use queries to create the report, but we have had
to resort to an intermediate Access table that is updated with VBA code via
Record-Set processing.  Once the data is processed and stored in this
intermediate table, a query is used to pull this data from the table for use
by the report. 

The report in question can be run for any desired date range.  If the date
range is small, we see little increase in the size of the accdb file.
However, if the date range selected is large (like a full year's worth of
data), the accdb file grows from 15 MB to 85MB.  I believe that this large
increase in size is primarily caused by the Access table that is used for
the intermediate processing of the data.

In the mainframe world, we used "temporary work files" for such intermediate
processing.  Is there something similar available in Access 2007?

One idea that I have considered is to use a delete query to clear out all of
the records in the intermediate table after the report is generated and then
add a "Compact on close" option to the application.

Again, I am new to the world of Access bloat and would like to better
understand how others handle this issue.

Thanks,
Brad

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com