Article Index
Microsoft Access Tutorial
Queries
Creating A Database
Tables
Creating Tables
Queries
Forms In Microsoft Access
Reports In Microsoft Access
Data Access Pages
Controlling The User's Input
Subdatasheets
Designing Forms
Exploring And Analyzing Data
Tables Looks
Design And Exploration
Forms Design Propertie
Controlling Data Output
Microsoft Access Controls
Design And Improvements
Importing And Exporting Data
Macros
Switchboards

Microsoft Access can help you import data from various other applications, whether from the Microsoft Office suite, or from other applications

You can import a time sheet from Microsoft Excel, export data to a table in Microsoft Word, import an address book from Microsoft Outlook, or publish your data to Microsoft Internet Explorer through Microsoft FrontPage.

Importing Data From Another Application:

With Microsoft Access, you can use data from another or external application into Microsoft Access. You have the ability to import or export data; you can also link to data that exist somewhere else. Data you import can originate from various sources, including other database applications (Microsoft Access, DBase, Paradox, ODBC databases), spreadsheet applications (Lotus 1-2-3, Microsoft Excel), and various others (Microsoft Word, WordPad, Notepad, Microsoft Outlook, etc). Regardless of the origin of data, it needs to be configured to be Microsoft Access friendly.

When you are getting prepared to import data from another source, there are a few rules you should observe, depending on the originating document. For example, for a text document (Microsoft Word, WordPad, Notepad, etc) you have to decide on the delimiter that will help Microsoft Access determine the beginning and end of a field item, a separator that will be the limit line between two fields. You can import data into an existing table or you can create a new table from the imported data.

Importing From Another MS Access Database:

If you have a table in another database and you want to use that table, you can either link to or import that external table. This is a simple process once you know where the table is located.

Start Microsoft Access and choose to create a Blank Database called Import - Export Data. The database we are going to create is for demonstration purposes. It doesn't have much functionality. But by the end, we should have learned the necessary information to import/export data.

On the Standard toolbar, click the New Object button and choose Table.

Double-click the Import Table line from the list. From the Import dialog, if necessary, browse to the right folder, double-click the Library2 database. The Import dialog comes up.

From the Import Objects property sheet, in the Tables tab, click tblEmployees and click OK.

Right-click tblEmployees, choose Rename, press End and type 1 (now the new table should be renamed tblEmployees1).

Importing Data From Microsoft Excel:

Microsoft Excel is a spreadsheet application made of columns and rows. The intersection of a column and a row is called a cell (it looks like, and maybe better than, the Datasheet View that you are used to).

Microsoft Excel uses Worksheets to display and relate its data. Everything you type in Microsoft Excel is stored in cells, and cells are comprised in a worksheet. Microsoft Access and MS Excel have differences in functionality or in the way they handle the content of cells.

In both apps, you can use column headers "as is", with MS Excel calling its columns A, B, C, etc; and MS Access calling its columns Field1, Field2, etc.

If you are planning on using data created in MS Excel into your Microsoft Access database, you need to configure the spreadsheet to be Microsoft Access "friendly", which is usually very easy since these apps where created with collaboration in mind.

In Microsoft Excel, it is not necessary to specifically have column headers to identify the content of a column (since it is your spreadsheet, you can decide that, just by looking at it and using the column headers provided by the workbook, you could figure out what the worksheet is used for).

That would make Microsoft Access also read your worksheet as is. The suggestion is to give customized headers to the worksheet and "let Microsoft Access know" that you have already defined the column headers. You do that by creating a special row of data to carry the "names" of columns.

To import your data, you have the option of creating a Microsoft Access table that has the same fields as in Microsoft Excel, the columns have to match those of Microsoft Excel. Then you can import the worksheet into the existing, already created table. You can also import a spreadsheet "as is", in a new table, which can sometimes be easier (but not always cute).

If you have Microsoft Excel, start it and create a spreadsheet on Sheet1. Rename the worksheet Employees and give it appropriate fields. You can also open the Employees.xls workbook and see what it looks like.

In Microsoft Access, create a table called tblEmployees2 with the following fields.

On the Standard toolbar, click the New Object button and choose Table

In the New Table dialog, click Import Table and click OK.

In the Microsoft Access Exercises folder, click the Files of Type combo box and choose Microsoft Excel.

Click the RcdEmployees workbook and click Import. The Import Spreadsheet Wizard starts.

Make sure you select the Employees worksheet and click Next.

In the following page, you let the wizard know whether you have already defined the column headers. For this exercise, click the First Row Contains Column Headings check box and click Next.

Then you decide whether you want to create a special cute table for the new data or you want to store data in an existing table. For this exercise, click the combo box and choose tblEmployees2.

Click Next and click Finish.

When the importing is finished, you receive a confirmation dialog:

Click Ok. If something goes wrong, the wizard will let you know. Then you can fix it.

Importing Data In Text File Format:

You can import a text database from Notepad. This time also, the text has to be configured to send data to Microsoft Access. First, you have make sure that your text "says" where a field content starts and where it stops. The usual way is to include it in double quotes (" "). Then MS Access wants you to specify a Delimiter, which is like the line between two columns. You have the choice of using the Tab key when you type your data, or separating fields contents with a comma, then let MS Access "know". Finally, you end each record with a carriage return (the Enter key).

Start a text application, like Notepad and open the txtEmployees file to see what it looks like. Then close it.

In Microsoft Access, from the main menu, click:

File -> Get External Data -> Import...
From the C:Microsoft Access Applications folder, choose Files Of Type: Text Files. For the File Name, choose txtEmployees.

From the first page of the Import Text Wizard, accept that the text be Delimited and click Next.

On the second page, set the delimiter as Comma and check the box stating: First Row Contains Field Names, then click Next.

Accept to create a new table. Click Next.

In the following dialog, you could optionally configure or reconfigure some of the fields. To do that, you click a field, then change its Data Type and/or its Indexed characteristics. For example, click the EmployeeNumber field. In the Indexed combo box, set its field to (Yes, No Duplicates). Click Next.

Next, you decide on the Primary Key. You can let the wizard create one for you. We will create our own. Click the combo box and choose EmployeeNumber. Click Next.

Finally, you are asked to give a name to the table. Name it tblEmployees3. Click Finish.

Once more, you should receive a confirmation message when the table has been imported.

You can open the table and see what it looks like.

Importing a Microsoft Outlook Address Book:

Importing a Microsoft Outlook Address Book is as smooth as what we have seen so far. All you have to do is have the address book already created and you are ready to roll.

On the Standard toolbar, click the New Object button and choose Table.

In the New Table dialog, double-click Import Table.

In the Import dialog, change the File Of Type to Outlook.

A particular wizard starts. In the first page of the wizard, click the + on Personal Folders to expand it.

Click Contacts and click Next.

Accept to store data In A New Table. and click Next.

Click Next, Next, Finish, and OK.