Microsoft Query is one of the most popular techniques to import data from external data sources into your Excel sheet. It offers a very flexible way of importing data such that if the data changes in the source database then you can also get the latest updates in your excel sheet by just refreshing your spreadsheet.
MS Query can import data from the following sources.
- Microsoft SQL Server Analysis Services
- Microsoft Office Access
- Microsoft FoxPro
- Microsoft Office Excel
- Text file databases
Lets see how we can connect your Excel spreadsheet to an MS Access Data Source by using the Microsoft Query feature.
First of all make sure that your Access Database is up and running, and then head over to the Data tab of the Excel spreadsheet and choose the From Microsoft Query option under the Get External Sources category
Now choose your data source, i.e. MS Access in our case.
Here browse and select the database that you wish to import into your Excel sheet.
The tables of the particular database will be displayed, now move the required fields from the Available tables and columns section, to the Columns in your query section by using the ‘>’ button.
Click Next and proceed to the Next step, and then specify any additional options like sorting etc.
That’s it, now choose how you would like to display the data of the Microsoft Access Database in your Excel sheet, as a Table, PivotTable Report, or PivotChart and PivotTable Report.
Click OK and you will find the data imported to your Excel sheet.