Prev: Can't get exclusive access
Next: Conditional Format
From: Eratosthenes on 13 Jan 2010 05:06
Hi, sorry if this has been done to death, but database bloating on a
big scale again. I've inherited development of a FrontEnd - Backend
database system, former has 30 odd forms, innumerable queries,
reports. Linked to Backends, uses a mix of DAO updates, DoCmd.RunSQL
and CurrentDb.Execute. Uses Access 2000 file format, windows XP
clients, mostly Novell servers. The 8 (varying by data content)
Backends have just tables, updated overnight by a process that recalls
them, compacts, updates the data & farms them out again. With 100 odd
connections of the FrontEnds the database may grow from 20MB to 400MB
or worse. There are combos, listboxes, temporary SQL bound forms.
Working data is in the front ends. Bad, huh?!

I'm going through it removing name autocorrect, surplus indices,
setting SubDatasheeName to [None], making sure the recordsets are
closed & set to Nothing, putting transactions around multiple inserts,
etc....

Any expert point me in the direction of anything else causing this?
From: Keith Wilby on 13 Jan 2010 05:43
"Eratosthenes" <howard_mckee(a)hotmail.com> wrote in message
news:754cb8f6-2330-4587-b515-197d0a1aa840(a)35g2000yqa.googlegroups.com...
>
> point me in the direction of anything else causing this?

Where's the bloat, front, back or both? If it's FE bloat then it shouldn't
matter if you deliver a new, pristine FE to your users each time they use
it.

If it's BE then I assume that you have tried compacting. You could also try
importing all tables into a new, blank file, although it might be a PITA if
you have lots of relationships set up.

Keith.
www.keithwilby.co.uk

From: hbinc on 13 Jan 2010 06:21
On Jan 13, 11:06 am, Eratosthenes <howard_mc...(a)hotmail.com> wrote:
> Hi, sorry if this has been done to death, but database bloating on a
> big scale again. I've inherited development of a FrontEnd - Backend
> database system, former has 30 odd forms, innumerable queries,
> reports. Linked to Backends, uses a mix of DAO updates, DoCmd.RunSQL
> and CurrentDb.Execute. Uses Access 2000 file format, windows XP
> clients, mostly Novell servers. The 8 (varying by data content)
> Backends have just tables, updated overnight by a process that recalls
> them, compacts, updates the data & farms them out again. With 100 odd
> connections of the FrontEnds the database may grow from 20MB to 400MB
> or worse. There are combos, listboxes, temporary SQL bound forms.
> Working data is in the front ends. Bad, huh?!
>
> I'm going through it removing name autocorrect, surplus indices,
> setting SubDatasheeName to [None], making sure the recordsets are
> closed & set to Nothing, putting transactions around multiple inserts,
> etc....
>
> Any expert point me in the direction of anything else causing this?

Hi Eratosthenes,

I assume you have problems with bloating of the FE.
If it is a bloating BE (with only tables), than that is probably
caused by much add/delete of the records, or much editing, especially
of "large" fields as Memo fields or OLE Object fields.

If it is a bloating FE, it can be caused by a table (see BE). In that
case move that table to some regularly compacted BE.
Otherwise it is most likely caused by any modification of the
definition of Forms, Reports, QueryDefs, Modules.
Many times users can modify Reports, QueryDefs etc. to suit their
personal wishes, but that causes the database to bloat.

So flexibility of the uesr should NOT be build in through modification
of Forms, Reports, QueryDefs, Modules, but should be realized in
another way.
This is possible by chanching the loaded Object, and not the stored
definition! The users of my applications NEVER need to change anything
on any definition, and I have NEVER problems with bloating.

HBInc.

From: Eratosthenes on 13 Jan 2010 06:30
On Jan 13, 10:43 am, "Keith Wilby" <h...(a)there.com> wrote:
> "Eratosthenes" <howard_mc...(a)hotmail.com> wrote in message
>
> news:754cb8f6-2330-4587-b515-197d0a1aa840(a)35g2000yqa.googlegroups.com...
>
>
>
> > point me in the direction of anything else causing this?
>
> Where's the bloat, front, back or both?  If it's FE bloat then it shouldn't
> matter if you deliver a new, pristine FE to your users each time they use
> it.
>
> If it's BE then I assume that you have tried compacting. You could also try
> importing all tables into a new, blank file, although it might be a PITA if
> you have lots of relationships set up.
>
> Keith.www.keithwilby.co.uk

Thanks, Keith. I'm not bothered about FE bloat as the FE is
distributed regularly by the update process, and supplied to the users
by the AutoFEUpdater (excellent app BTW). It's the BE that's the
problem which impacts all users. It gets compacted when recalled to
the update server (usually nightly but that depends on the somewhat
flaky network). I can only modify the BE's programmatically (as the
users monopolise during the day, and aren't keen on losing it even for
half a day), and the building is locked even if I had a twinge of
insomnia! I suppose I could code for making a programmatic copy (copy
table definitions, import data) but it sounds a major hassle & I was
hoping for a quick fix!
 | 
Pages: 1
Prev: Can't get exclusive access
Next: Conditional Format