ACCESS FEED: [MS_AccessPros] Re: BE bloat

Jumat, 03 Mei 2013

[MS_AccessPros] Re: BE bloat

Patty

Some of the things to track are:
1. updates/deletes on large number of records
2. creation of temporary tables
3. deletion/inserts of all records in temp tables

All the above force Access into putting all the records in a buffer so a rollback is possible. The buffer does not shrink once the transaction is done. Compacting is necessary.

Also, why an MDE for the back end. There shouldn't be anything in there but tables, and MDEs offer no protection of data.

Regards,
Bill Mosca, Founder - MS_Access_Professionals
http://www.thatlldoit.com
Microsoft Office Access MVP
https://mvp.support.microsoft.com/profile=C4D9F5E7-BB03-4291-B816-64270730881E
My nothing-to-do-with-Access blog
http://wrmosca.wordpress.com

--- In MS_Access_Professionals@yahoogroups.com, patrinod <no_reply@...> wrote:
>
> Thanks, John and Clive
>
> 1) John, there are no memo fields
> 2) Re - isolating what they are doing
> With the function from Clive, I can now keep track of the file
> size and then go back to see what was data was changed when
> the database.
> Thanks
> Patty
>
> --- In MS_Access_Professionals@yahoogroups.com, "Clive" <zctek@> wrote:
> >
> > 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
> > >
> >
>