Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


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 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)


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: http://www.fmsinc.com/Products/analyzer/ErrorsList.html

All the best!

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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…
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

572 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