Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2012
    Posts
    3

    Unanswered: Speed DB Access over WAN

    New to having my Access tables on WAN rather than LAN and need some help to speed operations. These are Yacht Club members records now located on a remote MySQL server. My access code reads through the approximately 300 records and for each and determine the oldest name on that record, years of service, family status and finally output a record to a temporary table with the information I need and in turn query for a report. If the data tables are on my computer or even on on my local LAN the report is available with seconds. When linked to these same tables miles away on a server this same report requires over a minute to create.

    I need some direction and wonder if I should be reading the needed table into a temporary table on my computer before doing the necessary sequential reading, calculation and even "making" the table on my computer for the report. This thought has no grounds based on theory or experience, just a first guess - appreciate some guidance.

    Bitz

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    This is a bit long, but has great info:

    MS Access on a WAN?

    I use SQL Server in a WAN environment and the performance is good, but the trick is to make the server do the work and just send the end result back. I haven't used MySQL, but see if you can make that happen with pass-through queries or stored procedures.
    Paul

  3. #3
    Join Date
    Sep 2012
    Posts
    3
    Read the article on Access over WAN and which certainly explained my observations and a couple articles on pass through queries and stored procedures. I do thank you. I see a long and steep learning curve for this old man but you have given me more than enough information to search even further for the necessary foundation.

    Bitz

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Happy to help, one old guy to another.
    Paul

  5. #5
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    When I saw the title of your thread, I pulled the link for Albert's article from my archives, before even opening the thread or seeing that Paul had already posted it! This paper really is the final word on this kind of thing!

    Good luck to you!

    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

  6. #6
    Join Date
    Sep 2012
    Posts
    3
    Appreciate the reinforcement - makes easier concentrating on the various approaches without wondering about the authenticity.

    Progress sure makes life difficult; lot easier working on on a single computer and then thought I was keeping up when I learned to separate my code from my tables so code maintenance did not interrupt users and its obvious value on a LAN. I miss the DOS prompt, life was a lot simpler.

Posting Permissions

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