There are a number of ways to get Access data into Excel and vice versa. Here is an example of one of the easiest ways.
Using QueryCell we can simply connect to the Access database and SELECT the data we are interested in directly from Excel.
1. Define an ODBC connection to your Access database. You can do this by simply clicking on the ODBC+ icon within Excel or by going to Control Panel -> Administrative Tools -> Data Sources.
2. Select the data source from the Data Source drop down box.
3. Write your SELECT query in the SQL editor and Click the Run SQL button.
4. The data will be returned to the QueryResults worksheet. Rename this worksheet if you wish to keep the data (so that it will not be overwritten the next time you run a query).
What if I want to manipulate the data in Excel and run SQL against it ?
You can do this easily in QueryCell. Once you have the data you need in Excel simply select ‘- Excel Data Regions -‘ from the Data Sources drop down box.
You can refresh the regions by clicking the refesh icon above the Regions Grid.
You are then able to run SQL queries against the Excel data regions as though they were tables in a relational database.