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:
You can then store the above data in an Excel file (where the Excel file name is “People“):
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:
Click on the New Data Source icon, and then select From File:
Choose the Excel option:
Click on the Browse… button:
Locate your Excel file, and then press Open:
Since our first row contains column headings, make sure that the First Row Contains Column Headings is checked. Then press Next:
You’ll now see the following display, where you can change the data type for each of your columns/fields. For now, press Next:
You may now choose to ‘Let Access add primary key’ and then press Next:
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:
Select the option to Save import steps by checking the box:
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:
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:
You will now see the imported data:
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:
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:
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
Right-click on the button, and then select Build Event…
Select the Code Builder option, and then press OK:
This is the default syntax that you’ll see when you open the Code Builder:
Next, add the following syntax in between the existing lines (see below):
This command will ensure that the saved import steps (that we named as Import-People) will run if you click the button:
Click on the Save icon, and then save your work as “Import File”
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
While in the Form View, click on the button:
This will automatically import your People data using a single click!
Note that you may access your saved imports at anytime, by going to the External Data tab, and then clicking on Saved Imports:
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.
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.
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.’
Go back to the Code Builder that contained the previous syntax for importing the ‘People’ data:
Now, add the following syntax as below:
This command will ensure that the ‘Cars’ data will also get imported when clicking the button:
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:
Finally, while in the Form View, click on the button to import all your files:
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:
Next, press on the “Down” arrow to see the list of all the available options:
Select the option of RunMenuCommand from the drop-down list:
Then, select the SavedImports option from the drop-down list:
Now you’ll need to save your macro. Here, I saved the macro as “Saved_Imports_Box”
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:
Under the Property Sheet, select the “Saved_Imports_Box” macro from the drop-down list (associated with the ‘On Click’ option):
Now, go back to the Form View and then click on your button.
The Saved Imports dialogue box will appear on your screen: