Results 1 to 3 of 3
  1. #1
    chalupabatman is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Aug 2016
    Posts
    114

    Will VBA Performance Time Improve If I Use Local Tables

    I am currently using VBA to build two queries with where clauses that query linked MS SQL Server database tables. The process does not take terribly long to run (maybe 8 - 10 seconds) but it freezes the UI.



    Would performance and/or query time be improved if incrementally throughout the day I made local copies of these linked SQL Tables then ran the VBA syntax against the local database tables?

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Linked SQL is usually faster than linked Access but local tables may be faster again.
    However I would instead look to optimising how your run your query on the SQL tables.
    If you allow most of the work to be done at server level, you should get a significant improvement without spending time makes local copies that will rapidly go out of date.
    Suggest you do a Google search - there should be a lot of information available to assist you
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    chalupabatman is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Aug 2016
    Posts
    114
    It's all done in access. The queries against the linked SQL tables are done as access queries.

    I'll test against local tables and see if I see any performance increase.

    Unfortunately we do not have direct access to the server to do the queries there or create views/indexes etc etc to expedite the process.

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

Similar Threads

  1. Local tables created by SSMA
    By Joakim N in forum SQL Server
    Replies: 2
    Last Post: 03-04-2019, 12:33 PM
  2. Show me a contact's local time?
    By dniezby in forum Programming
    Replies: 14
    Last Post: 03-25-2017, 01:32 PM
  3. Replies: 1
    Last Post: 12-11-2013, 01:18 PM
  4. Replies: 3
    Last Post: 10-18-2011, 03:08 PM
  5. Convert local time to UTC
    By zapper222 in forum Programming
    Replies: 0
    Last Post: 08-01-2010, 03:01 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