How to Create a Specification Name in Access

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.

Table

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

Import file into Access

(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:

How to Create a Specification Name in Access 2016

(6) Choose the Delimited option for the CSV file, and then press Next:

Create a Specification Name in Access 2016

(7) For our example, select the Comma option, and then check the First Row Contains Field Names. Finally, press Next to move to the next screen:

Import Text Wizard

(8) To create your Specification Name click on the Advanced… option:

Create a Specification Name in Access

(9) Next, click on Save As…

How to Create a Specification Name in Access 2016

(10) You can now type any name for your Specification Name:

How to Create a Specification Name in Access 2016

(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:

How to Create a Specification Name in Access 2016

(12) Press OK again, and then Next:

How to Create a Specification Name in Access

(13) Select the option to “Let access add primary key”. Then, press Next:

Let access add primary key

(14) Type Names as this will be the name of your Access table, where the imported data will be stored.

Finally, click on Finish:

Import to Table

(15) Click on Close (optionally: you can save your import steps, by checking the Save import steps box):

Save import steps

(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:

Table in MS Access

(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:

Delete Records in MS Access

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:

Form Design

(3) Place a button on your form:

Place a button on Access 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: 

Command Button Wizard

(5) Right-click on your button, and then select “Build Event…” from the drop-down list:

How to Create a Specification Name in Access 2016

Select the Code Builder option and then press OK:

Create a Specification Name in Access 2016

Create a VBA to import your data from the CSV file into Access

You’ll now see the following screen:

How to Create a Specification Name in Access

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:

specification name 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”

Save Form in MS Access

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:

Form View

If you haven’t already done so, close the “Names” table, and then click on the button to import your data:

Button in Access

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:

Access Table

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:

Data in Excel

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.