none
How to prevent Access crashes? RRS feed

  • Question

  • Hi all.

    We have Access (Office 2010) database front end, all tables have been moved to SQL server and linked back to front end. Front end is on the shared server and there are 40+ users that are using database through Citrix (on average about 25 users connected at the same time). We have multiple servers and currently converting all 32 bit servers to xen servers - therefore mix of servers at the moment - some users would login into old servers, some - into new. The database crashing seem to start around the time when new servers were introduced. Could be a coincidence, since we had this issue over a year ago as well. Anyway - front end database started crashing periodically - sometimes twice in one day, sometimes once or twice per week. When it crashes, usually we'll have a lot of users still able to use it if they had it opened prior to crash, but nobody else can open database. Couple of times it seemed that the reason was network interruption that put database in "inconsistent state"; couple of times users reported that they got locked up in different software and shut down desktop without closing database, which also caused the "inconsistent state" message. We don't have a problem of recovering database, since we always have a good backup, and no loss of data. The problem is to get 20+ users out of the database in order to copy good version in; especially if it happens 2 times a day. And of course the issue of users' productivity and inconvenience. Few times we had an error message "You attempted to open database that is already opened by user ADMIN on machine 'server name'". As an experiment I would try to disconnect users from the server mentioned, but then it will give me the name of the next server and keep changing the server, until I get every user out of the database.  Forgot to mention that front end is ACCDE version of the file. Any suggestions on preventing it or giving me a clue what could be causing it, are appreciated!


    Alla Sanders

    Friday, August 24, 2012 6:13 PM

Answers

  • Ahh, for the taste of middle ground.

    I have experience in both worlds here. A life in Citrix and an Acess db.

    What you are hearing in this thread is normally correct but since you are using Citrix you have options to consider that others are not familiar with.

    Now all your data of course is in the Back End SQL server. It is only in one place, on one server.

    Everyone accesses your BE through an A2010 FE. Now here is where everyone is partly correct. You see a Front End can only work on a computer whether real in front of the user or accessed through a Citrix server that simulates a PC. This allows users to use a db without it being on their own machine and without the need for the software. Users don't need Access 2010 installed on their own PC's at all. They don't even need a runtime installation.

    Now the other side you must realize, Each user really does need an individual FE. These are free. They don't cost you anything except a few seconds to copy and paste with a variable name like db1, db2 and db3 etc. You can even do this with code. Then tell every user when they log in to Citrix to login to their own copy of the FE. You could even have this part built into the Citrix Login.

    The Admin issue is the FE sharing that you are doing. So while you are partially correct about using Citrix the other posters are also correct in advising you to have multiple FE's and Citrix understands this and can work with you.

    Hopefully this helps all understand a little better.


    Chris Ward

    • Proposed as answer by KCDW Thursday, August 30, 2012 9:23 PM
    • Marked as answer by 许阳(无锡)Moderator Friday, August 31, 2012 7:07 AM
    Saturday, August 25, 2012 2:43 AM

All replies

  • Hi all.

    We have Access (Office 2010) database front end, all tables have been moved to SQL server and linked back to front end. Front end is on the shared server and there are 40+ users that are using database through Citrix (on average about 25 users connected at the same time). We have multiple servers and currently converting all 32 bit servers to xen servers - therefore mix of servers at the moment - some users would login into old servers, some - into new. The database crashing seem to start around the time when new servers were introduced. Could be a coincidence, since we had this issue over a year ago as well. Anyway - front end database started crashing periodically - sometimes twice in one day, sometimes once or twice per week. When it crashes, usually we'll have a lot of users still able to use it if they had it opened prior to crash, but nobody else can open database. Couple of times it seemed that the reason was network interruption that put database in "inconsistent state"; couple of times users reported that they got locked up in different software and shut down desktop without closing database, which also caused the "inconsistent state" message. We don't have a problem of recovering database, since we always have a good backup, and no loss of data. The problem is to get 20+ users out of the database in order to copy good version in; especially if it happens 2 times a day. And of course the issue of users' productivity and inconvenience. Few times we had an error message "You attempted to open database that is already opened by user ADMIN on machine 'server name'". As an experiment I would try to disconnect users from the server mentioned, but then it will give me the name of the next server and keep changing the server, until I get every user out of the database.  Forgot to mention that front end is ACCDE version of the file. Any suggestions on preventing it or giving me a clue what could be causing it, are appreciated!


    Alla Sanders

    Friday, August 24, 2012 6:15 PM
  • Correct me if I read the post wrong (incorrectly) -- but it sounds like you have one front end being shared by multiple users.  If this is the case -- then the problem is that you have one front end being shared by multiple users.  The fix is -- each user should have their own copy of the front end.  --There IS one way to share one front end -- and that would be through a web application (Aspx, MVC).

    Rich P

    Friday, August 24, 2012 7:31 PM
  • Sorry, but that won't work for us. We do not have local copies of any applications on users PC. Everything is shared through Citrix environment. About 100 users could access the database and supporting 100 versions of the same database would be a nightmare, especially if front end needs to be updated. Plus employees' turnover... Never mind that it would be against our policies. Also - I did a search for similar issues and seems that network interruptions, as well as database corruptions, happening even if front end is local. Redesigning f this particular front end in ASP.NET is also out of question.

    Alla Sanders

    Friday, August 24, 2012 8:05 PM
  • If the users have individual accounts on the server, they might instead run a batch file that first copies the Access front end from a shared folder (read only) into their own workspace and then opens the individual copy (the batch script should append the user name to the copy to help keep things sorted).  Otherwise, what you describe is like having 20 people login as ADMIN on the same computer and open the same Excel file simultaneously (obvious problems).
    Friday, August 24, 2012 10:17 PM
  • Pardon the blunt response, but your production application will not operate properly under the conditions you describe. How did you test this before going into a production environment? Access was never intended to work under the conditions you describe. You asked for help...so take it:

    1. Stablize your server environment first and then move the back-end file to a single stable server

    2. Link the front-end application to the back-end file

    3. Test the link and application forms/reports etc.

    4. Distribute the front-end file to each user (as above)

    5. Test with multiple users and document/fix problems

    Friday, August 24, 2012 11:30 PM
  • Ahh, for the taste of middle ground.

    I have experience in both worlds here. A life in Citrix and an Acess db.

    What you are hearing in this thread is normally correct but since you are using Citrix you have options to consider that others are not familiar with.

    Now all your data of course is in the Back End SQL server. It is only in one place, on one server.

    Everyone accesses your BE through an A2010 FE. Now here is where everyone is partly correct. You see a Front End can only work on a computer whether real in front of the user or accessed through a Citrix server that simulates a PC. This allows users to use a db without it being on their own machine and without the need for the software. Users don't need Access 2010 installed on their own PC's at all. They don't even need a runtime installation.

    Now the other side you must realize, Each user really does need an individual FE. These are free. They don't cost you anything except a few seconds to copy and paste with a variable name like db1, db2 and db3 etc. You can even do this with code. Then tell every user when they log in to Citrix to login to their own copy of the FE. You could even have this part built into the Citrix Login.

    The Admin issue is the FE sharing that you are doing. So while you are partially correct about using Citrix the other posters are also correct in advising you to have multiple FE's and Citrix understands this and can work with you.

    Hopefully this helps all understand a little better.


    Chris Ward

    • Proposed as answer by KCDW Thursday, August 30, 2012 9:23 PM
    • Marked as answer by 许阳(无锡)Moderator Friday, August 31, 2012 7:07 AM
    Saturday, August 25, 2012 2:43 AM
  • Does every user have their own copy of the front end? If not, try giving each user their own copy.

    Rod Gill

    The one and only Project VBA Book

    Rod Gill Project Management

    Sunday, August 26, 2012 10:06 AM
  • @Lawrence - I believe I described in original posting that steps 1-3 are in place. About step 5 - we had this database in production for about 7 years already, so believe me, it's been tested thoroughly. And what's interesting - the problem that I described have been happening with the latest version of Access, plus with using XEN servers. It actually been originally designed with Access 97, I believe and converted to all other versions as those come out. Interesting that based on your and prior user' statements, it sounds like it shouldn't have worked, but like I said it worked for number of years.

    @Chris - I appreciate your explanation. I'll run it by our network guys. Not sure of it it is doable, and adheres to our company policy. They do build user's login scripts, but users have access to specific folders and applications based on their job duties. So everybody who is supposed to use my database would have access to the same shortcut for the same database through Citrix. I'm not sure how consuming and realistic it would be to program access to individual database.

    P.S. And yes - users do not have Access installed on their local PC, but using it through Citrix.


    Alla Sanders

    • Proposed as answer by JohnnyJCNJ Monday, August 27, 2012 8:30 PM
    • Unproposed as answer by JohnnyJCNJ Monday, August 27, 2012 8:31 PM
    Monday, August 27, 2012 12:42 PM
  • I think it worth noting that i am having databases crash repeatedly after being just fine for EIGHT years.  Seems that the new mix of windows 7 and windows xp is bring trouble. 
    Monday, August 27, 2012 8:33 PM
  • Hello Johnny,

    I don't think this thread relates to your comment. Since you are here I will say to make sure that you have all the latest updates both in Windows7 and Office XP. Please note that Office XP is no longer supported by Microsoft. There are too many factors as to why Office XP is deprecated. Just note that things change over time not to get money out of you but, rather to protect users from the bad folks out there. Many of today's trojans, viruses and phishing is due to the weaknesses of what once was daunting to infect.

    Everytime technology gets smarter, the bad guys exploit it. Luckily Technology is progressing faster than the bad guys can break it. So like it or not Microsoft has made better products over the years and deprecated infection prone software over the years. So it is in my mind very important to have the latest technology at my disposal.

    I have no experience using Office XP on Windows7 because I would never want to operate in that environment. Too many things don't get along. Have you looked into using compatability installation or multiple oeprating systems on your computer?


    Chris Ward

    Monday, August 27, 2012 9:05 PM
  • Hi JohnnyJCNJ,

    Thanks for posting in the MSDN Forum.

    I will mark KCDW's replay as answer. If you feel it doesn't help you, please feel free to unmark it.

    Have a good day,

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us

    Friday, August 31, 2012 7:07 AM
    Moderator