Cant compact bloated database

[treva31]

Hi,

I have an Access backend database that has bloated up to over 400mb.

When I do the Compact & Repair it makes almost no difference.
If I ZIP the database it shrinks down to 32mb.
If I import all the tables to a new database it is 70mb.

Is there another way I can compact it or avoid it bloating back up?
I dont want to keep moving things to a new db every couple of weeks.

Every day it imports text files into its tables using a macro (emptying the tables first).
It also uses lots of pivot tables (in the frontend).

Any ideas?

(using Access 2003)

 

[PaulO]

Hi

Do the text files (presumably they are large) actually need to be imported or can you instead make them linked tables and run your queries and macros off them?

These would sure keep the file size down.

 

[CBrighton]

How many changes have been made to the VBA since it was created?

Compact & repair does not decompile & recompile the VBA.

I created a shortcut to

"C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE" /decompile

so I can recompile after major changes.

 

[treva31]

Hi

Do the text files (presumably they are large) actually need to be imported or can you instead make them linked tables and run your queries and macros off them?

These would sure keep the file size down.

No the files are not that large, 12mb in total.
The database at 60mb includes all of the data from them.

I cant use linked files as I need multiple simultaneous users on the database.

 

[treva31]

How many changes have been made to the VBA since it was created?

Compact & repair does not decompile & recompile the VBA.

I created a shortcut to so I can recompile after major changes.

Unfortunatley that didnt work.

There is very little vba, and none in the backend whch is the part that is bloated.

 

[Galaxiom]

What version of Access is the backend? I have seen reports of bloating problems with accdb files (Access 2007+) mentioned on this forum.

If you are using accdb for the backend I would suggest you try converting it to mdb. The front end can stay as accdb as it backwards compatible with older format backends.

Actually I have not seen anything in accdb that makes me want to change to them even for a front end. Indeed there are good reasons to stay with mdb.

 

[treva31]

What version of Access is the backend? I have seen reports of bloating problems with accdb files (Access 2007+) mentioned on this forum.

If you are using accdb for the backend I would suggest you try converting it to mdb. The front end can stay as accdb as it backwards compatible with older format backends.

Actually I have not seen anything in accdb that makes me want to change to them even for a front end. Indeed there are good reasons to stay with mdb.

Sorry I should have mentioned that in the original post.
I am using 2003 and .mdb for frontend and backend.

Maybe I should change the way it imports?
Currently it uses "TransferText" in a Macro (not vba) which refer to saved "Import Specifications".

 

[PaulO]

Are you running the compact & repair utility from the front end file, perhaps, as this would perhaps explain why the backend file doesn't reduce in size.

 

[treva31]

Are you running the compact & repair utility from the front end file, perhaps, as this would perhaps explain why the backend file doesn't reduce in size.

Yea I have been trying to compact the backend.


I just moved all the tables to a new database, 70mb.
Did a single import and its at 200mb already!

 

[PaulO]

You might also want to check that all the field sizes and types are just right/appropriate for the data being held

 

[CBrighton]

Yea I have been trying to compact the backend.


I just moved all the tables to a new database, 70mb.
Did a single import and its at 200mb already!

In your first post you said the import macro deletes the current data and then imports new data.

It will always bloat then. Access does not free up the space which was used by the deleted records. The only way to counter it would be to compact & repair immediately after each import.

 

[boblarson]

You might also want to check that all the field sizes and types are just right/appropriate for the data being held

That doesn't affect Access like certain datatypes in SQL Server.

 

[CBrighton]

A few memo fields in a table with a lot of records would still be worth looking at.

 

[AndyGee]

Did you ever resolve this problem?

Some years ago I encountered a similar issue and could not get to the bottom of it. I eventually worked around the problem by keeping a copy of the tables that I imported the data to and then when it was re-load time I did the following either in a macro or VBA, can't remember:
- Delete import tables
- Repaired mdb
- Compacted mdb
- Copied new tables into place
- Imported data
There shouldn't be any need to compact after this as the data should be going into "empty vessels".
I often suspected that the bloating was caused by the index information created when loading data into tables that had a number of indexed columns in them.

It seemed to work OK for me, but that was on Access97.

Best wishes,
Andy

 

[treva31]

Did you ever resolve this problem?

Some years ago I encountered a similar issue and could not get to the bottom of it. I eventually worked around the problem by keeping a copy of the tables that I imported the data to and then when it was re-load time I did the following either in a macro or VBA, can't remember:
- Delete import tables
- Repaired mdb
- Compacted mdb
- Copied new tables into place
- Imported data
There shouldn't be any need to compact after this as the data should be going into "empty vessels".
I often suspected that the bloating was caused by the index information created when loading data into tables that had a number of indexed columns in them.

It seemed to work OK for me, but that was on Access97.

Best wishes,
Andy

No I have not resolved the issue.
The database is currently around 200mb, which in Microsoft terms is not an unusual amount of bloat from 70mb

I would agree that deleting the tables, then compacting, then importing into new tables would probably solve the issue but I havent tried it yet.

 

[Galaxiom]

This situation clearly demonstrates a very good reason why temporary tables should never be written into the Front End or the Back End.

Such tables should go into a separate database. This "Side End" can be discarded and regenerated as required.

Search the Forum for Side End if you would like to read the discussion of this concept and its implimentation. As I said in those discussions, writing temporary data to the Front End is like writing a shopping list in the margins of a reference book.

It bewilders me that the practice of writing temporary data inside the main databases is apparently so widespread.