Do you need to create a specification name in Access? If so, in this post, I’ll show you how to create a specification name in Access 2016.
Note that similar principles apply to previous versions of Access.
So why create a specification name in Access?
Specification name can be used in a VBA to import a CSV/TXT file into Access using a click of a button.
Let’s now look at the steps to create a specification name.
Steps to Create a Specification Name in Access 2016
Let’s say that you have the following table and would like to import it into Access:
If you save the above as a CSV/TXT file, you can then create a specification name in Access to import this data.
Now suppose that you created an empty table in Access called “Names,” and that you’d like to import the data above into that empty table:
How would you then create a specification name in Access to import your data?
Creating a specification path in Access:
(1) First, go to the External Data tab in Access
(2) Then, press on the New Data Source icon
(3) Select the From File option from the drop-down list
(4) Choose the Text File option for CSV/TXT file types
(5) Locate your CSV file on your computer. You can use the Browse option to locate that file. You’ll also need to make sure that the File Name ends with “.csv” for a CSV file.
Once you located your CSV file, press OK to progress to the next screen:
(6) Choose the Delimited option for your CSV file, and then press Next:
(7) Select the Comma option, and then check the First Row Contains Field Names. Finally, press Next to move to the next screen:
(8) To create your Specification Name click on the Advanced… option:
(9) Now, click on the Save As… option:
(10) You can now type any name for your Specification Name:
(11) Here, I chose to type the name “ImportNames” to represent the specification name, which will be later used in the VBA.
Once you typed your desired name, press OK:
(12) Press OK again:
(13) Press Next:
(14) Select the option of Choose my own primary key, and then select the ‘ID’ column to be your primary key.
Then, press Next:
(15) Type “Names” as this is the name of your Access table, in which you want to import your data into.
Finally, click on Finish:
(16) Press Yes to overwrite your exiting “Names” table with the new data from the CSV file:
(17) Click on Close (optionally: you can save your import steps, by checking the Save import steps box):
(18) To test that your data was imported successfully from the CSV file, double-click on the table “Names” under the All Access Objects menu:
(19) Now let’s delete all the records under this table.
Wait a minute! You probably ask why would you do that?
In the subsequent section, I’m going to show you the steps to import those records back using a VBA (this will require the specification name we created earlier).
But before we proceed, delete all the records under the “Names” table, by selecting all the records (you can press CTRL+A on your keyboard to select all the records), then right-click, and finally select the option to Delete Record from the drop-down list:
Let’s create a simple MS Access Form and place a button on it.
You’ll be able to use that button to run the VBA, which will allow you to import all your data from the CSV file into your “Names” table.
Create a form
(1) First, go to the Create tab
(2) Then, press on the Form Design icon:
(3) Place a button on your form:
(4) You’ll now notice that the Command Button Wizard will appear on your screen.
Click on Cancel, as it will not be necessary to use this wizard:
Steps to create a VBA to import your data from the CSV file into Access
(1) Select the button you just placed on the form (in the Design View), and then perform the following on the Property Sheet, which should appear on the right-side of your screen (if you cannot see the Property Sheet, then press F4 to display that menu):
- For the On Click option, select the [Event Procedure] from the drop-down list
- Press on the 3 dots “…“
(2) This is the screen that you’ll see:
(3) Now, it’s time to write your VBA that will allow your to import the CSV data into the “Names” table, using a single click.
Here is the code that you’ll need to put in between the “Private Sub Command1_Click()” and the “End Sub.” You’ll need to make an adjustment to the “Path” variable based on the location where your CSV file is placed on your computer. Simply replace the location that I used in the “Path” variable, with the one that you currently use to store the CSV file:
Dim Path As String Path = "C:\Users\Doron E\Desktop\Specification name\Names" & ".csv" DoCmd.TransferText acImportDelim, "ImportNames", "Names", Path, True
This is how the VBA code would look in Access:
Let’s explain each component of this VBA code:
- Dim Path As String – we define the variable ‘Path’ as string, since that variable will contain a string of characters
- The variable Path will have the following structure:
Path = “Location where your CSV is placed” & “.csv”
- For the DoCmd.TransferText command:
- acImportDelim is used when importing a delimited CSV file
- “ImportNames” is the specification name we created earlier
- “Names” is the table name that will be used to store the imported data from the CSV file
- Path is the variable that contains the location of the CSV file
- True represents the fact that the data to be imported contains headings
Run the VBA to import your data
(1) Now save the form by pressing CTRL+S while still on the VBA screen.
Here, I chose to name the form as: “ImportNames”:
(2) Close your VBA screen, and then switch to the Form View. Under the Form View, you’ll be able to click on your button and import the data in the CSV file into the “Names” table.
To switch to the Form View, right-click on the tab name that contains the Form name “ImportNames.”
Then, from the drop-down list, select Form View:
(3) Close the “Names” table, and then click on the button to import your data.
Do not keep the “Names” table opened while you click on the button, otherwise you wouldn’t see the changes.
(4) The data from the CSV file should now be imported into Access. To see that it is indeed the case, double-click on the table “Names” under the All Access Objects.
You’ll now see all the imported records:
We have seen how to import your data in the CSV file into Access (same principles will apply for a TXT file), but what if you have an Excel file, and you’d like to import those results into Access using a button?
VBA to import data from Excel into Access
It is actually easier to import the data from an Excel file into Access, as it doesn’t require creating a specification name.
For simplicity, let’s use the same example as before. The only difference is that you’ll need to save the following table in the Excel format “.xlsx”
Below is the VBA code that you’ll need to use. And as before, you’ll need to modify the “Path” variable to the location where your Excel file is stored on your computer. Also notice that the Path suffix is now “.xlsx” to represent the Excel file/format:
Dim Path As String Path = "C:\Users\Doron E\Desktop\Specification name\Names" & ".xlsx" DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "Names", Path, True
Let’s now explain the DoCmd.TransferSpreadsheet command:
- acImport is used when importing data into Access
- acSpreadsheetTypeExcel12 is used when importing Excel spreadsheets
- “Names” is the table name that will be used to store the imported data from the Excel file
- Path is the variable that contains the location of the Excel file
- True indicates that the data to be imported contains headings
Finally, you may follow the same principles as we saw before, in order to apply the above VBA code whenever you click the button.