Small problems becoming much bigger!

[marcboorman]

Okay, brief overview.

We currently have 2 MDE front ends to and oracle box. Database is about 25Gigs and have been minimising the traffic by optimising the queries. However we have always noticed some very strange problems now that our user base is about 150. They seem to become more apparent when the MDE files reach a certain size. I have implimented an autoshut down and inactivity shut down protocol into the access front ends but I would really like to know why the MDE files are getting so large. They are normally about 5MB but get to in exccess of 130MB. The autoshut down is forcing everyone out which compacts it overnight!

When these databases reach critical mass, peoples data seems to be getting mixed up and one persons form is mixing data up with anothers!!!!!! What is casuing this? We are using the front ends over citrix too, is this the root of my problems. I am going to be copying the MDE file to each citrix box to speed it up and reduce the number of users on the mde. Ideally I would prefer an mde for each user but that is impractical.

Really my question is this, Why does an MDE file with no tables except linked via ODBC increase in size so dramtically? Is there a leak or somthing somewhere?

Thankyou in anticipation of any replies!

Marc

 

[Ron_dK]

Might this be of any help ?

http://www.granite.ab.ca/access/decompile.htm

 

[marcboorman]

thanks for the reply

Not sure if I mentioned but the autoshut down procedures that I have included force the MDE file to compact back to normal size, But this only happens when every single user has logged out. It seems that because there is always at least one user logged in it wont ever compact. This is fixed with the autoshutdown, however I would really like to know what is casuing the MDE to become so large, this may explaing the errors with mixing up data!

The autoshutdown is due to go live in mid november too, just dont like fixing somthing that I dont understand!

thanks again

marc

 

[Ron_dK]

Marc,
It's not so much compacting the MDE, but compiling the VBA codes.
If not compiled, the DB may bloat and expand to enornous size.
So, you might check going into the visual basic editor, compile the
dbase and verify if the size has decreased.

Hth

 

[marcboorman]

decompiled and recomplied

okay, did what the link described and it has increased the size of my mdb file to 6.8MB. This isnt so bad but will this fix the MDE file from bloating?

thanks

marc

 

[Pat Hartman]

.mde's do not include source code so they never get "uncompiled". Bloat is caused by creating and deleting temp tables or inserting and deleting rows. Another big cause is SQL strings created in code rather than using querydef's. Unlike saved querydefs which are evaluated when they are saved, the SQL strings need to be evaluated each time they are executed. This process involves syntax checking and calculating the most efficient method of solving the query.