There are two ways by which we can extract data from Ms-Access into Excel. First one is through Import External Data option under the data menu.
The second is through Pivot table wizard (when we create Pivot Table Report this wizard comes up). The second method is discussed below.
If you want to extract data from using the second method first you need to create and ODBC connection with the Ms-Access database from which you want to extract the data.
1. Open control panel dialog box.
2. Double click on ODBC from control panel options as shown below.
3. The following dialog box will come up as you click on ODBC option. As you can see we have currently three(dBASE, Excel Files, MS Access Database) ODBC connections. By default User DSN tab is selected. There is another tab System DSN. Leave the rest of tabs for now. You should be aware whether you are going to
access this connection on same machine on which you are creating new ODBC connection or from some other machine. If you are going to access the ODBC connection on the same machine then you can select User DSN, or if you are going to access from other machine then you can select System DSN option. In both case options are same to create the ODBC connection.
4. Click on add button to add new connection. The following window will come up on your screen. Select "Microsoft Access Driver(*.mdb) from the given options.
5. Click on finish button, another dialog box will come up on your screen as shown below. Add data source name(Northwind Database in my case) by which you can idenify the database uniquely. You can also add little description or you can leave it blank.
6. Now click on select button, another dialog box will come up on your screen as shown below. You can select Drives, Folder to specify the location where your data base is stored. Select the database name and then click on ok button.
7. Now in the following dialog box you can see that there are four options now. Last one is Northwind Database that we just added. Click on ok button to complete the process. Now we had created ODBC connection. Next step is to extract data into excel through pivot Table Report.
Select Pivot table and pivot chart report option from data menu as shown in the following dialog box.
Once you select pivot table and pivot chart report option from data menu following pivot table wizard will come up on your screen. As you can see first you have to specify where you data is stored. Is is store in excel sheet? Is it store at some external location? Is it store at multiple location? The last option is Another pivotTable report or pivot tablechart report. This option is
disable because currently we dont have any pivot table or chart report in the opened excel workbook. We know our data is stored in Ms-Access database named Northwind. So in this case i had selected second option. Next you should specify whether you want to create a pivottable report or pivot chart report(with pivot table report). So you can select appropriate option.
Click on next button. The following dialog box will come up on your screen. Click on get data option.
The following dialog box will come up. By default Database tab is selected. Next tab is queries tab, by clicking on this tab you can see all the queries that are stored already in Microsoft query. Next tab is OLAP cubes leave it for now.
As you can see under the database tab option, there are four database options. These options are same as we see in the ODBC dialog box. We created the Northwind database last time. So i selected this one.
After selecting the database from the choose data source option click on ok button.
