applied tips : february 2008

microsoft access : importing from anywhere

Microsoft Access can read everything from Excel workbooks to text files and beyond.

People from different countries speak different languages. Likewise, different types of computer programs save files in different formats. Fortunately, just as some people can speak several languages, Access can read and write in other file formats. In this article we’ll show you how easy it is to work with information in other file formats.

To link or to import

The first decision to make when working with other file formats is whether you want to import, or copy, the information into Access, or just link to the information’s original location. Here’s a quick description of both methods:

Imported: When you import information, you copy and place it in a new or existing table in your database. There are many reasons to import information into an Access table. Perhaps you want to use the most powerful features of Access to analyze data in an Excel workbook, or maybe you need to import a list of names stored in a comma delimited text file. This is a good option if the information in Access and the original file don’t need to correspond with one another.

Linked: When you link information, that data stays in its original location. You can add, delete, and edit records in a source file from within Access, but you can’t change its structure. If the data in the source file changes, the changes will appear in your database as well. Linked tables are most frequently used to connect Access to data sources that are accessed by other users. Maybe you want to create a link to information that is stored in your organization’s Microsoft SQL Server database. Use this option if the information in Access and the original file need to correspond.

Importation preparation

External information must often be cleaned and tweaked before being imported into Microsoft Access. Make sure that you delete any fields or extraneous data that you don’t want to import. If you want to import an Excel workbook, make sure that your data is the only data on the worksheet. If not, you'll have to cut and paste the data into a new worksheet.

Finally, try to make sure your data includes field names, such as FirstName, LastName, etc. Microsoft Excel works great for cleaning up information before it’s imported into an Access table.  If you're looking at a spreadsheet or text file, the very first line needs to be your field names.

So, let’s import already

Now that you’ve cleaned up your information, you can import it into Microsoft Access. Here’s the step-by-step procedure for importing:

  1. Select File, Get External Data, Import from the menu.
  2. Select the type of file you want to import from the Files of type list.  For example, if you wanted to open a Microsoft Excel workbook, you would select the Microsoft Excel format.
  3. Find and open the file you want to import.  The Text Import Wizard will appear. At this point you have to provide Microsoft Access with some more information, such as how the information is stored in the file. The questions you are asked will vary depending on the type of file you are importing. For example, if you opened a text file you would need to tell Access whether the data was stored in delimited or fixed width format.
  4. Follow the on-screen instructions of the Import Text Wizard to import the data.

If everything goes as planned, Access will import the file’s records into a new table. However, you’ve undoubtedly worked with computers long enough to know that things don’t always work exactly the way they’re supposed to. If Access encounters any problems with your imported data, it will inform you of the problem and save the offending records in a separate table for your review.

The missing link

As we mentioned earlier, the other way that Microsoft Access can use external information is with linked tables. Linked tables are helpful for gaining access to information that is shared by several users. Here’s how to create a linked table:

  1. Select File, Get External Data, Link Tables from the menu.
  2. Find and open the file you want to link to.

When the files are linked, the information will appear in a new table with an arrow next to it.

File Types

The following is a collection of file types that are compatible with Microsoft Access. Read the file type descriptions to find out how you can use different file types to optimize your databases.

You can also link Access databases to other access databases (.MDB and .ACCDB).  This is extremely useful when you have two database files on the network that need to exchange data with each other, even though the serve two different purposes.

schedule a class or get consulting on access

Request an on-site Access class from Applied Office or get some helpful consulting. Sessions are just $125/hr.  Learn more here

quick reference card

Get the Quick Reference Card on Microsoft Access! Download it for free and print it on your own printer. You might even want to laminate it.