Sabtu, 04 Mei 2013

[MS_AccessPros] Re: BE bloat

 

Bill ...

Thanks for your input but
1) there are no updates/deletes on large number of records
2+3) there are no temporary tables
The BE is a MDB not an MDE and only contains tables.

Patty

--- In MS_Access_Professionals@yahoogroups.com, "Bill Mosca" <wrmosca@...> wrote:
>
> 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
> > > >
> > >
> >
>

__._,_.___
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (6)
Recent Activity:
MARKETPLACE


.

__,_._,___

Tidak ada komentar:

Posting Komentar