Inflated Database

[Jacinda]

Hi, I'm having a problem with my database being inflated after I run updates.

We have a back end database which holds all of the tables and feeds the
front end. The backend is getting inflated after I run updates from the ODBC
tables.

In this backend database we have about 10 ODBC tables linked in, my updates
essentially delete all of the data from my access tables and then appends all
the data from my ODBC tables... we link the frontend to the access tables.
It's almost like we are storing the data twice, but the db doesn't retain the
data from the ODBC right?? it's only linked in??

We have been going fine until recently when all of a sudden the DB is
sitting at 1.99G.

I have compacted and repaired the Db many times, and it will go down only to
be inflated again... any suggestions?
- One other thing... we are using Access 2007 but the db is 2003... does
that have anything to do with it?

 

[John Spencer MVP]

WHEN you delete all the data, the deleted items are still there. The
information is no longer accessible in the program. The space is not
recovered until you compact the database. Most databases work this way for
efficiency. Recovering the now "unused" space with every delete can
significantly slow down the operation of the database.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[John W. Vinson]

Hi, I'm having a problem with my database being inflated after I run updates.

We have a back end database which holds all of the tables and feeds the
front end. The backend is getting inflated after I run updates from the ODBC
tables.

In this backend database we have about 10 ODBC tables linked in, my updates
essentially delete all of the data from my access tables and then appends all
the data from my ODBC tables... we link the frontend to the access tables.
It's almost like we are storing the data twice, but the db doesn't retain the
data from the ODBC right?? it's only linked in??

We have been going fine until recently when all of a sudden the DB is
sitting at 1.99G.

I have compacted and repaired the Db many times, and it will go down only to
be inflated again... any suggestions?
- One other thing... we are using Access 2007 but the db is 2003... does
that have anything to do with it?

A bit more clarification here!

Do you have the same data stored BOTH in ODBC linked tables (on a SQL/Server
database presumably), and *also* in JET/ACE tables in an Access backend? If
so... why?

Are all of the JET/ACE tables in the backend of this type, or are there
Access-only tables or data as well?

What is the nature of these "updates"?

 

[Jacinda]

Yes we have both stored in the same database...

Originally we were linking directly to the ODBC but then we ran into license
issues, and the ODBC would make our front end slow...so we created an
environment where we would take the data in the ODBC and dump it into Access
Tables... then link our front end to the Access Tables.

My thought was that if we were deleting the data, then appending back again
I would not run into space issues because I really was not "adding" any
thing....

So basicly I will need to compact and repair at the end of the day or first
thing in the AM to avoid the inflation?

 

[John W. Vinson]

My thought was that if we were deleting the data, then appending back again
I would not run into space issues because I really was not "adding" any
thing....

A plausible thought, but as John Spencer says, it's incorrect.

So basicly I will need to compact and repair at the end of the day or first
thing in the AM to avoid the inflation?

If you're just bringing over ALL the data daily I'd suggest simply deleting
the backend .mdb file daily. Have a template backend with all your tables
defined, *EMPTY*, and links to the ODBC data; copy the template to the
filename of the backend, and run a series of append queries to populate the
tables. You'ld then want to relink the frontends to this backend.

This has the advantage that you can specify the field types and sizes, define
indexes on your tables, include referential integrity, etc.