Import Access Parameter Query to Excel
Controlling data from Microsoft Access into Excel is one of the cooler things available in Excel. Keep the heavy data in Access and call upon parts of it as you need it with the assistance of a Parameter Query. If you are interested in importing the contents of a query then this the Import Access Query topic covers this.
Firstly the way you set up a Parameter Query in Access is pretty simple. In the Access. Click on the Query area and then click the Design button.
Click on the above.
You will notice in the bottom right inside square brackets [ ] I have the words ChooseRegion. This is the parameter part of the Parameter query.
Click either the View or the Run button.
When you run the Query the following box pops up. This is where the criteria for the query is placed. For Example in the Field called Region there are four Regions, The West, The North East, The South, The Mid West. Any one of these will unlock the query and filter the data base by the region typed in. Don't worry I will include the database to help make the process visual.
You have now created an Access Parameter Query.
In Excel we can leverage off the items in the Region Field of the Access Database and bring in a Region and its associated data.
The most important thing to do is to set up a connection to MS Access.
In Excel Press Alt F11.
Now choose Tools - References
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 conflict 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.
Sub AccParam() 'Excel VBA to import a parameter query from Access into Excel
Dim MyQueryDef As DAO.QueryDef
Dim MyRecordset As DAO.Recordset
Set MyDatabase=DBEngine.OpenDatabase("C:\Test\TestDb.accdb")'DB Name
Set MyQueryDef=MyDatabase.QueryDefs("qryStates") 'Query Name
Sheet1.[A1].CurrentRegion.ClearContents 'Range to Clear
MyQueryDef.Parameters("[ChooseRegion]")=Sheet1.[f6].Value
Set MyRecordset=MyQueryDef.OpenRecordset 'Open the query
Sheet1.Range("A11").CopyFromRecordset MyRecordset
The lines with green on them are the lines you will absolutely want to change if you are hooking this procedure up with your own database. The above example will bring back the data without the headings. I have the headings in the file but if you wan the headings too then add this procedure at the end.
For i=1 To MyRecordset.Fields.Count
The following is what the query looks like after it has run. Choose the data Validation list to import other regions.
