Results 1 to 8 of 8
  1. #1
    mindbender is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2013
    Posts
    283

    Database Speed Slow with Linked Tables


    Hello Gurus,

    I have a database that looks at 4 different linked tables. The main form has data from one of the tables and the other three are on individual subforms.

    Even running the queries that drive the forms is really slow.....like minutes to return data. I have tried make table queries to bring the data local and it is still really slow.

    Any ideas on how to improve performance in general?

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    It depends on your server and network.
    I had MANY links in my db and it ran fast in 1 dept, but very slow in another dept. The network in that space slowed everything down.

    If the db is used by 1 user, then you could move everything to the local PC,
    but if its a multi-user db then you must keep it on the network.

  3. #3
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    If you have a slow network problem then some improvements can be made;
    Make sure you only retrieve the records you want to work.
    Load forms initially with no records e.g. SELECT * FROM YourTable WHERE PrimaryKeyID = 0
    Then only load a specific set of records you want after a search.

    Are all your subforms displayed at the same time? If they are on tabs, only load the subform on the tab that is visible.
    This can make a big difference.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  4. #4
    mindbender is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2013
    Posts
    283
    Quote Originally Posted by Minty View Post
    If you have a slow network problem then some improvements can be made;
    Make sure you only retrieve the records you want to work.
    Load forms initially with no records e.g. SELECT * FROM YourTable WHERE PrimaryKeyID = 0
    Then only load a specific set of records you want after a search.

    Are all your subforms displayed at the same time? If they are on tabs, only load the subform on the tab that is visible.
    This can make a big difference.

    So I am new to databases working this way as usually they are small and speedy.

    This is the code I have for querying which I am assuming because of the Me. it needs the form to be loaded with data first before it can query or it will find no results every time.

    Code:
    Option Compare Database
    
    Private Sub Command36_Click()
    Dim sWhere As String
    
    
    sWhere = "1=1"
    If Not IsNull(Me.txtCode) Then sWhere = sWhere & " and [PROD_CD]='" & Me.txtCode & "'"
    If Not IsNull(Me.txtCarrier) Then sWhere = sWhere & " and [SUB_LOC_CD]='" & Me.txtCarrier & "'"
    If Not IsNull(Me.txtMCode) Then sWhere = sWhere & " and [MKT_CD]='" & Me.txtMCode & "'"
    
    
    If sWhere = "1=1" Then
    Me.FilterOn = False
    Else
    Me.Filter = sWhere
    Me.FilterOn = True
    End If
    End Sub
    
    
    
    
    Private Sub Command40_Click()
    Me.FilterOn = False
    Me.txtCode = Null
    Me.txtCarrier = Null
    Me.txtMCode = Null
    Me.Refresh
    End Sub
    This is what the form looks like

    Click image for larger version. 

Name:	Capture.jpg 
Views:	32 
Size:	270.8 KB 
ID:	35302

    Main form with 2 sub forms.

    I am unsure how to start with the querying of the empty form.

    Any help you can provide would be awesome

  5. #5
    Shadow9449 is offline Advanced Beginner
    Windows 7 64bit Access 2003
    Join Date
    Feb 2017
    Posts
    38
    Do you have a persistent connection to the database? This can make a big difference (My apologies if this question was answered in this thread and I didn't see it).

    A good source about splitting databases in general:

    http://kallal.ca/Articles/split/index.htm

    Scroll down to One Last Tip for an explanation of a persistent connection and see if that helps.

  6. #6
    mindbender is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2013
    Posts
    283
    Quote Originally Posted by Minty View Post
    If you have a slow network problem then some improvements can be made;
    Make sure you only retrieve the records you want to work.
    Load forms initially with no records e.g. SELECT * FROM YourTable WHERE PrimaryKeyID = 0
    Then only load a specific set of records you want after a search.

    Are all your subforms displayed at the same time? If they are on tabs, only load the subform on the tab that is visible.
    This can make a big difference.

    Hi Minty,
    Do you have any samples of code used to generate the filters. I have the form opening blank now but I am unsure how to create the queries to run when the filter button is pressed if there is data in 1, 2 or all 3 filter fields.

  7. #7
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Once you click to apply your filter then change the record source to only load records that match the filter. So if you had a customer account number field you would load
    Code:
    SELECT Field list you need FROM YourTable WHERE AccountNo = YourAccountnoFilterValue
    Allen Browne excellent search form shows some of the techniques for this. http://allenbrowne.com/ser-62.html
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  8. #8
    mindbender is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2013
    Posts
    283
    Quote Originally Posted by Minty View Post
    Once you click to apply your filter then change the record source to only load records that match the filter. So if you had a customer account number field you would load
    Code:
    SELECT Field list you need FROM YourTable WHERE AccountNo = YourAccountnoFilterValue
    Allen Browne excellent search form shows some of the techniques for this. http://allenbrowne.com/ser-62.html

    thank you very much on my way

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 9
    Last Post: 10-08-2015, 05:13 AM
  2. Slow access tables linked to sharepoint. How to fix it?
    By flamingos_usa in forum SharePoint
    Replies: 3
    Last Post: 05-03-2014, 09:36 AM
  3. Replies: 3
    Last Post: 06-29-2012, 08:58 AM
  4. Speed/perfromance issues with Linked Tables.
    By Jamescdawson in forum Database Design
    Replies: 2
    Last Post: 05-12-2012, 04:04 AM
  5. linked tables - slow
    By wowiwi in forum Access
    Replies: 5
    Last Post: 10-01-2011, 12:17 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums