Results 1 to 6 of 6
  1. #1
    rdougherty is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Dec 2016
    Location
    Michigan
    Posts
    91

    Query Access from Excel using SQL in VBA or Power Query, and why?

    I am dealing with what appears to be a known slow query functionality with Power Query, compared to running the query within Access. I am using PQ to populate a table used in a Pivot-style Excel report. I am trying to automate someone's many manual steps, and the final step in this newly automated report is very slow which is unsettling

    Would simply writing out some SQL and pasting it into VBA on the Excel side be better? I have never done that before...

    Example is not important - this is any very simple return of small number of rows, which would normally return in a second or less, can take up toa minute to retrieve.



    Network is only a small portion of the issue - Access to Access over this same network is much faster than Excel to Access... Even Local Excel to Local Access is slow!

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    if you write data to your local FE, compact it.
    also compact the BE tables, time-to-time.

    you should have a query and just export it via Transferspreadsheet.
    or even a query on external link to excel thru access should be quick too.

  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Maybe this will help.....

    Is your dB split? If yes, be sure to link to the BE.

    Here is one method using Excel to extract data from Access.
    http://www.exceltrainingvideos.com/g...ike-ms-access/
    Read the whole article. Starting at "Select Sheet2", the example used Microsoft Query to get the data.

    Again, Note: if the Access dB is split, make sure to link to the back end (where the tables are). Do not link to the front end - it takes longer.


    Another method is shown in this YoiuTube video
    https://www.youtube.com/watch?v=MWLBM2t7azc
    This method uses ADO to connect to the Access dB.
    The code is located in one of the links shown in the text of the video
    http://www.exceltrainingvideos.com/g...ing-excel-vba/


    This gets the data from Access. You'll probably have to do some manipulating to get the data in the format you want.

  4. #4
    rdougherty is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Dec 2016
    Location
    Michigan
    Posts
    91
    thanks for the help, both of you. I am sorry I have not been back to read, as I was pulled away from this project and its research...

    Can anyone articulate why something like Power Query is so damned slow? Why is it slower than me copying a table to excel, and manually pasting it into the final document so it is there as source data? How can a machine be 10x slower than a human?

    Thanks.

  5. #5
    rdougherty is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Dec 2016
    Location
    Michigan
    Posts
    91
    Quote Originally Posted by ranman256 View Post
    if you write data to your local FE, compact it.
    also compact the BE tables, time-to-time.

    you should have a query and just export it via Transferspreadsheet.
    or even a query on external link to excel thru access should be quick too.
    Transferspreadsheet method was not going to work for some constraint... I don't remember exactly right now. It was something to do with exporting it to a named table vs a range, which is required so that pivots can always point to the same named data and expand and collapse as data changes. You cannot have an Excel Table defined and replace said data with Transferspreadsheet, according to a link someone gave me ehre from another thread... (and I tried everything).

  6. #6
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi there, please feel free to download and use my free utility (http://forestbyte.com/ms-access-util...able-designer/) that allows you to design your pivot table in Access and export it to Excel (it exports the data and creates the pivot table in VBA).

    Cheers,
    Vlad

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

Similar Threads

  1. Replies: 2
    Last Post: 12-15-2017, 08:09 PM
  2. Calling Microsoft Power Shell from within Access
    By crowegreg in forum Programming
    Replies: 1
    Last Post: 11-18-2014, 02:59 PM
  3. Import access query data into excel file, using excel vba
    By exceljockey in forum Import/Export Data
    Replies: 3
    Last Post: 11-15-2013, 02:36 PM
  4. Replies: 7
    Last Post: 04-25-2013, 03:47 PM
  5. Replies: 1
    Last Post: 08-31-2009, 10:24 AM

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