To load data from Access into Excel, you can copy data from an Access data table and paste it into an Excel worksheet, connect to an Access database from an Excel worksheet, or export Access data to an Excel worksheet.
To load data from Excel into Access, you can copy data from an Excel worksheet and paste it into an Access data table, import an Excel worksheet into an Access table, or link it from an Access table to an Excel worksheet.
"Import" has different meanings in Excel and Access. In Excel, importing refers to establishing a refreshable permanent data connection. In Access, importing means loading data into Access once without establishing a data connection.
You cannot save an Excel workbook as an Access database. Neither Excel nor Access provides the function of creating an Access database using Excel data.
-
Using Access data in Excel
If the data is in Access, but you want to use it in Excel: Operation: Nature of Exchange:
Temporarily copy Access data to Excel.
Regularly export Access data to Excel.
Connect from Excel to access data persistence
Using Excel data in Access
If the data is in Excel, but you want to use it in Access: Operation: Nature of Exchange:
Temporarily copy Excel data into Access.
Import Excel data into Access on a regular basis.
Permanently link to Excel data from Access.
-
Using Access data in Excel
You may want to use Access data in an Excel workbook to take advantage of data analysis and charting functions, flexibility in data arrangement and layout, or other functions that are not available in Access.
Copy Access data to Excel
You can copy data from datasheet view in Access, and then paste the data into an Excel worksheet.
Start Access, and then open the table, query, or form that contains the record you want to copy.
On the Start Page tab, click View, and then click Datasheet View.
Select the record to copy.
If you want to select a specific column, drag between adjacent column headings.
On the Home tab, in the Clipboard group, click Copy.
Start Excel, and then open the worksheet where you want to paste the data.
Click the upper left corner of the worksheet area where you want to display the first field name.
To ensure that the copied record will not replace the existing record, make sure that there is no data below and to the right of the cell clicked in the worksheet.
On the Home tab, in the Clipboard group, click Paste.
Export Access data to Excel
By using the export wizard in Access, you can export an Access database object (such as a table, query or form) or a record selected in a view to an Excel worksheet. When you perform an export operation, you can save the details for future use, and even make a plan to make the export operation run automatically at specified intervals.
The following are common situations when you need to export data from Access to Excel:
Your department or workgroup uses both Access and Excel when processing data. You store data in an Access database, but you use Excel to analyze the data and distribute the analysis results. Your team currently exports data to Excel when needed, but you want to make this process more efficient.
You use Access most of the time, but your manager prefers to view reports in Excel. You want to copy data to Excel regularly, but you want to finish the process automatically to save yourself time.
For more information about exporting data from Access to Excel, please refer to the Access Help System.
Connect to access data from Excel
To load refreshable Access data into Excel, you can create a connection to an Access database, which is usually stored in an Office data connection file (. Odc) and retrieve all the data in the table or query. The main advantage of connecting to Access data is that you can analyze these data regularly in Excel without repeatedly copying or exporting data from Access. After connecting to Access data, when the original Access database is updated with new information, you can also automatically refresh (or update) the Excel workbook containing the data in the database. For example, you may want to update the Excel budget summary report distributed every month to include the data of the current month.
Click the cell where you want to store data in the Access database.
On the Data tab, in the Get External Data group, click From Access.
In the Look in list, locate and double-click the Access database that you want to import.
In the Select Data Source dialog box, click the table or query you want to import, and then click OK.
In the Import Data dialog box, do the following:
Under Choose how to display data, do one of the following:
Stored as a table (table: a collection of data about a specific topic, such as records (rows) and fields (columns). ), please select "Table".
Use PivotTable (PivotTable: an interactive cross-table Excel report) to summarize and analyze data (such as database records) from multiple sources (including external data of Excel). ), please select PivotTable.
Use PivotChart (PivotChart: A chart that provides interactive data analysis, similar to a PivotTable report. You can change the data view, view different levels of detail data, or reorganize the layout of the chart by dragging a field and showing or hiding items in the field. ) and PivotTable, please select PivotChart and PivotTable.
Or click Properties to set refresh, format, and layout options for the imported data, and then click OK.
Under Data Storage Location, do one of the following:
To return the data to the selected location, click Existing Sheet.
To return the data to the upper left corner of the new worksheet, click New Worksheet.
Click "OK".
Excel places the external data range in the location you specify.
For more information about connecting data, see Connecting (Importing) External Data.
Using Excel data in Access
You may want to use Excel data in an Access database to take advantage of the data management, security, or multi-user features of Access. There are many useful functions in Access, two of which are especially useful for users who use Excel data:
Report If you are familiar with the design of an Access report and want to summarize and organize Excel data in this form, you can create an Access report. For example, you can create more flexible reports, such as grouping and summarizing reports, printing labels and graphical reports.
Forms If you want to use forms to find or display data in Excel, you can create an Access form. For example, you can create an Access form to display fields in a different order from the columns in a worksheet, or to view long rows of data on one screen in a more convenient way.
For more information about using Access forms and reports, see the Access Help System.
Copy Excel data into Access
You can copy the data in the Excel worksheet view, and then paste the data into an Access datasheet.
Note If you paste data from multiple fields in a worksheet into a datasheet, make sure that these columns match the order of the data to be copied. When you paste data from multiple fields into a form, Access pastes the data into fields with the same name as the source fields, regardless of their order in the form. If the data you want to copy contains fields that don't exist in the target form, Access will ask you if you want to paste only fields with matching names. If there are no fields with matching names, Access will paste the fields according to the tab order of the target form, which may not be the desired order. If the source field name is different from the target field name, you may need to paste the data into a datasheet instead of a form.
Start Excel, and then open the worksheet that contains the data you want to copy.
Select the rows to copy.
On the Home tab, in the Clipboard group, click Copy.
Start Access, and then open the table, query, or form where you want to paste rows.
On the Datasheet tab, click View, and then click Datasheet View.
Please do one of the following:
To replace records, select them, and then on the Home tab, in the Clipboard group, click Paste.
To append data as a new record, on the Start Page tab, in the Clipboard group, click Paste Append on the Edit menu.
Import Excel data into Access
To store data in Excel in an Access database, and then use and maintain the data in Access, you can import the data. When you import data, Access stores the data in a new table or an existing table without changing the data in Excel. Only one worksheet can be imported at a time when importing. To import data from multiple worksheets, repeat the import operation for each worksheet.
The following are common situations when you need to import Excel data into Access:
You use Excel most of the time, but you will use Access to process these data in the future. You want to move the data in the Excel worksheet to one or more new Access databases.
Your department or workgroup uses Access, but sometimes you will receive data in Excel format, which must be merged into the Access database. You want to import Excel worksheets into the database when you receive them.
You use Access to manage data, but the weekly report sent by others in the workgroup is an Excel workbook. You want to simplify the import process to ensure that data can be imported into the database at certain times of the week.
For more information about importing data from Excel into Access, please refer to the Access Help System.
Link to Excel data from Access
You can link an Excel range to an Access database as a table. You can use this method if you plan to continue to maintain the range in Excel, but you want to be able to Access the range from Access. You should create such a link from an Access database instead of Excel.
When you link to an Excel worksheet or named range, Access creates a new table and links it to the source cell. Any changes made to the source cell in Excel will be reflected in the linked table. However, you cannot edit the contents of the corresponding table in Access. If you want to add, edit or delete data, you must make changes in the source file.
The following are common situations where you need to link from Access to Excel worksheets:
You want to save the data in an Excel worksheet, but you should be able to use the powerful query and report functions of Access.
Your department or workgroup uses Access, but the external source data that needs to be processed is in the Excel worksheet. You don't want to maintain a copy of external data, but you need to be able to use it in Access.