databasedev.co.uk - database solutions and downloads for microsoft access

Importing Data

Importing Data into Microsoft Access using VBA

You might be wondering why you would want to use VBA code to import data while you have so many other options available when doing it the visual way. Well, there are two main reasons:

  • Importing data through VBA code enables you to work with a variety of formats that is not available when you do it using the visual interface such as delimited text in a text file or data in a word table.
  • On a more practical level, if you need to make the same import every time, then simply creating a function to do the job for you will make the process faster and easier as opposed to going through the import wizard every time.

As with exporting data, Access offers three methods for importing data from various types of files. These are; transfertext, transferdatabase and transferspreadsheet. All of these methods are part of the Docmd object.

We will do two import activities, first we will look at how to import data from another Access database and then we will look at how to import data from a text file. If you’ve read my previous article titled "Exporting Data from Access with VBA Code" then you should have all the databases and relevant text files in place. I will be using them here, so it might be a good idea to download them before continuing.

Let’s import a table called "Table1" from the source database to the destination database (both available for download). To import the table we use the transferdatabase method with acImport as the TransferType parameter and "Microsoft Access" as the DatabaseType parameter. So open up the VBA editor (alt+F11) then create a new module and add the following code:

Function GrapAccessTable()
 DoCmd.TransferDatabase transfertype:=acImport, _
  databasetype:="Microsoft Access", _
  databasename:="C:\Documents and Settings\Leidago\My Documents\source.mdb", _
  objecttype:=acTable, Source:="Table1", _
  destination:="importedTable1", structureonly:=False
End Function

The table that is imported will be called importedTable1:

The imported table, now visible in the Table objects.

Importing Text Files

Access makes it easy to import text files, but there is a catch. The data in the text file needs to be structured in a way that Access can interpret it and thus import it easily. The data can be arranged in two ways:

  • Fixed width format - all the fields are the same size
  • Delimited format - the fields are separated by a special character, such as a comma or a tab.

Both these arrangements enable you to import text data in a single line of code using the TransferText method. This method also expects a import specification to be included in one of its arguments.

In our previous article called "Exporting Data from Microsoft Access" we created a delimited text file called table1.txt (also available for download). I will be using this file to import data into a table named "friends".

But first, we need to create a import specification so let’s get to it. Open up the destination database, but don’t select any tables:

  • Select File » Get External Data » Import. The Import dialog box will open:
  • In the Save as Type dropdown box choose Text Files:

  • Go to the folder in which the tab-delimited file (table1.txt) is located, select the file, and click Import. A import wizard dialog will popup:

    Import Text Wizard
  • With Delimited option pre-selected click Next.
  • A second import wizard dialog should come up. Make sure to select Tab as the delimiter as shown below:

  • Click on the advanced button and another window will popup. This is where we define the import specification:

    Import Specification
  • Click on the Save As button and save the import spec as 'Table1 Import Specification' (without the quotes):

Once you’ve saved the specification, just click OK and then Finish. You do not have to go through this process again unless you want to change something in the specification.

Now, that we are armed with an import specification we can go ahead and create a new module with the following code:

Function GetTextFileData()
 DoCmd.TransferText transfertype:=acImportFixed, _
  specificationname:="Table1 Import Specification", _
  tablename:="Friends", _
  filename:="c:\table1.txt", _
  hasfieldnames:=True
End Function

This function, when run, will create a new table called Friends and fill it with data stored in the table1.txt file. Below is a screen of what my database looks like after the function has been run:

Download the sample Microsoft Access Files for this article (29kb)

The Author

Leidago !Noabeb is a computer programmer based in Namibia. He has worked with both opensource and Microsoft technologies for over seven years and specializes in writing communications software. He has made many contributions to various online websites dedicated to web development. He can be reached at: leidago [at] googlemail.com