How to Import Data into Access 2016 

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 IDClient First NameClient Last NameAddress
111BobLee21-Bay street Canada
222JamesFord32-Rome street Italy
333NancySilva22-Tokyo street Japan
444MariaGreen15-Rio street Brazil
555JackMay25-Oxford street UK

 

Your table in Excel should look like this:

 

Table in Excel 2016

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.

 

External Data tab in Access 2016

 

(2) Press on the ‘New Data Source’ icon.

 

New Data Source in Access 2016

 

(3) From the drop-down menu, select ‘From File.’

 

From File Access 2016

 

(4) Select ‘Excel.’

 

How to import data into Access 2016 from Excel

 

(5) Click on the ‘Browse…’ button.

 

Browse button in Access 2016

 

(6) Locate your Excel file that contains the table with your client information, and then press Open.

 

Import Data into Access 2016

 

(7) Press OK.

 

Import Data into MS Access

 

(8) Since our first row contains column headings, check the box: First Row Contains Column Headings. Then press Next.

 

First Row Contains Column Headings in Access 2016

 

(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.

 

Import Spreadsheet wizard in Access 2016

 

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.’

 

Import Data into MS Access 2016 

 

(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.

 

How to Import Data into Access 2016 

 

(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.

 

Import to Table in Access 2016

 

(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.

 

Save import steps in Access 2016

 

(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.

 

Open Table in Access 2016

 

This is how your imported table would look you:

 

Imported table in Access 2016