Solved

Microsoft Access performance is slow

Posted on 2016-10-01
10
66 Views
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
0
Comment
Question by:jrsitman
  • 3
  • 2
  • 2
  • +2
10 Comments
 
LVL 49

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

/gustav
0
 

Author Comment

by:jrsitman
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.
0
 
LVL 49

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.

/gustav
0
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 250 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.

Jim.
0
 
LVL 18

Assisted Solution

by:crystal (strive4peace) - Microsoft MVP, Access
crystal (strive4peace) - Microsoft MVP, Access earned 250 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.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Closing Comment

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

Thanks
0
 
LVL 57
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.

Jim.


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
tagFromErrorHandling:
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

tagError:
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
0
 
LVL 75
ID: 41832372
+1 on turning off Subdatasheets !
0
 

Author Comment

by:jrsitman
ID: 41832376
thanks
0
 
LVL 18
ID: 41832934
you're welcome ~ happy to help
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Citrix XenDesktop, Citrix Studio, Citrix Policies, Citrix XenApp
Citrix XenDesktop 7.6 Citrix Policies Graphics
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
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 …

708 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

13 Experts available now in Live!

Get 1:1 Help Now