Improve company productivity with a Business Account.Sign Up


Tabbed Control on Access Form

Posted on 2011-03-07
Medium Priority
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.

Question by:Eileen Murphy
  • 2
  • 2
LVL 86
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)


Author Comment

by:Eileen Murphy
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.
LVL 10

Accepted Solution

Luke Chung earned 2000 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:

Author Comment

by:Eileen Murphy
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!
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:

All the best!

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

I have had my own IT business for a very long time. I started mostly with hardware and after about a year started to notice a common theme. I had shelves with software boxes -- Peachtree, Quicken, Sage, Ouickbooks -- and yet most of my clients were…
If you need to implement application level security in an Access database application or other VBA code, I strongly encourage you to take advantage of Active Directory groups.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

607 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