Solved

Tabbed Control on Access Form

Posted on 2011-03-07
5
239 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:
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

911 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

21 Experts available now in Live!

Get 1:1 Help Now