Microsoft Access performance is slow

Posted on 2016-10-01
Medium Priority
Last Modified: 2016-10-06
We use Access 2013 on Citrix and the Citrix servers are load balanced so Access is evenly distributed.  Citrix is run over a dedicated MPLS of 3 gps.

Users average 6-8 per server.  Over 5 the performance is degraded.   The memory on both servers never goes over 30% usage.  On small occasion the CPU will jump to 85%, but it's brief.

I'm looking for any suggestions that might help with the performance
Question by:J.R. Sitman
  • 3
  • 2
  • 2
  • +2
LVL 53

Expert Comment

by:Gustav Brock
ID: 41825191
You don't tell how you run the application and what database you share.

A proven method is to use SQL Server for the backend database and (silently) distribute the frontend to the user's local environment. You can study the article here for ideas:

Deploy and update a Microsoft Access application in a Citrix environment


Author Comment

by:J.R. Sitman
ID: 41825411
Not sure what you mean "how we run the application".
We aren't using SQL.
Each user has a frontend on the remote server.
LVL 53

Expert Comment

by:Gustav Brock
ID: 41825435
The sad situation is that you may not be able to do anything at all. Citrix admins usually have little understanding not to say passion for Access. Thus, adjusting something related to file sharing - which probably is what your issue is about - in a Citrix environment is normally a no-go.

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'.

LVL 59

Accepted Solution

Jim Dettman (Microsoft MVP/ EE MVE) earned 1000 total points
ID: 41825436
<<I'm looking for any suggestions that might help with the performance>>

  There are a multitude of things that can be done.  But for a start:

1. Make sure you keep a connection to the BE from the FE for the life of the app.

2. Check your indexing (whole subject onto itself).   Over indexing can be just as bad as under indexing.   Columns with low cardnality (ie. a yes/no column) should not be indexed.

3. Look at the app.  Most Access performance issues are related to development.   Things like using Domain functions (DLookup()) inside of a query.   Or using multiple Dlookups() instead of opening a recordset and fetching a record once, or not modifying a forms recordset to load related table data.

  Locking is another one.   Use Transactions set to no on some queries can boost performance considerably.

4. Make sure the DB's are not being virus scanned.

5. On Citrix, watch out for references in VBA and pathing in general.   Pointing to something like a bar code lib can end up with every user pointing to the the DB file (i.e. C:\Program Files\abarcode.MDA).

 You also say your load balancing servers.....where are the FE's stored?   If their being hauled over the network, that's not good.

6. Make sure your compacting the BE.

LVL 24

Assisted Solution

by:crystal (strive4peace) - Microsoft MVP, Access
crystal (strive4peace) - Microsoft MVP, Access earned 1000 total points
ID: 41825654
it is also best to use short fields for linking. Autonumber/Long Integer gives good performance. Text fields that are used shouldn't be allowed to be 255 characters -- resize them to be just what you need. And while I can't say for sure, my guess is you need to delete the Index/RelationshipWithRI (since that creates hidden index), compact/repair, then recreate the Index(es)/Relationships after resizing if you don't notice a gain in performance.

Author Closing Comment

by:J.R. Sitman
ID: 41832272
After getting your suggestions, I did reduce some of the indexing.   Also I found that turning off the automatic spell checking helped.

LVL 59
ID: 41832277
<< Also I found that turning off the automatic spell checking helped.>>

 That is a biggie and I was remiss in not mentioning.

 Along the same lines, subdatasheets should be turned off as well if you don't use them.


Sub TurnOffSubDataSheets()

    Dim MyDB As DAO.Database
    Dim MyProperty As DAO.Property
    Dim propName As String, propVal As String, rplpropValue As String
    Dim propType As Integer, i As Integer
    Dim intCount As Integer

10  On Error GoTo tagError

20  Set MyDB = CurrentDb
30  propName = "SubDataSheetName"
40  propType = 10
50  propVal = "[None]"
60  rplpropValue = "[Auto]"
70  intCount = 0

80  For i = 0 To MyDB.TableDefs.Count - 1
90      If (MyDB.TableDefs(i).Attributes And dbSystemObject) = 0 Then
100         If MyDB.TableDefs(i).Properties(propName).Value = rplpropValue Then
110             MyDB.TableDefs(i).Properties(propName).Value = propVal
120             intCount = intCount + 1
130         End If
140     End If
150 Next i

160 MyDB.Close

170 If intCount > 0 Then
180     MsgBox "The " & propName & " value for " & intCount & " non-system tables has been updated to " & propVal & "."
190 End If

200 Exit Sub

210 If Err.Number = 3270 Then
220     Set MyProperty = MyDB.TableDefs(i).CreateProperty(propName)
230     MyProperty.Type = propType
240     MyProperty.Value = propVal
250     MyDB.TableDefs(i).Properties.Append MyProperty
260     intCount = intCount + 1
270     Resume tagFromErrorHandling
280 Else
290     MsgBox Err.Description & vbCrLf & vbCrLf & " in TurnOffSubDataSheets RoutineName."
300 End If
End Sub
LVL 75
ID: 41832372
+1 on turning off Subdatasheets !

Author Comment

by:J.R. Sitman
ID: 41832376
LVL 24
ID: 41832934
you're welcome ~ happy to help

Featured Post

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

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

Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Beware when using the ListIndex and the Column() properties of a listbox in Access 2007.  A bug has been identified in the Access 2007 listbox code which can cause the .ListIndex property to return a -1, and the .Columns(#) property to return a NULL…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Hi, this video explains a free download that you can incorporate into your Access databases, or use stand-alone for contact management. Contacts -- Names, Addresses, Phone Numbers, eMail Addresses, Websites, Lists, Projects, Notes, Attachments…

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