MDE file growing

[jonesda]

Hi,

I have an MS Access database. It contains tables linked to tables on SQL Server. Therefore MS Access is really only acting as a front end to the SQL Server database.

I converted the MS Access database to a .mde file.

I noticed over the past few days that the .mde file is growing in size. I don't understand why this is the case. The data is being stored in SQL Server and the database only contains links to the tables so I wouldn't have expected the size of the .mde file to change.

Does anyone know why the .mde file is growing? Is there anyway I can stop this of growing?

Many Thanks

 

[ghudson]

Access dbs like to bloat up for a number of reasons. Compact the db. Access 2000 and newer have the AutoCompact option.

 

[jonesda]

Thanks Ghudson

Is the Autocompact option to "compact on close". It is the only option I can see.

My .mde file is growing extremely quickly. At the moment it's at 85MB after 2 days. What data is Access storing that's causing it to grow this quickly.

All of the data is stored in SQL Server so access should really only be a front end.

 

[ghudson]

Yes it is the "compact on close" option. Not sure what is up in your db for I have never worked with a SQL Server backend.

Access must be storing the "junk" somewhere.

Does it not decrease in file size after you compact it?

 

[jonesda]

Hi ghudson,

Just managed to compact it yesterday and it did decrease the size if the .mde file from 190MB to 2MB. It is still growing as users use the database but I have set the autocompact option on so should compact itself after the last user logs out.

Can't find anything on web though to explain exactly why the mde file is growing.

Many thanks for your help

 

[GRW]

I might be wrong, only my views, but if you've got linked tables as opposed to an access project, the processing of data is all still happening locally inside of the mdb/mde instead of at the server end.

 

[Robert88]

I agree with GRW

I agree with you GRW.

I recently moved an mde away from a network share to locally in a folder C:\Program Files\[application name]\[mde file]..... so the only table links were to a back end on a network share. It did the world of good to performance.

Instead of accessing the mde over a network share and then the mde further referring to another network shared back end, it now only uses the shared links to the back end and the front end is local.

Maybe if the mde is shared by a lot of users across a network, it creates a lot of references which is causing your mde to bloat as you have indicated it does not decrease until the last user is off. Don't quote me on this but my users are now a lot happier with my new network arrangement and performance of it as a result.

I hope it helps

Regards
Rob