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 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|
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 2013 (similar principles would apply for other 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:
(2) Click on the Excel icon:
(3) Click on the Browse button, and locate your Excel file.
(4) 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. Go ahead and select the option to ‘Save import steps’ by checking the box:
(5) 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, which will be used in the VBA to import the clients file (as you would later see).
Once you’re done, click on ‘Save Import’ button:
(6) To access your saved imports at anytime, simply go to the External Data tab, and then click on Saved Imports:
(7) Under the Manage Data Tasks box, you’ll be able to change the name of your saved import steps, and/or the directory from which you wish to import your files.
For the time being, don’t change anything, and close the Manage Data Tasks box.
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 a single click.
First, go to the Create tab. Then, press on the Form Design icon:
(2) A default form will now appear on your screen (this is the Design View, where you will be able to add your button).
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
(3) 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:
(4) From the Choose Builder box, select the ‘Code Builder’ option, and then press OK:
(5) This is how the default syntax would look like when you open the ‘Code Builder’
(6) Next, you’ll need to 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-Clients) will run if you click the button:
(7) Click on the Save icon, and then save your work as “Import File”
Finally, press OK and then close the Code Builder.
(8) 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.’
(9) 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 ‘Import Clients’ button.
This will automatically import your client data (and store it in the Clients table) using a single click!
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 ‘Sales’ data.
As before, check the box of ‘Save import steps.’ Here, I chose to save the import steps as ‘Import-Sales.’
(1) Go back to the Code Builder that contained the previous syntax for importing your client data:
(2) Now, add the following syntax as below:
Adding this code will ensure that your “Import-Sales” data is also imported when clicking on the button:
(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:
(4) Finally, while in the Form View, press on the button “Import All Files”
Congratulation, you just imported your data into Access in one click!
In the final section of this tutorial, I’m 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:
(2) Next, press on the “Down” arrow to see the list of the available options:
(3) Select the option of RunMenuCommand from the drop-down list:
(4) Then, select the SavedImports option from the drop-down list:
(5) Now you’ll need to save your macro. Here, I chose to save the macro as “Saved_Imports”
(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:
(7) Under the Property Sheet, select your “Saved_Imports” macro from the drop-down list (associated with the ‘On Click’ option):
(8) Now, go back to the Form View and then click on your button.
The Saved Imports dialogue box will appear on your screen: