Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    anavagomez is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Apr 2017
    Posts
    31

    Question How to cache or store static reference table data in MS Access database.

    Hello,

    I work with an MS Access database that uses various reference tables as their rowsource. This causes slowness in the performance of the database. I was told that caching or storing static reference tables locally to the MS Access database would save hits and locks against the SQL Server database that the MS Access points in the backend.

    I have never done something like this and I am wondering if I could get some help as to how this is done, either by some articles that offer explanaition of if someone has a piece of code that can be refactored to fulfill my requirements.

    Any help would be greatly appreciated.



    Thank you.

    anavagomez.

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    if the backend is sql server use sql server to run your queries. using queries which mix local tables with external tables will always be slower. Also you cannot maintain referential integrity between local and external tables

    I would look at your sql server performance first - are all fields used for filtering and sorting suitably indexed? Is sql server properly set up and maintained - indexes refreshed regularly (equivalent to access's compact and repair) cores balanced etc. Is someone else running something which monopolises the resources? Are queries efficiently written?

    Is the network connection a good one?

    Are forms and reports (including combo and listboxes) optimised to minimise the network traffic - i.e. forms are not opened with an entire table as a recordset.

  3. #3
    anavagomez is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Apr 2017
    Posts
    31
    Right now we have a mix of linked tables and local tables. Change this will be a much larger project and I can't make that decision myself.

    Most of the dropdowns use local tables.

    How do you optimise combo and lists boxes to minimise network traffic?

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I've done it, but as Ajax mentioned you don't want any queries that link to both local and server tables. I use the local table as the source for combo/list boxes, but any queries joining them to transaction tables use the server tables. In other words, if I've got a lookup table for cities, I'll have local and server copies. If appropriate, when the app loads the local table is repopulated. Combos use the local table, queries join to the server table. If you have this:

    SELECT Blah
    FROM ServerTable INNER JOIN LocalTable

    Access can't send that SQL to the server as is (the server has no knowledge of the local table), so it pulls the entire server table across the network so it can resolve it. A potentially huge performance problem. If you have

    SELECT Blah
    FROM ServerTable INNER JOIN AnotherServerTable

    Access can send it straight to SQL Server and let it just send back the results.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    anavagomez is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Apr 2017
    Posts
    31
    Hello pbaldy, yes only the local tables are used for the drop downs and we think that is causing some of the slowness. I haven't seen any inline queries that join an external and a local table and we are moving slowly but surely from all the inline queries and putting them in sprocs in sql server.

  6. #6
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    How do you optimise combo and lists boxes to minimise network traffic?
    number of ways. if the rowsource is only a few records then not really a problem. Otherwise use code to leave the rowsource unpopulated until one or two characters have been entered, then populate the rowsource with a filtered list. Or if you have the same rowsource used multiple times, consider when the app opens, or first time call of the rowsource, populate an array or a string, then use the array or string to populate the rowsource as a value list rather than a recordset. This would also be relevant if the form has many combo/listboxes, all of which need populating.

  7. #7
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I'm confused, as the first post seemed to say that you wanted to move to using local tables for this. Now you say they already are, and you think that's the source of slow performance. A local table should be faster than a linked table, since nothing has to be pulled over the network.

    Ajax has a good thought if they have lots of records. Here's one method to implement the solution:

    http://allenbrowne.com/ser-32.html

    Let's confirm that each user has a copy of the front end on their computer, they aren't sharing a copy sitting on the network.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #8
    anavagomez is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Apr 2017
    Posts
    31
    Hello Ajax, sorry for my delayed response to your comments, I got sidetracked with other tasks. I am interested in finding out if you have an example of how to do: " if you have the same rowsource used multiple times, consider when the app opens, or first time call of the rowsource, populate an array or a string, then use the array or string to populate the rowsource as a value list rather than a recordset. This would also be relevant if the form has many combo/listboxes, all of which need populating."

    The immediate change I have to make, involves a form with several subforms that use combo/ list-boxes that reference the tables in SQL Server.

  9. #9
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    it is only relevant if the rowsource is the same.

    in a module code would be something like

    public commonrowsource as string

    public function popCommonRowsource()
    Dim rst as recordset
    set rst=currentdb.openrecordset("myrowsourcequery")
    While not rst.eof
    'change fld1 to the PK and fld2 to the name of the text field to be displayed (assumes just two columns required)
    commonrowsource=commonrowsource & rst!fld1 & ";" & rst!fld2 & ";"
    rst.movenext
    Wend
    end function
    When you first open the front end run the function

    when you open a form where the rowsource is to be used the code in the form open event would be

    me.cboControl.rowsource=commonrowsource

    ensure the combo rowsource type is set to valuelist

  10. #10
    anavagomez is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Apr 2017
    Posts
    31
    Hi Ajax, in your code example, where is the array being populated?

    Thank you for your help.

  11. #11
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    it's populating a string called commonrowsource

  12. #12
    anavagomez is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Apr 2017
    Posts
    31
    Thank you Ajax. I am going to give that a try this Monday. I hope I can report positive news.

    Again, thank you for taking the time to give help.

    anavagomez.

  13. #13
    anavagomez is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Apr 2017
    Posts
    31
    Hi Ajax, I put together a function using your recommendation:

    Code:
    Function GetComboBoxData()
    
    
        On Error GoTo errhandler
        
        Dim con As ADODB.Connection
        Dim rs As ADODB.Recordset
        Dim recordSourceData As String
        
        Set con = New ADODB.Connection
        With con
            .ConnectionString = GetConnectionString("MNCatalog")
            .Open
        End With
        
        Set rs = New ADODB.Recordset
        With rs
            .ActiveConnection = con
            .Open "SELECT DISTINCT" & _
                  "  Category" & _
                  ", ProductDescription" & _
                  ", BasePrice" & _
                  ", AdditionalPrintPrice" & _
                  ", MinimumPurchaseAmount" & _
                  "FROM z_PriceCategories " & _
                  "ORDER BY Category;", , adOpenStatic, adLockReadOnly
        End With
        
        While Not rs.EOF
            
            recordSourceData = recordSourceData & _
                                CStr(ADORS.Fields(0)) & ";" & _
                                CStr(ADORS.Fields(1)) & ";" & _
                                CStr(ADORS.Fields(2)) & ";" & _
                                CStr(ADORS.Fields(3)) & ";" & _
                                CStr(ADORS.Fields(4)) & ";"
            
            rs.MoveNext
        
        Wend
        
    eofit:
        
        On Error Resume Next
        con.Close
        rs.Close
        Set con = Nothing
        Set rs = Nothing
    
    
        Exit Function
    
    
    errhandler:
        
        z = ErrorFunction(Err, Err.Description, Erl, "GetComboBoxData", , True)
        
        Err = 0
        
        Select Case z
            Case 0: Resume Next
            Case 1: GoTo eofit
        End Select
    
    
    End Function
    I still do not understand how can I use the string populated with the recordset data as the rowsource of the combobox.

  14. #14
    anavagomez is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Apr 2017
    Posts
    31
    I figured out how to assign the value list of "recordSourceData" to the combobox. My problem now is that the values are moving in an "s" shape so rather than having

    value 1
    value 2
    value 3
    value 4
    value 5
    value 6

    The values are displaying like:

    value 1 value 2 value 3
    value 4 value 5 value 6

    Do you know where, or how I can correct this?

    Thank you.

  15. #15
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    sorry for not responding sooner - have been away. Suggest look at the number of columns to be displayed in the combo

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 03-01-2016, 06:05 AM
  2. Replies: 1
    Last Post: 05-21-2014, 12:38 AM
  3. Replies: 7
    Last Post: 02-25-2014, 03:11 PM
  4. Replies: 2
    Last Post: 12-02-2012, 09:14 PM
  5. Replies: 0
    Last Post: 01-09-2009, 03:10 PM

Tags for this Thread

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