ACCESS FEED: Re: [MS_AccessPros] BE bloat

Kamis, 02 Mei 2013

Re: [MS_AccessPros] BE bloat

Hi Patty,

You may be able to get an idea of when it is happening
by using the Timer to call a routine that logs the
FileSize of your Db into a table or a separate file.

You could also note which users are using the Db at
the time. (I don't have the code for that.)

Here is a small routine that could get the FileSize for you.
It could run in the BackEnd or in a separate DB.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Function GetFileSize(pstrFilename As String) As Variant
Dim fileData As WIN32_FIND_DATA
Dim lngResult As Long
Dim lngRC As Long
On Error GoTo GetFileSizeErr
lngResult = FindFirstFile(pstrFilename, fileData.dwFileAttributes)
If lngResult = -1 Then
' Disable the two MsgBoxes or it could hang the Db.
'MsgBox "Can't find file " & pstrFilename, , "GetFileSize"
GetFileSize = -1
Exit Function
End If
lngRC = FindClose(lngResult)
lngResult = fileData.nFileSizeLow
GetFileSize = lngResult
GetFileSizeExit:
Exit Function
GetFileSizeErr:
' Disable the MsgBox or it could hang the Db.
' Maybe log the error to a Table or a file.
' MsgBox "Err =" & Err & " " & Err.Description, , "GetFileSize"
Resume GetFileSizeExit
End Function
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Hope that helps.

Regards, Clive.

--- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@...> wrote:
>
> Patty-
>
> Can you isolate what users are doing when the file bloats up? Do the tables
> have Memo fields?
>
> John Viescas, Author
> Microsoft Access 2010 Inside Out
> Microsoft Access 2007 Inside Out
> Microsoft Access 2003 Inside Out
> Building Microsoft Access Applications
> SQL Queries for Mere Mortals
> http://www.viescas.com/
> (Paris, France)
>
>
>
> -----Original Message-----
> From: MS_Access_Professionals@yahoogroups.com
> [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of patrinod
> Sent: Thursday, May 02, 2013 3:41 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: [MS_AccessPros] BE bloat
>
> Problem: Database bloat
> Split Setup: Access 2003 BE on a server and
> 2007 FE on each user's local drive. The FE is an mde file.
>
> One of the BE's frequently (once a week) bloats from 1.5 meg to 15 - 20 meg
> overnight.
> Compact and repair immediately brings the BE back to 1.5 meg.
>
> The FE does not create any temporary files. There are no pictures stored in
> the BE.
>
> The FE is pretty basic - forms to determine what is available and the
> ability to write / modify a few production records. There is not a lot of
> activity in this database! I've ensured the I close objects (rst.close,
> db.close, set rst nothing, set db nothing)
>
> 1) Does anyone have any ideas what I can do to find out why this BE is
> bloated.
>
> There are at least another dozen FE databases similar to this one (not
> linked to the BE above) that are not creating a bloat problem.
>
> Thanks,
> Patty
>
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>