Need to import data into Access 2016? If so, in this post, I’ll show you the steps to accomplish this goal.
In particular, I’ll demonstrate how you can import your data from external sources such as Excel.
The data to be imported into Access 2016
Let’s say that you store the following information about your clients in Excel:
| Client ID | Client First Name | Client Last Name | Address |
| 111 | Bob | Lee | 21-Bay street Canada |
| 222 | James | Ford | 32-Rome street Italy |
| 333 | Nancy | Silva | 22-Tokyo street Japan |
| 444 | Maria | Green | 15-Rio street Brazil |
| 555 | Jack | May | 25-Oxford street UK |
Your table in Excel should look like this:
Steps to import data into Access 2016 from Excel
Here are the steps to import the above table into Access from Excel:
(1) Open MS Access, and then go to the ‘External Data’ tab.
(2) Press on the ‘New Data Source’ icon.
(3) From the drop-down menu, select ‘From File.’
(4) Select ‘Excel.’
(5) Click on the ‘Browse…’ button.
(6) Locate your Excel file that contains the table with your client information, and then press Open.
(7) Press OK.
(8) Since our first row contains column headings, check the box: First Row Contains Column Headings. Then press Next.
(9) You can change the data type for each of your fields/columns.
In our example, since the ‘Client ID’ field contains only numbers, change the Data Type to ‘Integer.’
To do so, select the column ‘Client ID’ and then select Integer (from the ‘Data Type’ drop-down menu).
Finally Press Next.
Since the other fields contain short texts, we will not change the data type for those fields. By default, those fields are already assigned with the data type of Short Text.
At times, you may get errors when importing your data into Access. One way to avoid those errors, is by selecting the data type of Short Text for each field when importing your data. Once the data is imported, you can apply different formatting.
(10) Optionally, you may select the option of ‘Choose my own primary key.’
(11) From the drop-down menu, select ‘Client ID’ to be your primary key. This primary key is a unique key that represents each of your clients. It will be used to link with other tables. Finally, press Next.
(12) Type ‘Clients’ in the box ‘Import to Table.’
The name of your new table that is imported from Excel will be ‘Clients.’
Press Finish.
(13) Press Close. Optionally, you may wish to check the box ‘Save import steps’ if you intend to repeat the same steps as above when importing your data from Excel.
(14) Congratulation, you just imported your ‘Clients’ table into Access. In order to open your imported table, double-click on your ‘Clients’ table under the ‘All Access Objects’ section.
This is how your imported table would look you:
