Connecting to Access Data from Excel

Connect to Access Data from Excel

To bring refreshable Access data into Excel, you can create a connection, to the Access database and retrieve all of the data from a table or query. The main benefit of connecting to Access data is that you can periodically analyse this data in Excel without repeatedly copying or exporting the data from Access. After you connect to the data, you can also automatically refresh (or update) your Excel workbooks from the original Access database whenever the database is updated with new information. For example, you may want to update an Excel summary budget report that you distribute every month so that it contains the current month's data.

1. From Excel window, click the cell where you want to put the data from the Access database.
2. Click on the Data tab, in the Get External Data group, click From Access.
3. In the Look in list, locate and double-click the Access database that you want to import.
4. In the Select Table dialog box, click the table or query that you want to import, and then click Ok.
5. In the Import Data dialog box, do the following:
1. Under Select how you want to view this data, do one of the following:
1. To view the data as a table, select Table.
2. To view the data as a PivotTable report, select PivotTable report.
3. To view the data as a PivotChart and PivotTable report, select PivotChart and PivotTable report.
2. Under Where do you want to put the data? Do one of the following:
1. To return the data to the location that you selected, click Existing worksheet.
2. To return the data to the upper-left corner of the new worksheet, click New worksheet.
6. Click Ok.

 

Copy Access Data into Excel

From Microsoft Access, you can copy data from a datasheet view and then paste the data into an Microsoft Excel worksheet. Additionally, you can take the following steps to copy data from Microsoft Access to Microsoft Excel:

1. Start Access, and then open the table, query, or form that contains the records that you want to copy.
2. Click on the Home tab, click View, and then click Datasheet View.
3. Select the records that you want to copy. If you want to select specific columns, drag across adjacent column headings.

4. On the Home tab, in the Clipboard group, click Copy.
5. Start Excel, and then open the worksheet that you want to paste the data into.
6. Click in the upper-left corner of the worksheet area where you want the first field name to appear.
7. To ensure that the copied records do not replace existing records, make sure that the worksheet has no data below or to the right of the cell that you click.
8. Click on the Home tab, in the Clipboard group, click Paste.