Access Database Size Drastic Increase.

[AngelSpeaks]

I'm not sure how it happened. I made a few changes to my database. It was about 14,000kb. Now its 650,000kb! I have it set to compact on close and lately, I've been getting a message at the right hand corner 'Compacting, Press Hold, etc' which I didn't receive before. I have not added any large tables or reports.

I did recently add a reference (not sure what it was), could that be it?

Thanks

 

[AngelSpeaks]

Here are the references. The last thing I was looking into was trying to automatically relink (which I haven't done yet) and to try to include a PDF.

 

[AngelSpeaks]

Well I checked the references with the backup, that's not it.

 

[oleronesoftwares]

Here are the references. The last thing I was looking into was trying to automatically relink (which I haven't done yet) and to try to include a PDF

Try removing the reference to see if same thing occurs, also if you have an old backup check the database size to compare with your current database size.

Well I checked the references with the backup, that's not it.

is the reference in the back up copy, and is the size okay in the back up copy?

 

[CJ_London]

I would be very surprised if it was a reference. More likely to be table data or perhaps a corruption.

Is the db split? if so is it the front or backend that is the problem?
Do you store attachments?

 

[AngelSpeaks]

Try removing the reference to see if same thing occurs, also if you have an old backup check the database size to compare with your current database size.


is the reference in the back up copy, and is the size okay in the back up copy?

References the same and size is good on backup

 

[AngelSpeaks]

I would be very surprised if it was a reference. More likely to be table data or perhaps a corruption.

Is the db split? if so is it the front or backend that is the problem?
Do you store attachments?

The problem is the front end. I did not store any attachments.

 

[Pat Hartman]

The biggest cause of db bloat these days is make table queries or permanent tables where you delete/append the entire contents of the tables. Access cannot recover the scrap space without a compact.

PS, there are better options that don't cause bloat and compacting when you close is generally a bad idea.

If you work with a lot of temporary data, you might consider making a template BE to hold only the temporary data. Create a new, empty database. Define the tables/keys/relationships for the temporary data. Compact and Close. Copy the template to the folder where your FE runs. Open the FE and link to the empty tables in the second BE. Create a procedure that fills the empty tables rather than using the other techniques. At the beginning of the procedure, copy the empty template to the current folder to replace the existing one. As long as no table linked to the template is open, you won't have a problem.

 

[AngelSpeaks]

Pat, that makes sense. I have one temporary table where i run a delete query and then append rows to it. The table has almost 100 columns. I've been doing extensive testing with this table. I will remove the compact on close. Thanks

 

[CJ_London]

assuming the table is in your front end, trying creating a temporary db and putting it there. You can have a 'template' table in your front end so instead of running a delete query, you create a temporary db and copy the table across and link to it. When done you delete the temporary db. A variation on Pat's suggestion

 

[AngelSpeaks]

I've removed the table that is causing the bloat, I did a compact, but it's still the same size

 

[GPGeorge]

I've removed the table that is causing the bloat, I did a compact, but it's still the same size

You could try creating a new, blank accdb and importing all of the objects -- or should I say objects you need/want into it to see if that leaves behind the bloat.

 

[AngelSpeaks]

You could try creating a new, blank accdb and importing all of the objects -- or should I say objects you need/want into it to see if that leaves behind the bloat.

I did that and the bloat went away.

 

[AngelSpeaks]

Update. To get around the bloat, i recreated the database by copying all the objects. This will buy me some time to do the suggestions above. Client would have to be running a weekly procedure about 50 times in one session to get the bloat i got so i have time

Thanks for the help.

 

[NauticalGent]

Have you considered using a "Side-End" db? If you do a search on this Forum you will find many threads about it. I have only used it once, when I had to receive raw data on a daily basis from MASSIVE Excel spreadsheets. It might be a little more than what you are looking for but it is a handy tool for the toolbox.

 

[GPGeorge]

Update. To get around the bloat, i recreated the database by copying all the objects. This will buy me some time to do the suggestions above. Client would have to be running a weekly procedure about 50 times in one session to get the bloat i got so i have time

Thanks for the help.

 

[GPGeorge]

I'm glad to hear that helped. It's sort of a last straw measure, but when it works, it's cool.

I have a demo relational database application on my website which includes the use of what is often called a "Side End". That is, as others have suggested, a temporary accdb that is created for temp tables ONLY at the start of each session and deleted when the session ends. The purpose of the demo was actually something different, but you can extract the side end stuff if you want to try it on a development copy of your own application.

 

[The_Doc_Man]

The usual causes of bloat were listed by Pat Hartman, but she left out one more cause of bloat. As it happens, updating a table will also cause bloat because of the possibility that you might need to do a rollback. If you are using CurrentDB.Execute on an SQL update, the fact that a rollback can occur after a failure means that briefly you have two copies of the table - the original copy and the updated copy - and that as a result, when the updated records replace the originals, they get dropped into "bloat-space" which also where deleted records go. INSERT INTO does not do as much to contribute to bloat as deletes and updates. However, a repeated cycle of delete/insert to the same table is a potential invitation to bloat.

Your design is your design and I will not blindly criticize it. If you really think you need 100 fields in a table, then perhaps you need it. Having said that, we generally get VERY suspicious of tables that wide. One question I have to ask is whether you have applied normalization techniques to this table. It is possible that your extra-wide table can be split using normalization so that you have taller, narrower tables that don't carry quite so much baggage when you delete/update records within them.

 

[AngelSpeaks]

Side end DB? I will look into it. Doc, the almost 100 columns (especially several columns using duplicate aliases) certainly would never be my idea. You can thank the State of Illinois for that mess. The table is based on the exact requirements of the csv upload they require.

 

[The_Doc_Man]

Another example of governmental bloat - just not monetary this time. But it probably represents the output of some old mainframe as an intermediate transfer file. Saw that a LOT in the U.S. Navy where I was a contractor.