Solved

Tabbed Control on Access Form

Posted on 2011-03-07
5
238 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
  • 2
  • 2
5 Comments
 
LVL 84
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:
LukeChung-FMS 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:LukeChung-FMS
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

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
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…

758 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now