Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2010
    Posts
    57

    Unanswered: Slow Combo box when using linked SQL Server tables

    My Access application contains tables linked to a SQL Server 2008 back end.

    I have a form which contains a combo box that is populated from one of the linked tables. For folks working across the WAN (rather than the LAN local to the SQL server) it takes almost a second for the combo box to be filled after the form opens and the rest of the fields are populated.

    I've tried various data sources to the combo box: a local query on the linked tables, a pass through query, a server-side view, and it is always the same. Even more intresting I've tried limiting the returned query results to just one record, or to the full 200+ records in the table, and the results are the same (about a 1 second delay, regardless of how much data is being returned)

    Any suggestions?

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Use a local table as cache for the combo. You fill that table when the form opens (e.g. SELECT ... INTO <LocalTable> pass-through query). Nothing prevents you from refreshing the contents of the local table from time to time in the background, should the data be subject to frequent changes.
    Have a nice day!

  3. #3
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    WANs and Access don't play well together, but Albert Kallal has an often quoted article on the subject of WANs and Access at

    MS Access on a WAN?.


    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  4. #4
    Join Date
    Aug 2010
    Posts
    57
    @Sinndho - local table was what I was leaning toward. Figuring out when to update it will be the only tricky part.

    @missinglinq - I'd seen that page before. Obviously the part about properly sizing the WAN is irrelevant post fact Besides, I don't think it is a true speed issue, but some sort of transaction latency. It doesn't seem to matter whether I bring down 1 record or 200. I've also been looking at SharePoint. Problem with SharePoint is that putting together any sort of fancy front end involves painful development of web parts. Publishing from Access to the web shows promise - I'll have to wait until my company stands up SharePoint 2010

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •