How to Import your Data into Access in One Click

Tired of repeating the same steps when importing your data into Access? If that’s the case, I’ll show you a way to import your data into Access in one click.

Steps to Import your Data into Access using One Click

Step 1: Prepare the Data to be Imported

To start, prepare the data to be imported into Access.

For example, let’s say that you have the following data about people:

First_NameLast_NameAge
BobLee23
JamesFord57
NancySilva34
MariaGreen28
JackMay45

You can then store the above data in an Excel file (where the Excel file name is “People“):

Excel Table

The goal is to import the above data into Access.

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

Step 2: Import the File into Access

Next, you’ll need to import the Excel file into Access for the first time (in subsequent times, you’ll be able to use a button to import the data as you’ll see later).

To begin, go to the External Data tab:

External Data

Click on the New Data Source icon, and then select From File:

How to Import your Data into Access in One ClickChoose the Excel option:

Import your Data into Access in One Click

Click on the Browse… button:

Browse button

Locate your Excel file, and then press Open:

Locate Excel File on Computer

Press OK:

Import your Data into Access in One Click

Since our first row contains column headings, make sure that the First Row Contains Column Headings is checked. Then press Next:

How to Import your Data into Access

You’ll now see the following display, where you can change the data type for each of your columns/fields. For now, press Next:

Import data into Access

You may now choose to ‘Let Access add primary key’ and then press Next:

Import data into Access

Type a name for the new table to be created in Access. This table will contain the imported data from Excel.

For example, type ‘People‘ for the new table name, and then press Finish:

Import data into Access

Select the option to Save import steps by checking the box:

Import data into Access

You can then type a name for the import steps. Here, I typed the name of Import-People, which will be used in the VBA to import the ‘People’ file (as you would later see).

Once you’re done, click on the Save Import button:

How to Import your Data into Access in One Click

Your Excel file should now be imported into Access. You can view the imported data at anytime by double-clicking on the ‘People‘ table under the ‘All Access Objects‘ section:

All Access Objects

You will now see the imported data:

Table in Access

Next, delete all the existing records in the ‘People’ table. In the next step, you’ll see how to import the data back using a single button click.

This is how the ‘People‘ table would look like after you deleted the content:

Delete table in Access

Once you’re done, close the table.

Step 3: Create a Button to Import your Data into Access

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

First, go to the Create tab, and then press on the Form Design icon:

import data into Access

A default form will now appear on your screen. To add your button:

  • Press on the “button icon”
  • Then, press anywhere on the form to add your button
  • Finally, click on Cancel as the ‘Command Button Wizard’ will not be needed

How to Import your Data into Access in One Click

Right-click on the button, and then select Build Event…

Import data into Access

Select the Code Builder option, and then press OK:

Code Builder

This is the default syntax that you’ll see when you open the Code Builder:

VBA to import data into Access

Next, add the following syntax in between the existing lines (see below):

DoCmd.RunSavedImportExport ("Import-People")

This command will ensure that the saved import steps (that we named as Import-People) will run if you click the button:

How to Import your Data into Access in One Click

Click on the Save icon, and then save your work as “Import File”

How to Import your Data into Access

Finally, press OK, and then close the Code Builder.

Step 4: Press the Button to Import the Data

You’ll now need to switch to the Form View (where you can click your button in order to import your data/file into Access).

To switch to the Form View:

  • First, go to the Design tab
  • Then, press on the View icon
  • Finally, select the Form View option

Form View

While in the Form View, click on the button:

Click a button

This will automatically import your People data using a single click!

Table Example

Note that you may access your saved imports at anytime, by going to the External Data tab, and then clicking on Saved Imports:

How to Import your Data into Access in One Click

You can then change the name of your saved import steps, and/or the directory from which you wish to import your file.

For our purposes, don’t change anything, and close the box below.

How to Import your 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, the ‘Cars’ data.

As before, check the box of ‘Save import steps.’ Here, I saved the import steps as ‘Import-Cars.’

How to Import your Data into Access in One Click

Go back to the Code Builder that contained the previous syntax for importing the ‘People’ data:

VBA Example

Now, add the following syntax as below:

DoCmd.RunSavedImportExport ("Import-Cars")

This command will ensure that the ‘Cars’ data will also get imported when clicking the button:

VBA in Access

Switch back to the Form View so that you can press the button to import both the People and the Cars datasets in one shot:

How to Import your Data into Access in One Click

Finally, while in the Form View, click on the button to import all your files:

Example of button

In the final section of this tutorial, I’ll 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

To start, go to the Create tab and then press on the Macro icon:

Macro

Next, press on the “Down” arrow to see the list of all the available options:

How to Import your Data into Access in One Click

Select the option of RunMenuCommand from the drop-down list:

Import your Data into Access in One Click

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

SavedImports

Now you’ll need to save your macro. Here, I saved the macro as “Saved_Imports_Box”

SavedImports

 

Finally, create a button that will open the Saved Imports dialogue box.

To do so, you’ll need to create a form (or use an existing one) and place a button on it:

Button

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

Property Sheet

Now, go back to the Form View and then click on your button.

The Saved Imports dialogue box will appear on your screen:

Saved Imports