Import Access Query to Excel

The following article explains how you would import an access query to Excel with the use of VBA.  Firstly I will set up a query in an Access Database and call the query into Excel.  If you want to import part of a query it might be worthwhile looking into import a                         Parameter query to Excel which is covered in a related topic.    The procedure has a number of practical implications, data which is not required such as the heavy source data can be stored in a database and called on when required.  The Excel file runs on smaller summarised data and is kept lean.  You have the power to call on refreshed data periodically to update your Excel datasets.  
 
Getting started the most important thing to do is to set up a connection to MS Access. 
 
In Excel Press Alt F11.
 
Now choose Tools - References

Access import

Scroll through the extensive list till you find Microsoft Office xx.0 Database Engine Object (where xx is 14.0 above)

If you are using a version less than 2007 you need to pay attention now. For earlier versions choose Microsoft DAO XX Object Library. Don't choose both there will be a confilct and your process will probably not run.

Now that you are connected to Access the following is the Coding which will make your procedure fly.


Option Explicit
Sub ImpAccess() 'Excel VBA to import Access query.
Dim MyDatabase As DAO.Database
Dim qryDef As DAO.QueryDef
Dim MyRecordset As DAO.Recordset

Set MyDatabase=OpenDatabase("C:\Test\TestDb.accdb") 'DB Name
Set qryDef=MyDatabase.QueryDefs("qryVanilla") 'Query Name
Set MyRecordset=qryDef.OpenRecordset Sheet1.Range("A11").CopyFromRecordset MyRecordset

For i=1 To MyRecordset.Fields.Count 'Headings to bring back
Sheet1.Cells(10, i).Value=MyRecordset.Fields(i - 1).Name
Next i
End Sub


The lines with Purple are the ones which are a must change when connecting to your own database. The above example will bring back the data and the headings. If I were running this procedure I would put the headers in the file as this would be something you know in advance which would mean you would not need to bring the headers back.

I will include both the Excel and the Access files. To see how they run together just save the Database to a Directory called "C:\Test\" and ensure you call the file TestDb.accdb. That is the newer version of Access. Change the file extension to suit older versions if necessary.

I have also included files which operate on Access 2000 mdb format.

Const dbPath="C:\Test\Db2.mdb"
Set sh=Sheet3


Your sheet needs to change to the Sheet Code name which relates to the sheet you want the results to appear on. You can also use this if it is simpler to understand. Set sh=Sheet3 is the final line which needs to change for your needs is the query name. My query is called:

DataQ rs.Open "Select * from DataQ", cn


So the above is the line that needs to change. The following are the relevant Excel and Access files. Happy importing.