Solved

Tabbed Control on Access Form

Posted on 2011-03-07
5
244 Views
Last Modified: 2012-05-11
Good morning. I have been struggling with this issue for too long and just can't quite resolve it. I have a form with 4 tabs. On tab change I change the record source of the form. The most records displayed in the continuous form is 60-70 -- with around 50 being average. When a 2nd or 3rd user logged in the form and tab changes take 20- 30 seconds before the record source refreshes. Unacceptable.

The environment is: I have a batch file which copies the front-end from a server location to the user's virtual C:\ drive. I say virtual because I don't know how Remote Desktop on a Server treats the user's C: drive.  The back-end Jet DB never exceeds 35mb has relationships and indexes along with referential integrity. I have also made sure not to have too many indexes.

Could this issue be related to 7-8 users running Access through Remote Desktop? The performance of this basically simple application has never been where it should be. I've had apps with many more users with much larger back-ends and never saw this type of poor performance.

They recently replaced the NIC card because they were experiencing drops/corruptions and that seems to have resolved that issue.

How does the Access engine behave in a Remote Desktop session? Do all the users share the same Jet engine? Could that be causing problems? I have suggested that we take the 3 people who have Access 2007 installed on their local machines, move the app there --- and then put the BE on a local machine to see if we can improve speed.

They share space on the Server with another office location - but those users use their own Access application - but again - I wonder if they all share the same engine.

I am ready to pull my hair out. This is not a big calculation heavy FE. It's basic. I would go with unbound forms and/or temp tables if the data wasn't shared in real time -- so that won't work.

Help! Thanks in advance.


Eileen
0
Comment
Question by:Ei0914
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 85
ID: 35059352
In most cases performance with Remote Desktop is as good (if not better) than performance on a standard LAN.

Is this database split into Backend (tables only) and Frontend (everything else)? If not, then you could be encountering some issues with users "sharing" the same objects.

How large are the tables that are supplying data to those continuous forms? Access/Jet/ACE is a file-based database engine, which basically means that the workstation does all the processing, so even if your forms are showing 50 - 70 records, if the tables that hold the data hold 200,000 records, then Access/Jet/ACE must work with ALL of those records (unless you're only working with indexed fields, that is)

 
0
 

Author Comment

by:Ei0914
ID: 35059406
Hey there -- thanks for the response.... It is a FE/BE scenario --- the tables contain no more than 1500 records. Some have even fewer. I do have indexed fields. I changed the form to contain 2 sub-forms - 1 continuous and set to Snapshot and 1 with a single record only (based on the selection in the first) -- I thought that to have only 1 editable record available at a time might speed things up. Initially it seemed to speed things up -- but when the 4th user logs in --- it becomes sooooo slow it's inexplicable.

I do plan to migrate to SQL Server, but the db is so small it almost seems like overkill.
0
 
LVL 10

Accepted Solution

by:
Luke Chung earned 500 total points
ID: 35059459
Sounds like a multiuser database locking performance problems.

Do you create a database connection to the back-end/linked database when your application starts and keep it open?  It can make a significant difference and is often the problem when the performance hit is on the second person (which has nothing to do with the network, size of the database, etc.)

Read my paper on Significantly Improve the Performance of Microsoft Access Databases with Linked Tables for more details including the code from our Total Visual SourceBook program to handle this:

http://www.fmsinc.com/microsoftaccess/performance/linkeddatabase.html
0
 

Author Comment

by:Ei0914
ID: 35059631
Well -- let's see if that works. I've never used that before nor did I realize that the lock file is created every time a table is opened. I will let you know!

Thank you so much!
0
 
LVL 10

Expert Comment

by:Luke Chung
ID: 35709833
You should see a significant difference.

If you're interested in more ways avoid errors, design Access applications better and improve performance, please take a look at our list of Best Practices for Microsoft Access Database Development: http://www.fmsinc.com/Products/analyzer/ErrorsList.html

All the best!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question