MA Access front-end bloating

We have MS Access 2010 application, with front-end that bloats over the time.
Historically front-end is shared by multiple users.
Could that be the reason for bloating or not?
Could the bloating be stopped while keeping shared front-end?

Any proofs/links to some authoritative sources in support of either answer would be highly appreciated.
Thanks a lot
ForresterCAAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Bloating can occur for a number of reasons.   Anything from normal operations (deleted record space is not reclaimed) to bugs in Access.

One thing is for sure; users should not be sharing front ends.

Jim.
0
ste5anSenior DeveloperCommented:
As Jim already wrote:

Each user should have its own copy of the front-end on the local client. Then it is safe to use the Compact on Close option.

A non-shared, local front-end should also reduce the corruption of the front-end, when you see this often.
0
ForresterCAAuthor Commented:
Jim,
Personally I think the same.
Just need to show significant proofs to our decision-makers, because separate back-ends will require major infrastructure change.
For now my bosses are saying - show us where MS is telling that users should not be sharing FE, while technically MS allows it..
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

PatHartmanCommented:
separate back-ends will require major infrastructure change.
You would NEVER use separate back ends.  All users have their own personal copy of the FE but link to a shared copy of the BE.  That is how data is shared.

MS allows it because technically, "Access" doesn't know the difference between a FE and a BE.  They are both databases.  It is only the way we use them that differs.  "Access" can be used by a single person as a monolithic application to hold both program objects and data and that is typically how power users use it.  Once you determine that more than one user needs to use the app at the same time, you must split the app into a FE and BE.  That way, the data is separate and can be stored on a network drive that all users link to.  There is no advantage to sharing a FE and there are downsides.  Corruption is the first problem.  If five users have the same physical FE open and one of them has to force a reboot then that can corrupt the FE and cause a problem for the other users.  It won't always happen but it happens enough that you should actively prevent it and since doing so is actually quite simple, we do it.

Separate FE's does not require major infrastructure changes.  It can be done with a simple batch file.  The users have a shortcut on their desktops to a batch file stored on the server.  The .bat file copies the master copy of he FE from the master directory on the server to the local c: drive and then opens it.  In addition to satisfying the criteria of each user having his own FE, this also eliminates totally the need to compact the FE since each user always gets a fresh copy whenever he opens the app.  Of course if you still have bloat issues you have a much bigger problem with your design.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<For now my bosses are saying - show us where MS is telling that users should not be sharing FE, while technically MS allows it.. >>

 Problem is, Microsoft doesn't produce much documentation anymore<g> But:

https://support.office.com/en-us/article/Split-an-Access-database-3015ad18-a3a1-4e9c-a7f3-51b1d73498cc

 also, here's the way to attack the argument:

1. It's simple to do as Pat pointed out.
2. They need Access in one form or another anyway.

3. Think of any other application; Sage Fixed Assets, SAP, etc They *all* require you to install software on the client side.

 A "front end" in Access is just that, "the application".    What I've found often works is to pick any app, such as bar Tender for Windows and say "OK, let's un-install it from everyone's desktop and install it on the server where it can be shared."

  Guess what kind of response you'd get?   Servers are meant to share data, nothing more (unless it's a Terminal Services server of course).

  You won't find any app out there that says it's better to share then not.

4. In doing so you:

a. Reduce the chance of corruption
b. Can use the compact on close as has been pointed out.
c. can simplify the development process in regards to temp tables (users can't impact one another if the temp tables are in the FE's).
d. Can allow for the creation of temp items (ie. reports) in the FE without conflict.

e. and most importantly, with a shared FE, all Forms, report, VBA project etc. is being pulled over the network every time a user goes to do something (that's overly simplistic, but the idea is, you are pulling more than data).   So by splitting, you avoid a lot of network overhead.

  Splitting has been the SOP with every Access developer and Microsoft since day one, even to the point that Microsoft too the time to build a splitting Wizard into the product.

  I have to say, I never understand this reluctance to split.  There are so many pro's to it, it is a no brainer.  

 Also the fact that you would never dream of doing this with any other windows app.  But for some reason, with Access a lot of people think this is a good idea.

Jim.
1
ForresterCAAuthor Commented:
Pat,
I'm sorry for the typo. Of course I meant "separate front-ends" for each user.
Infrastructure is beyond my competence and this is what I've been told. They do not want to make changes there, unless I will be able to prove that the bloating of FE is caused by shared FE.

Jim,
Your link is about splitting database into front-end and back-end. This is already done.
The problem is that all users are using the same front-end file.
If that matters, both FE and BE are located on Citrix/Terminal Services server, so no network traffic issue here.

BTW 4d is a real pain, especially in part of not being able to use temporary "per user" tables in a simple way. Same challenge as it used to be with .adp

Thank you guys for your input, I really appreciate it!

However, basically I was hoping to find any references, confirming that bloating of FE is related to the fact that the file is shared in multi-user environment.
This could help me to persuade my bosses to switch to the correct database layout
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<However, basically I was hoping to find any references, confirming that bloating of FE is related to the fact that the file is shared in multi-user environment.>>

 You won't find any.

Jim.
0
PatHartmanCommented:
Bloating is caused by certain actions such as using make-tables.  if one user is making tables, that's one thing.  If 10 users are making tables in the same FE, that is quite another.  What are you doing in the app to cause the bloating?  Are you editing objects and saving them?  making temp tables?  adding/deleting rows from local tables?

Terminal Services and Citrix are no different from a LAN setup.  The FE MUST BE COPIED TO EACH USERS PERSONAL DRIVE SPACE.  Do NOT take no for an answer.  This is not a difficult thing to do.  This can be done using the same batch file setup.  The difference is that you have to use tokens to determine the name of the individual's "desktop" folder.  I don't have one handy and we no longer write batch files on a regular basis so I need to look up the syntax every time I write one.  I'll look for one but hopefully someone can post an example that uses tokens to obtain the user's directory.
0
ForresterCAAuthor Commented:
We do not create tables, do not modify objects, and do not edit local tables, and do not use temp tables.
And all recordsets are destroyed correctly.

In my another project I use the following shortcut to start application:
"C:\Program Files (x86)\Microsoft Office\Office14\MSACCESS.EXE" /runtime "C:\Users\%USERNAME%\FolderName\AppName.accde"

and it works fine.
for copying file to multiple locations without bat files there is a really nice small app CopyToLocs from http://www.codesingh.com/2009/07/copytolocs-copy-files-or-folders-to.html

so I agree that this is really simple.  

my employer just do not want to take risk and make changes in what is working fine for him for a rather long time.
and I'm not able to provide enough evidence that weird things happen because of shared FE..
0
PatHartmanCommented:
ANYTHING that makes Access acquire workspace introduces bloat.  That would include opening  recordsets.  the ONLY way to recover acquired workspace is a compact.  Does the database bloat when a single individual uses it all day?  How many concurrent users are there?  How many days does it take to get to critical mass?

There is nothing inherently different about multiple users having the FE open simultaneously.  The problem is volume.  One person causes a small amount of bloat.  Many people working all day cause a lot of bloat.  Remember, as long as any individual has the FE open, it cannot be compacted.  If the file is not compacted every day, then today's bloat gets added to yesterday's and all the previous yesterday's.

what is working fine for him for a rather long time.
If it was working for a long time, what has changed?  Was a change made to the app to increase the operations that cause bloat?
0
ForresterCAAuthor Commented:
well, it was always bloating
just more and more users online, currently up to 40-45 at a time.
and yes, the application grows in features, objects.
now in 1 day it can reach 2gb, which becomes critical.
of course we compact it overnight

and no, it does not bloat if single user
but I'm asked questions, if there are any magic settings in Access/SQL/ODBC/Windows etc which could resolve the issue for multiple users.
did not manage to find one yet..
0
ste5anSenior DeveloperCommented:
0
Gustav BrockCIOCommented:
In fact, your boss is turning the question upside down. Nothing speaks for having a shared frontend; on the contrary, it prohibits update of the frontend while users are logged on, and if one user by accident brings the frontend down, all users are left in the dark.

However, as you operate in a Citrix environment, you must understand his/her concern. No one changes anything except for a good reason, and if a change is needed, you want some kind of proof that it will work and not influence other operations and not add a new burden on the system people.
I've been through this for a client, thus, this link may interest you:

Access Citrix Installation

It contains a link to another question on the topic with additional info.

/gustav
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
On the bloat, there are multiple reasons why that might occur.

1. Normal usage - Space not being reclaimed from record deletes or storing OLE objects in the DB

2. Operations that occur because of multiple users in the DB.

3. bugs in Access and/or JET.

The later two you will not find documented much anywhere (in the past, it's usually after the fix comes out that you'll find them documented), but they do occur.

 I've got a client right now that I'm trying to track down what is causing bloat in a DB.  It can run for weeks without issue and remain at 40MB.   But something they do causes it to suddenly bloat up to 400+ MB.   A compact and repair brings it right down to 40.    

 All that aside, I don't understand the reluctance to do something....you say it's bloating up close to the limit of 2GB.   Whatever the reason, you can't allow that to happen or your going to have a lot more problems then just an abnormally large DB.

Jim.
0
ForresterCAAuthor Commented:
ste5an
these links are about splitting database into FE and BE. as I mentioned, our database is already split.

Gustav
there is no problem with updating FE within working hours.
we just publish new version under different file name. then change path in Citrix shortcut to the new file, and ask users to re-login at their convenience (exactly the same as non-sharing users would do)

also, crashing FE instance session does not destroy the file itself (at least I do not remember such cases for the last 5 years)

this link is about using %USERNAME%, same as in my post above. so we know how to do this. I just need to provide enough evidence that new layout will resolve bloating issue.

Jim
yeah, its growing.. that's why I'm here ;)
they want me to find solution without changing layout, and that was my original question..

btw, from my experience, BE bloating could also happen:
- because of error which does not allow to close recordsets (especially ADO)
- if users are saving images/pdf into the table's OLE object field, instead of saving file path, or at least OLE link (compact won't help).
- using delete/append or records instead of update. when users at some point decide to clean/update historical data, this one causes exactly sudden bloat.

UPD:
yesterday we opened case with Microsoft. they promised to look into the issue and provide official response

Thanks again, guys, for trying to help!
0
ste5anSenior DeveloperCommented:
Split the database file into a front-end application database, consisting of the Access objects such as queries, forms, and reports, and a back-end data database, consisting of the tables. The back-end database is saved to the shared file directory and the front-end database is saved on each client computer.
(MSDN)

So, what else do you want..?

When you've done this, then the FE always gets the Compact on Close set. Bye, bye bloat.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<this link is about using %USERNAME%, same as in my post above. so we know how to do this. I just need to provide enough evidence that new layout will resolve bloating issue.>>

<<When you've done this, then the FE always gets the Compact on Close set. Bye, bye bloat. >>

  No one can say directly that sharing is what is causing your bloating issue, except possibly Microsoft.  As has been pointed out, there are just too many un-documented things that can cause bloat.   But not sharing:

a. Might fix it.
b. Will certainly manage the problem better allowing for the use of compact on close.

 So that's as much of an answer as your going to get here.

<<UPD:
yesterday we opened case with Microsoft. they promised to look into the issue and provide official response>>

 My only comment on that is don't hold your breadth.   Access development on the desktop client is basically dead at this point.   Since it's A2010, unless whatever bug carries through to A2013 and it has a critical impact on data, you won't see a fix for it.   I think it's only once that Microsoft has gone back to a previous release with a hot patch (combo box bug that could potentially destroy data).

 You might get a workaround at best or more likely be told "that's the way it is".

 Because of that, I think your best course of action is to not share the FE.   Set it up as a test for a few users at the very least.   It might just "solve" the problem and it will certainly allow you to mange it better with compact on close.

Jim.
1
ForresterCAAuthor Commented:
ste5an
bye bye bloat will happen only after the last user have closed shared front-end, while currently we are close to hit 2gb limit within a day,
also, compact occasionally damages/renames/kills file, after which all users will be immediately affected.
after couple of such cases, we are no longer doing compact on close.

Jim
I agree with you about Access development situation, and I was not impressed by MS representative and his initial comments ("MS provides all the important information in error messages", etc..).
But we decided to give it a try. Hoping they will find somebody from Access development team (if there is still any..)
0
Gustav BrockCIOCommented:
> while currently we are close to hit 2gb limit within a day,

If that really is so, that will be the single argument you need for your boss to move away from the shared frontend at the soonest.

/gustav
0
ForresterCAAuthor Commented:
Gustav,
as I said, he wants proofs, that this bloating cannot be resolved in any other way
0
Gustav BrockCIOCommented:
OK, Jim's last comment covers this quite well ...

/gustav
0
ste5anSenior DeveloperCommented:
..the front-end database is saved on each client computer..
(MSDN)
0
Armen Stein - Microsoft Access MVP since 2006PresidentCommented:
This is like the climate change debate.  Every expert (including me) is saying to not share the FE.  But your boss wants "proof".  Sorry, there is no definitive proof.  We all say just do it.

Armen Stein, Access MVP
J Street Technology
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ForresterCAAuthor Commented:
I was hoping to wait till MS will make official comment, but EE requires me to close the question.
I'm accepting multiple solution including all participants, to say thank you for support.
0
ste5anSenior DeveloperCommented:
I guess, that you won't get more than the MSDN link I've posted. And MSDN is imho quite "official" or "proof"lke.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.