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
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
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.
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.
ASKER
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..
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..
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.
<<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.
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.
ASKER
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
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
<<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.
You won't find any.
Jim.
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.
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.
ASKER
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.E
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..
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.E
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..
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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..
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..
As a former Access Microsoft MVP:
There are no magical settings. But using a separated design is the standard with Access for years.. even when there are not that much specs out there.
MSDN: Best Practices When Using Microsoft Office Access 2003 in a Multi-user Environment
Allen Browne: Split your Access database into data and application
Tony Toews: Splitting your Microsoft Access MDB into a front end and back end - Why?
access.mvps.org: The Ten Commandments of Access
Armen Stein: J Street Access Relinker
There are no magical settings. But using a separated design is the standard with Access for years.. even when there are not that much specs out there.
MSDN: Best Practices When Using Microsoft Office Access 2003 in a Multi-user Environment
Allen Browne: Split your Access database into data and application
Tony Toews: Splitting your Microsoft Access MDB into a front end and back end - Why?
access.mvps.org: The Ten Commandments of Access
Armen Stein: J Street Access Relinker
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER
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!
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!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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..)
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..)
> 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
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
ASKER
Gustav,
as I said, he wants proofs, that this bloating cannot be resolved in any other way
as I said, he wants proofs, that this bloating cannot be resolved in any other way
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
..the front-end database is saved on each client computer..(MSDN)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
I'm accepting multiple solution including all participants, to say thank you for support.
I guess, that you won't get more than the MSDN link I've posted. And MSDN is imho quite "official" or "proof"lke.
One thing is for sure; users should not be sharing front ends.
Jim.