File size 17mb - VBA to find out why? (1 Viewer)

[wrightyrx7]

Hi all,

I have a database which i have just moved to SQL sever but still using Access as the front end..

When the data was in a back-end on a shared drive the front-end file size was 3mb.

For some reason now my back-end is SQL server the file size has increased to 17mb.

I am trying to find out which form/report is causing this. Does anyone have any code that will produce the size of each Form/Report in the front end? This way i can find out what is causing the problem.

Thanks in advance.

Chris

 

[Minty]

Have you run a compact and repair on the front end ?

 

[wrightyrx7]

Have you run a compact and repair on the front end ?

Hi Minty,

Yes, i keep an eye on the file size as a run it too. The file size goes to 11mb but when i come out out of the front end it goes back up to 16mb

 

[Minty]

To be fair 17mb is not huge.
Do you have many images embedded in reports or forms? These are not stored very efficiently in Access. You are better off having them linked rather embedded if is possible.

As a exercise make a copy of the front end - remove an number of images and compact and repair and see what the result on the size is.

 

[gemma-the-husky]

It really isn't worth bothering about.
17Mb is really very small

 

[The_Doc_Man]

It might not be a form or report. As I recall, the internals of Forms and Reports don't change when you upconvert a BE file. Their properties don't change at all, and if they are bound to stored queries then their datasource specifications (recordsource, rowsource, etc.) are still just the names of the stored queries. Ditto if the rowsources are simple SQL.

However, if your forms/reports directly open TABLES rather than queries as their recordsources, I'm not going to swear what that does to some internal structures. I have seen some behavior that is less than optimum when doing direct table opens, but if you make a query - even a single-table query - to anything that drives your forms and then open the query, behavior is more consistent.

Another question is whether your DB FE file grows continuously - which you can test by not doing a compact/repair on it for a while and track its size. If the database grows slowly then your problem is common to just about every Access database ever made... "bloat." Databases need temporary space to do things. Some other programs call this "scratchpad" space or "dynamic memory" or "heap space" depending on the exact program. Once this working memory is used, you can't re-use it. You can only release it by compact/repair operations.

 

[wrightyrx7]

It might not be a form or report. As I recall, the internals of Forms and Reports don't change when you upconvert a BE file. Their properties don't change at all, and if they are bound to stored queries then their datasource specifications (recordsource, rowsource, etc.) are still just the names of the stored queries. Ditto if the rowsources are simple SQL.

However, if your forms/reports directly open TABLES rather than queries as their recordsources, I'm not going to swear what that does to some internal structures. I have seen some behavior that is less than optimum when doing direct table opens, but if you make a query - even a single-table query - to anything that drives your forms and then open the query, behavior is more consistent.

Another question is whether your DB FE file grows continuously - which you can test by not doing a compact/repair on it for a while and track its size. If the database grows slowly then your problem is common to just about every Access database ever made... "bloat." Databases need temporary space to do things. Some other programs call this "scratchpad" space or "dynamic memory" or "heap space" depending on the exact program. Once this working memory is used, you can't re-use it. You can only release it by compact/repair operations.

Thank you for your reply.

I have found something really strange. I have built the front end to hide all the objects and menu's. In the Options/Current Database i have the "DISPLAY FORM" set to my main form. When i turn this off to display (none) the file size drops 10mb.