 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Multiuser / Networking / March 2005linked tables project running extremely slow on network
Hello, I am in the process of testing and implementing an access project in a multi user environment. It sits on the corporate file server, and I have split up the database into front-end and DB in the back end with linked tables. I am encountering the following problem: it is extremely slow in opening up and even slower in opening up the form. It comparitively fast when not using linked tables. I am perplexed as to why that would happen? I have the following questions/ ideas for analyzing why it's slow. Any other suggestions and answers to these are appreciated. 1) I have asked the users to copy the .mde from network to their desktops, while the backend DB is on the server. is this the right way to do it? 2) I have a main menu that's the first thing to pop up, with different cmd buttons to go to other forms. is this what is called a switchboard form? if yes, should it be on the back end or front end? 3) also I am using DAO to run queries and update recordsets, where I have set an instance DAO.database to current database. Does this slow it down because it's going to look for the actual table and not at the linked table? 4)Is it a good idea not to link tables with initial data like user id's that dont change? will that speed up? 5) another option was to let the users update the linked tables using linked table manager when done entering data is this good option? Thanks > I am in the process of testing and implementing an access project >in a multi user environment. It sits on the corporate file server, and I have >split up the database into front-end and DB in the back end with linked >tables. I am encountering the following problem: it is extremely slow in >opening up and even slower in opening up the form. It comparitively fast when >not using linked tables. I am perplexed as to why that would happen? The three most common performance problems in Access 2000 or newer are: - LDB locking which a persistent recordset connection or an always open bound form corrects (multiple users) - sub datasheet Name property set to [Auto] should be [None] - Track name AutoCorrect should be off If the problem is for everyone when starting up the MDB then it likely needs a decompile. For more information on these, less likely causes, other tips and links to MS KB articles visit my Access Performance FAQ page at http://www.granite.ab.ca/access/performancefaq.htm >1) I have asked the users to copy the .mde from network to their desktops, >while the backend DB is on the server. is this the right way to do it? Yes. You reduce the risk of corruptions and make it much easier to update the FE when you wish to make updates available to the users. I specifically created the Auto FE Updater utility so that I could make changes to the FE MDE as often as I wanted and be quite confident that the next time someone went to run the app that it would pull in the latest version. For more info on the errors or the Auto FE Updater utility see the free Auto FE Updater utility at http://www.granite.ab.ca/access/autofe.htm at my website to keep the FE on each PC up to date. >2) I have a main menu that's the first thing to pop up, with different cmd >buttons to go to other forms. is this what is called a switchboard form? if >yes, should it be on the back end or front end? Because it's a form with command buttons it must remain in the FE. The BE should only have tables, relationships, indexes and one form which tells the users to get out. >3) also I am using DAO to run queries and update recordsets, where I have >set an instance DAO.database to current database. Does this slow it down >because it's going to look for the actual table and not at the linked table? No, because the current database is your FE. So it will be using the linked table. >4)Is it a good idea not to link tables with initial data like user id's that >dont change? will that speed up? Not sure what you mean by this. >5) another option was to let the users update the linked tables using linked >table manager when done entering data is this good option? NO, Users should never, ever update data from tables. They should be using forms. Otherwise they can get used to changing data in fields which they shouldn't be touching etc. And their "experience", to use a MS term currently in vogue, should be as simple as possible. Tony -- Tony Toews, Microsoft Access MVP Please respond only in the newsgroups so that others can read the entire thread of messages. Microsoft Access Links, Hints, Tips & Accounting Systems at http://www.granite.ab.ca/accsmstr.htm Tony, Thanks for your response, I have been reading up on your website about the various factors, that could potentially slow the application down in a linked table context. it's a great site,a nd I found a few hints to try. Since the time I posted the question, I made a few changes to the form you suggested and like removing all excess code in the form_load module, changing the oleimage types to regular oleunbound bitmaps and that has helped a little bit. One thing I am not sure of is the persistent connection.I have the switchboard form on the FE,and a command button opens another form( accessing various tabled in the DB at this point). How can I set a persistent connection for that form? Where can I find the subdatasheet property name to be set to auto? I am using access 2000 Thanks, MC > > I am in the process of testing and implementing an access project > >in a multi user environment. It sits on the corporate file server, and I have [quoted text clipped - 66 lines] > Microsoft Access Links, Hints, Tips & Accounting Systems at > http://www.granite.ab.ca/accsmstr.htm > Thanks for your response, I have been reading up on your website about >the various factors, that could potentially slow the application down in a [quoted text clipped - 7 lines] >various tabled in the DB at this point). How can I set a persistent >connection for that form? One way would be to make the switchboard form bound to a table in the backend. This is assuming it's your switchboard form and not the standard Wizard generated switch board form. >Where can I find the subdatasheet property name to be set to auto? I am >using access 2000 If you follow the link on my page to the MS KB article ACC2000: Slower Performance on Linked Tables - 261000 you will find the code required. Tony -- Tony Toews, Microsoft Access MVP Please respond only in the newsgroups so that others can read the entire thread of messages. Microsoft Access Links, Hints, Tips & Accounting Systems at http://www.granite.ab.ca/accsmstr.htm Tony, Every link does not show up for me, however, I can access the main page (http://www.granite.ab.ca). how do I get at those links from there. I also am having slow form load times when used with linked tables over a LAN. "If you follow the link on my page to the MS KB article ACC2000: Slower Performance on Linked Tables - 261000 you will find the code required." I don;t see the link in your tips/kb article link page. I will have to search MS then. Scratch that. It seems my computer can not see the site, however a co-worker's can. Ok, I got the FE to load quickly on multiple users machines. However if they want to put the FE in their startup the FE couldn't find the tables when it started. My guess is due to the fact that mapped network drives haven't be connected to yet. I put in the UNC name instead of drive letter but now the FE is back to loading slowly. Any ideas? i run a2k and have several 'mdb' files sitting on a shared network drive. i guess the most frequently occuring problem i encounter is my users telling me that they have a lock 'ldb' file which prevents them from accessing the mdb file. i've been told by the mis network folks that sometimes a 'ldb' lock file can persist even though the user's logged out of the database and that a shutdown/reboot of the user's machine can have a salutory effect. what does 'LDB locking which a persisten recordset connection fixes' mean? there are no fe/be situations here. thanks for any assitance w/ this... -ted > > I am in the process of testing and implementing an access project > >in a multi user environment. It sits on the corporate file server, and I have [quoted text clipped - 66 lines] > Microsoft Access Links, Hints, Tips & Accounting Systems at > http://www.granite.ab.ca/accsmstr.htm |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.