What is the fastest way to pull data into Excel from Access (MS Query/DAO/ADO etc.)

[whitespace]

Hello all,

Could anyone tell me the fastest way to pull Access query data into Excel using vba. Note we need to be able to amend the queries in a loop to automate report generation (so the criteria changes on each loop). We currently use something along the lines of that below but some queries to the server run so slow. Is this the quickest way to do it??

Many thanks for any help.

s = "SELECT `KEY STAGE 3 RESULTS`.SchoolNo, `KEY STAGE 3 RESULTS`.Year, `KEY STAGE 3 RESULTS`.Sex, `KEY STAGE 3 RESULTS`.`KS3-En-Pupils`, `KEY STAGE 3 RESULTS`.`KS3-En-%L5`, `KEY STAGE 3 RESULTS`.`KS3-En-%L6`, `KEY STAGE 3 RESULTS`.`KS3-En-Pts`, `KEY STAGE 3 RESULTS`.`KS3-Ma-Pupils`, `KEY STAGE 3 RESULTS`.`KS3-Ma-%L5`, `KEY STAGE 3 RESULTS`.`KS3-Ma-%L6`, `KEY STAGE 3 RESULTS`.`KS3-Ma-Pts`, `KEY STAGE 3 RESULTS`.`KS3-Sc-Pupils`, `KEY STAGE 3 RESULTS`.`KS3-Sc-%L5`, `KEY STAGE 3 RESULTS`.`KS3-Sc-%L6`, `KEY STAGE 3 RESULTS`.`KS3-Sc-Pts`, `KEY STAGE 3 RESULTS`.`KS3-IT-Pupils`, `KEY STAGE 3 RESULTS`.`KS3-IT-%L5`, `KEY STAGE 3 RESULTS`.`KS3-IT-%L6`, `KEY STAGE 3 RESULTS`.`KS3-IT-Pts`, `KEY STAGE 3 RESULTS`.`KS3-Pub-Number`, `KEY STAGE 3 RESULTS`.`KS3-Pub-En-%L5`, `KEY STAGE 3 RESULTS`.`KS3-Pub-Ma-%L5`, `KEY STAGE 3 RESULTS`.`KS3-Pub-Sc-%L5`, `KEY STAGE 3 RESULTS`.`KS3-Pub-APts-%L5` "
s = s & "FROM `H:\LSIP\Current Year\Special\Special`.`KEY STAGE 3 RESULTS` `KEY STAGE 3 RESULTS` "
s = s & "WHERE (`KEY STAGE 3 RESULTS`.SchoolNo=" & Chr(39) & scno & Chr(39) & ") AND (`KEY STAGE 3 RESULTS`.Year>='2001') AND (`KEY STAGE 3 RESULTS`.Sex='M') "

    Sheets("GetKS3Male").Select
    Range("A1").Select
    With Selection.QueryTable
        .Connection = Array(Array( _
        "ODBC;DSN=MS Access Database;DBQ=H:\LSIP\Current Year\Special\Special.mdb;DefaultDir=H:\LSIP\Current Year\Special;DriverId=25;FIL=MS " _
        ), Array("Access;MaxBufferSize=2048;PageTimeout=5;"))
        .CommandText = s
        .Refresh BackgroundQuery:=False
    End With

 

[DCrake]

Simple Software Solutions

Hi

first decide do I want to copy data FROM Access TO Excel using Excel OR copy data FROM Access TO Excel using Excel?

You may thing that the latter is the best, however experience has told me that the former is far more manipulative.

So with that in mind may I point you to an MS Kb article that describes the different methods of transferring data to Excel from Access using VB(A).

Article no: 247412

http://support.microsoft.com/kb/247412/en-us


Good Look

Code Master

 

[unmarkedhelicopter]

Hi

first decide do I want to copy data FROM Access TO Excel using Excel OR copy data FROM Access TO Excel using Excel?

You may thing that the latter is the best, however experience has told me that the former is far more manipulative.

So with that in mind may I point you to an MS Kb article that describes the different methods of transferring data to Excel from Access using VB(A).

Article no: 247412

http://support.microsoft.com/kb/247412/en-us


Good Look

Code Master

to ...
copy data FROM Access TO Excel using Excel OR
copy data FROM Access TO Excel using Excel
Yep that's a toughie

 

[whitespace]

haha yeah that's what I thought unmarkedhelicopter, however I'm pretty sure I know what you meant DCrake and I've read the article. It is interesting but unfortunately doesn't go into the benefits of using ADO over Querytable or using csv file as I was after which is the fastest method or even which is the preferred method that most people use.

We currently use Queytable and it does work ok in the majority of cases but it runs slow. What does everyone else use?

For info we are pulling queries in from Access but we usually need to update the query with certain criteria; print the display sheets to pdf, then update the query with new criteria etc. usually a few thousand times.

Thanks for your help so far and any further help

 

[unmarkedhelicopter]

ADO is I believe the preferred weapon of choice, it's the most upto date and though DAO is still supported it is said that ADO is more resilient (don't quite know how that word applies, it either works or it doesn't). The issue is whether MS will continue to support DAO indefinitely, given MS unlikely, but there will always be someone who will step in and provide a dll to keep the party going. I've always found ADO to be superior to MS Query and generally now don't use anything else. I especially like the way that the user gan be given only the access they are supposed to have and it's difficult (for them) to access anything else. (DAO is much the same in that respect)

 

[boblarson]

ADO is I believe the preferred weapon of choice, it's the most upto date and though DAO is still supported it is said that ADO is more resilient (don't quite know how that word applies, it either works or it doesn't). The issue is whether MS will continue to support DAO indefinitely, given MS unlikely, but there will always be someone who will step in and provide a dll to keep the party going. I've always found ADO to be superior to MS Query and generally now don't use anything else. I especially like the way that the user gan be given only the access they are supposed to have and it's difficult (for them) to access anything else. (DAO is much the same in that respect)

I would tend to actually disagree and say that DAO will be faster than ADO, especially on a large dataset. DAO is native to JET and therefore requires less "translating" between Access and anything else. Based on other posts (and I'm too tired to go find them. I have to go to bed as it's 1am here) I would say that DAO is a good choice. If you want, look for posts on DAO vs ADo by Pat Hartman here on the forum.