Access is a database management system (DBMS) suitable for capturing, storing, querying, and sharing data, while Excel is a spreadsheet application best for calculations, analysis, and visualization of data.
You can transfer Excel data to Access and convert your data to relational tables, allowing you to use Excel and Access together.
Access has an Import Spreadsheet Wizard that lets you import data from one or multiple Excel worksheets into its tables. Because a single Access table can contain data from multiple Excel worksheets, the program helps aggregate and analyze large amounts of data.
In this tutorial, I will show you how to transfer Excel data to Access.
Transfer Excel Data to Access Using the Import Spreadsheet Wizard
Suppose you have four worksheets in a workbook containing data on four brands of 3D printer accessories and want to transfer that data into one table in Access.
You can use the three steps below to transfer the Excel data to Access.
Step #1: Prepare and Clean the Excel Data
Preparing and cleaning your Excel data before transferring it to Access is considered best practice.
Here are some things you can do to prepare and clean your Excel data:
- Use Excel’s TRIM function to remove leading, trailing, and multiple embedded spaces.
- Find and fix spelling and punctuation errors.
- Split cells that contain multiple values in one cell to separate columns. For instance, split a cell that contains first name and last name into two columns containing first name and last name.
- Delete non-printing characters.
- Remove duplicate fields and duplicate rows.
- Ensure that columns of data do not contain mixed formats, especially dates formatted as numbers or numbers formatted as text.
- Ensure your data is in a tabular format with headers for each column.
Step #2: Create a New Access Database or Open an Existing One
Open Microsoft Access and create a new database or open an existing one where you want to import or transfer the data.
You can create a new Access database using the steps below:
- Type the keyword ‘Access’ in the search box on the Windows taskbar and click the Access app on the list that appears.
- Click the ‘Blank database’ button on the Welcome screen.
- On the ‘Blank database’ screen, type the desired name for the database in the File Name box, click the ‘Browse’ button to find a location to put your database, and click the ‘Create’ button.
Access creates a new database with a default table named Table1, which you can rename and use to input data.
To open an existing database, launch Access, and on the Welcome screen, type the database name in the Search box or click it on the ‘Recent’ or ‘Pinned’ list if you pinned the database.
Note: Alternatively, if you know where the database is stored, open the folder and double-click the database to open it.
Step #3: Import Excel Data into Access
You can transfer Excel data to Access in the following three ways:
- Import Excel data into a new Access table. If the table you specify does not exist, Access will create it. Access might overwrite its contents with the imported data if the table you specify exists. Any changes you make to the Excel data source will not be reflected in the Access table.
- Import Excel data into an existing Access table. If the table you specify exists, Access will add the records to it. If it does not exist, Access will create it. Any changes you make to the Excel data source will not be reflected in the Access table.
- Link Excel data to Acess. Access will create a table that will maintain a link to the source data in Excel. Any changes you make to the source data in Excel will be reflected in the linked table. However, you cannot modify the Excel data source from within Access.
Note: If you encounter error messages during the data transfer process, carefully read them to understand what you need to correct in your Excel file.
Import Excel Data into a New Access Table
Sometimes, you may need to transfer Excel data to a new table in Access.
You can use the steps below to import Excel data into a new Access table:
- Open an Access database following the instructions in Step #2.
- Click the ‘External Data’ tab, open the ‘New Data Source’ drop-down, hover over the ‘From File’ option, and click ‘Excel’ on the submenu.
- On the ‘Get External Data—Excel Spreadsheet’ dialog box that appears, do the following:
- Click the ‘Browse’ button and find where the workbook containing the Excel data you want to import is stored; select the workbook and open it.
- Select the ‘Import the source data into a new table in the current database’ option.
- Click OK.
- On the first screen of the Import Spreadsheet Wizard, specify the worksheet you want to import data from on the ‘Show Worksheets’ list box and click Next. In this example, we select the ‘Apolox’ worksheet.
Note: The ‘Show Worksheets’ option is unavailable if your workbook has only one worksheet.
- On the second screen of the Import Spreadsheet Wizard, check the ‘First Row Contains Column Headings’ option and click Next.
- On the third screen of the Import Spreadsheet Wizard, specify information about each field you are importing by selecting the field in the area below by clicking its header and modifying the field’s information in the ‘Field Options’ area. Click Next.
Note: Please see the next section, ‘How to Choose the Best Data Type,’ for tips on the best data type for each field you import.
- On the fourth screen of the Import Spreadsheet Wizard, define a primary key for the new table by letting Access add a primary key or choosing your primary key from the table fields and clicking Next. In this example, we decide to let Access add a primary key.
Note: A primary key is a field or a combination of fields that uniquely identifies each record in an Access table.
- On the fifth screen of the Import Spreadsheet Wizard, enter the desired name for the table on the ‘Import to Table’ box and click Finish.
Note: If you want to save the import steps above for future use, select the ‘Save import steps’ option on the ‘Get External Data—Excel Spreadsheet’ dialog box that appears and click Close.
Note: Saving the import steps allows you to quickly repeat the import operation without using the Import Spreadsheet Wizard. This action is helpful if your work requires regularly importing data from the same worksheet.
Access lists the new table containing data from Excel on the Access objects pane on the left of the Access window.
How to Choose the Best Data Type for Imported Fields
During the import operation in Access, you should choose the best data type for each field to prevent conversion errors.
The table below summarizes how Excel data types are converted to Access data types when importing data from Excel to Access. It also offers tips on the best data type to select for each imported field in the Fields Options area of the Import Spreadsheet Wizard.
| Excel data type | Access data type | Comments | Tip |
|---|---|---|---|
| Text | Text, Memo | The Access Text data type can store alphanumeric data up to 255 characters, while the Access Memo data type can store up to 65,535 characters. | Choose Memo to prevent truncating any data. |
| Number, Percentage, Fraction, Scientific | Number | Access has a Number data type that varies based on a Field size property, such as Byte, Integer, Long Integer, Single, Double, or Decimal. | Choose Double to avoid any data conversion errors. |
| Date | Date | Access and Excel store dates using the same serial number. However, Access has a larger date range, from -657,434 (January 1, 100 A.D.) to 2,958,465 (December 31, 9999 A.D.). | Choose Date. |
| Time | Time | Access and Excel both store time values using the same data type. | Choose Time, which is usually the default. |
| Currency, Accounting | Currency | In Access, the Currency data type stores data as 8-byte numbers with precision to four decimal places. It is used to store financial data and prevent rounding of values. | Choose Currency, which is usually the default. |
| Boolean | Yes/No | Access uses -1 for Yes and 0 for No, while Excel uses 1 for TRUE and 0 for FALSE. | Choose Yes or No, and the underlying values will be automatically converted. |
| Hyperlink | Hyperlink | A hyperlink in Excel or Access contains a URL or Web address you can click to follow. | Choose Hyperlink. Otherwise, Access may use the Text data type by default. |
Import Excel Data into an Existing Access Table
Sometimes, you may need to transfer Excel data to an existing table in Access.
Note: Before you transfer the Excel data, ensure that the fields from the Excel data are correctly mapped to the fields in the Access table.
You can use the steps below to import Excel data into an existing table in Access:
- Open an Access database following the instructions in Step #2.
- Click the ‘External Data’ tab, open the ‘New Data Source’ drop-down, hover over the ‘From File’ option, and click ‘Excel’ on the submenu.
- On the ‘Get External Data – Excel Spreadsheet’ dialog box that appears, do the following:
- Click the ‘Browse’ button and find where the workbook containing the Excel data you want to import is stored; select the workbook and open it.
- Select the ‘Append a copy of the records to the table’ option, open the drop-down menu, and select the Access table to which you want to transfer the Excel data. In this case, we choose the ‘3D Printer Accessories’ table.
- Click OK.
Note: The ‘Append a copy of the records to the table’ option only becomes available if you have at least one table in the Access database.
- On the first screen of the Import Spreadsheet Wizard, specify the worksheet you want to import data from on the ‘Show Worksheets’ list box and click Next. In this example, we select the ‘ABSPro’ worksheet.
- On the second screen of the Import Spreadsheet Wizard, click Next.
Notice that the ‘First Row Contains Column Headings’ option is unavailable because you are appending the Excel data to an existing Access table.
- Click Finish on the fourth screen of the Import Spreadsheet Wizard.
Note: If you want to save the import steps above for future use, select the ‘Save import steps’ option on the ‘Get External Data—Excel Spreadsheet’ dialog box that appears and click Close.
Note: Saving the import steps allows you to quickly repeat the import operation without using the Import Spreadsheet Wizard. This action is helpful if you regularly import data from the same worksheet.
If you open the Access table, you will notice that the steps above have appended new Excel data to it:
You can repeat the above steps to append the data from other worksheets to the Acces table.
Transfer Excel Data into Access By Creating a Linked Access Table
Sometimes, you may need to create an Access table linked to your Excel data so that any changes you make in the Excel data are immediately reflected in the linked Access table.
You can use the steps below to create an Access table that is linked to your Excel data:
- Open an Access database following the instructions in Step #2.
- Click the ‘External Data’ tab, open the ‘New Data Source’ drop-down, hover over the ‘From File’ option, and click ‘Excel’ on the submenu.
- On the ‘Get External Data – Excel Spreadsheet’ dialog box that appears, do the following:
- Click the ‘Browse’ button and find where the workbook containing the Excel data you want to import is stored; select the workbook and open it.
- Select the ‘Link to the data source by creating a linked table ‘ option.
- Click OK.
- On the first screen of the Import Spreadsheet Wizard, specify the worksheet you want to link to on the ‘Show Worksheets’ list box and click Next. In this example, we select the ‘Apolox’ worksheet.
- On the second screen of the Import Spreadsheet Wizard, check the ‘First Row Contains Column Headings’ option and click Next.
- On the third screen of the Import Spreadsheet Wizard, enter the desired name for the linked Acces table on the ‘Linked Table Name’ box and click Finish.
If the linking operation is successful, Excel displays a message box indicating it has finished linking the Access table to the Excel data.
When you click OK on the message box, Access displays the linked Access table on the Access Objects pane on the left of the Access window.
If you make any changes to the original Excel data, the changes will be reflected in the linked Access table. However, any changes you make to the data in the linked Access table will not be reflected in the original Excel data.
Also read: Excel vs Access – Ultimate Comparison Guide
Benefits of Transferring Excel Data to Access
Transferring Excel data to Access can give you several advantages, particularly when working with large datasets or complex data management tasks.
Here are some benefits of importing Excel data into Access:
- In Access, you can create relationships between different tables, which helps manage large datasets that need to be connected meaningfully. For example, you can create relationships between tables and then use queries to retrieve information about different subjects.
- Access maintains data integrity by enforcing data types, relationships, and rules, reducing entry errors.
- Access can efficiently manage larger datasets, reducing performance issues as your data grows compared to Excel.
- Access provides a wide range of customizable reporting tools, enabling you to create detailed and professional reports.
- In Access, you can design user-friendly forms for data entry, which can standardize the way data is collected and minimize errors.
In this tutorial, I showed you how to transfer Excel data to Access. I hope you found the tutorial helpful.
Other Excel articles you may also like:
