How to import your data into Access in one click

Tired of repeating the same steps when importing your data into Access. Why not create a button to import your data into Access in one click?

You can even create a button in Access to import multiple files using a single click!

In this tutorial, I’ll review the steps to accomplish these types of tasks.

Import your data into Access in one click

Let’s say that you have the following data about your clients. This data is stored in an Excel file. Your goal is to import that data into Access.

 

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

 

To make your life easier, you decided to create a button in Access in order to import that type of data on an ongoing basis.

So how would you accomplish this goal?

Here are the steps that you can follow using MS Access 2016 (similar principles would apply for previous versions of Access).

Steps to import your data into Access using a single click

(1) Firstly, you’ll need to import the above table into Access for the first time.

To do that, go to the External Data tab:

 

External Data tab in Access 2016

 

(2) Click 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, and locate your Excel file.

 

Browse button in Access 2016

 

(6) Proceed with the menus to import your file (you can check the following source for additional information about importing your file into Access).

At the final stage of importing your data into Access, you’ll be presented with a choice to save your import steps:

 

Save import steps in Access

 

(7) Go ahead and select the option to ‘Save import steps’ by checking the box as below.

You can then type the name that will represent your import steps (in the ‘Save as’ box). Here, I chose the name of “Import-Clients table”

(8) Once you’re done, click on ‘Save Import’

 

How to import your data into Access in one click

 

(9) To access your saved imports at anytime, simply go to the External Data tab, and then click on Saved Imports:

 

How to import your data into MS Access in one click

 

(10) Under the Manage Data Tasks, you’ll be able to change the name of your saved import steps. To do so, simply click on the name saved (in our case it is “Import-Clients table”) and then type your desired name:

 

Manage Data Tasks - MS Access

 

(11) You can also modify the directory location from which you wish to import your files. Simply click on the directory path below, and then modify it according to your needs:

 

Import data into Access

Creating a button to import your data into Access

(1) Now, let’s create a form, where you’ll be able to place a button to import your data into Access using one click.

First, go to the Create tab. Then, press on the Form Design icon:

 

Form Design - MS Access 2016

 

(2) A default form will now appear on your screen (this is the Design View, where you will be able to add your button):

 

Form Design in MS Access

 

(3) Press on the “button icon”

Then, press anywhere on the form to add your button.

Click on Cancel as the ‘Command Button Wizard’ will not be needed.

 

Add a button in Access

 

(4) On the right-hand-side of the screen you will see the Property Sheet.

In the Property Sheet, you can set your button to import your data “On Click”

To do that, press on the 3 dots (“…”) that are located under the Property Sheet:

 

Property Sheet - MS Access

 

(5) From the Choose Builder box, select the ‘Code Builder’ option, and then press OK:

 

Code Builder in Access

 

(6) This is how the default syntax would look like when you open the ‘Code Builder’

 

VBA Access

 

(7) Next, you’ll need to add the following syntax as below:

 

DoCmd.RunSavedImportExport ("Import-Clients table")

 

This command will ensure that the saved import steps (that we named “Import-Clients table”) will run if you click the button:

 

How to import your data into Access 2016 in one click

 

(8) Click on the Saved icon, and Save your work as “Import-Clients.” You can now close the Code Builder.

 

Save in the Code Builder - MS Access

 

(9) In the Design View, you can rename your button by typing your desired name on the button. Here, I chose to rename the button to ‘Import Clients.’

To switch from the Design View (where you can add buttons and edit your form) to the Form View (where you can click on your button in order to import your data/files into Access), press on the ‘View’ icon under the Design tab. From the drop-down menu, choose the ‘Form View.’

 

Form View

 

(10) Now delete all the existing records in the Clients table. This will allow you to test that the button is working.

While in the Form View, press on the button ‘Import Clients.’

This will automatically import your client data (and store it in the Clients table) in a single click!

 

Import data into Access

Creating a button to import multiple files into Access

You probably wonder if you can apply the same method as above to import multiple files into Access using a single click.

Sure thing.

To illustrate this concept, let’s say that you decided to save the import steps for another set of data. For example, ‘Sales’ data.

As before, check the box of ‘Save import steps.’ Here, I chose to save the import steps as ‘Import-Sales.’

 

Creating a button to import multiple files into Access

 

(1) Go back to the Code Builder that contained the previous syntax for importing your client data:

 

VBA in MS Access

 

(2) Now, add the following syntax as below:

 

DoCmd.RunSavedImportExport ("Import-Sales")

 

Adding this code will ensure that your “Import-Sales” data is also imported when clicking on the button:

 

DoCmd.RunSavedImportExport

 

(3) You can rename your button in the previous form you created while in the Design View. Here, I renamed the button to “Import All Files”

Now, switch back to the Form View so that you can press the button to import your data for both the Clients and Sales data-sets in one shot:

 

Form View in MS Access

 

(4) Finally, while in the Form View, press on the button “Import All Files”

 

Click button in Access

 

Congratulation, you just imported your data into Access in one click!

In the final section, I am going to show you how to create a macro that will open the Saved Imports Dialogue box.

Create a macro that will open the Saved Imports Dialogue box

(1) The first thing that you’ll need to do, is to go to the Create tab and then press on the Macro icon:

 

Macro in MS Access

 

(2) Then, press on the “Down” arrow to see the list of the available options:

 

Add macro in MS Access

 

(3) Select the option of RunMenuCommand from the drop-down list:

 

RunMenuCommand

 

(4) Then, select the SavedImports option from the drop-down list:

 

SavedImports macro

 

(5) Now you’ll need to save your macro. Here, I chose to save the macro as “Saved_Imports”

 

Save Macro in MS Access

 

(6) Finally, create a button that will open the Saved Imports dialogue box on click.

To do that, you’ll need to create a form and place a button on it:

 

Form in MS Access

 

(7) Under the Property Sheet, select your “Saved_Imports” macro from the drop-down list (associated with the ‘On Click’ option):

 

Property Sheet - MS Access

 

(8) Now, go back to the Form View and then click your button.

The Saved Imports dialogue box will appear on your screen:

 

Saved Imports dialogue box