[AccessD] Back end bloat

Asger Blond ab-mi at post3.tele.dk
Tue Dec 16 06:07:09 CST 2008


Knew about the hidden querydefs behind forms and reports, but didn't know
about hidden system tables not compacting. Thanks Charlotte.

Asger

-----Oprindelig meddelelse-----
Fra: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] På vegne af Charlotte Foust
Sendt: 16. december 2008 02:36
Til: Access Developers discussion and problem solving
Emne: Re: [AccessD] Back end bloat

Starting in Access 2000, there's a lot going on under the hood, and it's
gotten more so with each new release.  Even in earlier versions, reports
created their own query version of their recordsouces totally unbeknownst to
the developer or user.  That was the reason that sometimes a big query would
run fine as a query but the whole thing would fall over or return a "query
too complex" when you tried to hook it to a report.

There are hidden system tables in version of Access from at least 2002 up
and at least some of them don't compact, they only grow.  That's the main
reason many of us stuck with the 2000 file format for data, to avoid those
helpful bloat-builders.  If you go into the backend and build a query and
run it, even if you don't save it, you've just generated a lot of hidden
"stuff" that bloats the database.

I rarely even open Access any more except to look at one of our alternate
format back ends, so those who are more current should answer your question.


Charlotte Foust 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Asger Blond
Sent: Monday, December 15, 2008 5:04 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Back end bloat

Hi Charlotte,

"The later version of Access create their own hidden queries for things and
that's where a lot of bloat comes from as well."

Interesting point, can you elaborate that?

Asger

-----Oprindelig meddelelse-----
Fra: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] På vegne af Charlotte Foust
Sendt: 16. december 2008 00:59
Til: Access Developers discussion and problem solving
Emne: Re: [AccessD] Back end bloat

So the queries are being created in the front end and executed against the
back end?  That shouldn't cause back end bloat, but if you're running
queries that actually execute in the back end, then yes it will bloat the
database. I'm not sure what you mean about adding and removing data in a
temporary backend.  You still have to pass those changes to the backend and
any changes against backend data will cause at least some bloat.  The later
version of Access create their own hidden queries for things and that's
where a lot of bloat comes from as well.  Stored queries are generally more
efficient in Access because they can be optimized by the query engine while
on the fly queries take a hit there.

Charlotte Foust


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

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






More information about the AccessD mailing list