Need to create a specification name in Access?
If so, I’ll show you the steps 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 button click.
Steps to Create a Specification Name in Access
Let’s say that you have the following data saved in a CSV/TXT file. Your goal is to create a specification name in Access to import that data.
Create the Specification Name in Access
To start, import the data into Access:
(1) First, go to the External Data tab
(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 the CSV file on your computer. You can use the Browse button 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, click on 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) Next, click on Save As…
(10) You can now type any name for your Specification Name:
(11) Here, I typed “ImportNames” to represent the specification name. This specification name will be used later in the VBA.
Once you typed your desired name, press OK:
(12) Press OK again, and then Next:
(13) Select the option to “Let access add primary key”. Then, press Next:
(14) Type “Names“ as this will be the name of your Access table, where the imported data will be stored.
Finally, click on Finish:
(15) Click on Close (optionally: you can save your import steps, by checking the Save import steps box):
(16) The “Names” table will now get created in Access.
To test that your data was imported successfully from the CSV file, double-click on the table “Names” under the All Access Objects menu:
(17) 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).
In order to delete all the records under the “Names” table, select all the records (you can press CTRL+A using your keyboard to select all the records), then right-click, and finally select the option to Delete Record from the drop-down list:
Close the table once you’re done.
Create an Access Form
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 to import all your data from the CSV file into your “Names” table.
(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 this wizard is not needed:
(5) Right-click on your button, and then select “Build Event…” from the drop-down list:
Select the Code Builder option and then press OK:
Create a VBA to import your data from the CSV file into Access
You’ll now see the following screen:
You can then write your VBA to import the CSV data into the “Names” table, using your button.
Here is the code that you’ll need to put in between the “Private Sub Command0_Click()” and the “End Sub.” You’ll need to make an adjustment to the “Path” variable based on the location where the CSV file is stored on your computer.
Dim Path As String Path = "C:\Users\Ron\Desktop\Test\Names" & ".csv" DoCmd.TransferText acImportDelim, "ImportNames", "Names", Path, True
This is how the VBA code would look in Access:
Let’s quickly review each component of the VBA code:
- Dim Path As String – the ‘Path’ variable is set to string, since that variable will contain a string of characters
- The Path variable has the following structure:
Path = “Location where your CSV is stored\File name” & “.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 to store the imported data from the CSV file
- Path is the variable that contains the location of the CSV file
- True indicates that the data to be imported contains headings
Run the VBA to import your data
Now save the form by pressing CTRL+S while still on the VBA screen. Then type a name for your form. Here, I chose to name the form as: “myImportForm”
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 “myImportForm.”
Then, from the drop-down list, select Form View:
If you haven’t already done so, close the “Names” table, and then click on the button to import your data:
The data from the CSV file should now be imported into Access. To see that it is indeed the case, double-click on the “Names” table under the All Access Objects. You’ll now see all the imported records:
You have seen how to import your data in the CSV file into Access (the same principles would apply for a TXT file), but what if you have an Excel file, and you’d like to import the data in the file into Access using a button?
VBA to Import Data from Excel into Access
It’s actually easier to import 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 an Excel file with “.xlsx” format:
Below is the VBA code that you can use. As before, you’ll need to modify the “Path” variable to the location where the 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\Ron\Desktop\Test\Names" & ".xlsx" DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "Names", Path, True
Let’s quickly review the DoCmd.TransferSpreadsheet command:
- acImport is used when importing data into Access
- acSpreadsheetTypeExcel12 is used when importing Excel spreadsheets
- “Names” is the table name 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 you saw before, in order to apply the above VBA code whenever you click the button.