Fenrirshowl
Technical User
Hi all
I have read a number of posts on various speed issues with multi-user databases, but I haven't found one that mirrors my particular circumstances.
At the start of October 2012 our office lost our local server. Our files are now held on a client server in a different part of the country. We access them via a Citrix portal using "One Desktop".
Before October we were running Office 2007. We then moved to 2010. The majority of people moved to Thin Client terminals so have lost their desktop PCs. I have retained my desktop.
We use an Access split end database daily. It is a bit of a beast, and it is mine. There are approx 50 normalized tables in the back end. All other information (queries, forms, reports etc) are held in the front end.
The database is a workflow system, and contains project plans and various information to do weekly and monthly reports. The flexibility has lead to the large number of tables. For most daily operations it will use 10-18 only. Some tables store information about data changes, system changes (i.e. version control info) which are rarely read from, but regularly written to.
I use an unrestricted version, the version the others use has no navigation bar and restricted menus. (Not very secure I know but no-one else really understands Access so I haven't devoted more time to locking it down, apart from turning off certain controls depending on the persons group which is based on their system/server log in)
Before October, speed wasn't a huge issue. Some people had old PCs and they were slower, but generally things were ok. Now we have major slow down, to a x8 factor.
All links between FE/BE are UNC. There are usually only up to 5 or maybe 10 users at any one time. (There is code to shut down the system after 30 mins of inactivity to keep the numbers down.)
I am unable to convert the BE to an SQL database due to my system/user restrictions. I have been unable to convert the FE to an MDE file (haven't quite figured out why - the help file mentions about the number of table IDs having a max of 2000 or something, but I don't have that many).
Problem - its now too slow
Observation - there is a main form used that houses a lot of information about particular work tasks to be completed. User A logs in as user one and can switch between records with under 2 second lag. I can happily live with this. User B logs in, and can switch between records with under 2 second lag. Again, acceptable. User A amends one record and moves to another record, so the amended record is saved. User B then moves records from one unrelated to User A, to another unrelated to User A. The lag is now approx 16 seconds. User B then amends a record, moves, and User A then encounters a similar slowdown.
The front ends are different files (copies of the same master file).
I have tried one of the Tek-Tips solutions to bind a constantly open form (the 30 mins shut down is running from a hidden form, so I have bound that to a table in the back end) - no change.
I am not an Access genius - I can build things that work reasonably well, and when they go wrong I can usually search for the appropriate words to find an answer online, use my Access 2000 book or my VBA book. However, the switch to 2010, Thin Client, Citrix portals etcs has given me too many variables to play with and not enough knowledge or experience in these new elements to try and find a solution myself.
Has anyone got any tips on why the above might be happening and what are the principal factors? Solutions also welcome!![[bigsmile]](/data/assets/smilies/bigsmile.gif "[bigsmile] [bigsmile]")
Returning to a local server and everyone having desktop PCs is not an option unfortunately.
[Finally, MS Access isn't officially supported within the company, so I am mainly on my own. The database was set up as a solution to the problem that the company systems already in place did not do what my dept needed them to do. The size of the dept means that funding an off the shelf package is in the "too expensive" list of actions.]
Regards and thanks
Fen
I have read a number of posts on various speed issues with multi-user databases, but I haven't found one that mirrors my particular circumstances.
At the start of October 2012 our office lost our local server. Our files are now held on a client server in a different part of the country. We access them via a Citrix portal using "One Desktop".
Before October we were running Office 2007. We then moved to 2010. The majority of people moved to Thin Client terminals so have lost their desktop PCs. I have retained my desktop.
We use an Access split end database daily. It is a bit of a beast, and it is mine. There are approx 50 normalized tables in the back end. All other information (queries, forms, reports etc) are held in the front end.
The database is a workflow system, and contains project plans and various information to do weekly and monthly reports. The flexibility has lead to the large number of tables. For most daily operations it will use 10-18 only. Some tables store information about data changes, system changes (i.e. version control info) which are rarely read from, but regularly written to.
I use an unrestricted version, the version the others use has no navigation bar and restricted menus. (Not very secure I know but no-one else really understands Access so I haven't devoted more time to locking it down, apart from turning off certain controls depending on the persons group which is based on their system/server log in)
Before October, speed wasn't a huge issue. Some people had old PCs and they were slower, but generally things were ok. Now we have major slow down, to a x8 factor.
All links between FE/BE are UNC. There are usually only up to 5 or maybe 10 users at any one time. (There is code to shut down the system after 30 mins of inactivity to keep the numbers down.)
I am unable to convert the BE to an SQL database due to my system/user restrictions. I have been unable to convert the FE to an MDE file (haven't quite figured out why - the help file mentions about the number of table IDs having a max of 2000 or something, but I don't have that many).
Problem - its now too slow
Observation - there is a main form used that houses a lot of information about particular work tasks to be completed. User A logs in as user one and can switch between records with under 2 second lag. I can happily live with this. User B logs in, and can switch between records with under 2 second lag. Again, acceptable. User A amends one record and moves to another record, so the amended record is saved. User B then moves records from one unrelated to User A, to another unrelated to User A. The lag is now approx 16 seconds. User B then amends a record, moves, and User A then encounters a similar slowdown.
The front ends are different files (copies of the same master file).
I have tried one of the Tek-Tips solutions to bind a constantly open form (the 30 mins shut down is running from a hidden form, so I have bound that to a table in the back end) - no change.
I am not an Access genius - I can build things that work reasonably well, and when they go wrong I can usually search for the appropriate words to find an answer online, use my Access 2000 book or my VBA book. However, the switch to 2010, Thin Client, Citrix portals etcs has given me too many variables to play with and not enough knowledge or experience in these new elements to try and find a solution myself.
Has anyone got any tips on why the above might be happening and what are the principal factors? Solutions also welcome!
Returning to a local server and everyone having desktop PCs is not an option unfortunately.
[Finally, MS Access isn't officially supported within the company, so I am mainly on my own. The database was set up as a solution to the problem that the company systems already in place did not do what my dept needed them to do. The size of the dept means that funding an off the shelf package is in the "too expensive" list of actions.]
Regards and thanks
Fen