[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Microsoft Access performance is slow

Posted on 2016-10-01
10
Medium Priority
?
167 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:J.R. Sitman
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +2
10 Comments
 
LVL 52

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: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.
0
 
LVL 52

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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 58

Accepted Solution

by:
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.

Jim.
0
 
LVL 22

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

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.

Thanks
0
 
LVL 58
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:J.R. Sitman
ID: 41832376
thanks
0
 
LVL 22
ID: 41832934
you're welcome ~ happy to help
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

650 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