[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