About exchanging data between Excel and Access

ShowWorking with Microsoft Access data in Microsoft Excel

Copy Access records to Excel

 Note   Use these methods when you don't need to refresh (refresh: To update data from an external data source. Each time you refresh data, you see the most recent version of the information in the database, including any changes that were made to the data.) the data in Excel every time the Access database changes.

Bring refreshable Access data into Excel

If you want to refresh the data in the worksheet when the Access database changes — for example, to update an Excel summary that you distribute every month so that it contains the current month's data — you can create either a query or an Office Data Connection file to bring the data into Excel. Create a query if you need to retrieve data from more than one table, or think you might need to change the scope of the retrieved data. Use an Office Data Connection file if you want data from only one table in the database and you want to retrieve all of the data in the table. You can return the data to Excel as an external data range (external data range: A range of data that is brought into a worksheet but that originates outside of Excel, such as in a database or text file. In Excel, you can format the data or use it in calculations as you would any other data.) or a PivotTable report (PivotTable report: An interactive, crosstabulated Excel report that summarizes and analyzes data, such as database records, from various sources, including ones that are external to Excel.), both of which can be refreshed.

ShowUsing Access to manage Excel data

Link Excel data into an Access database     You can link an Excel range into an Access database as a table (table: A collection of data about a particular subject that is stored in records (rows) and fields (columns).). Use this approach when you plan to continue maintaining the range in Excel but also want it to be available from within Access. You can view data in the linked Excel range from within the Access database. You create this type of link from within the Access database, not from Excel. For more information, see Access Help.

Import Excel data into an Access database     If you're working in Access and want to copy data from an Excel workbook into your database, you can import the data into Access. Use this method to bring a copy of a small amount of data that you intend to continue maintaining in Excel into an existing Access database, without having to retype the data.

Convert an Excel range to an Access database     If you have a large Excel range that you want to move permanently to an Access database, in order to take advantage of the Access data management capabilities, security, or multiuser capabilities, you can convert the data from Excel to an Access database. Use this method when you want to move the data from Excel into Access and use and maintain the data in Access from then on.

Create an Access report from Excel data     If you are familiar with designing Access reports and want to summarize and organize your Excel data in this type of report, you can create an Access report from the data in your Excel range. For more information about designing and using Access reports, refer to Access Help.

Applies to:
Excel 2003