Understanding Access 2013 Databases

Importing and exporting data

Access has wizards to guide you through specific operations; these are particularly useful when it comes to importing or exporting data. When you import data, you are making a copy of the original data and storing that data in your database file.

In this exercise, you’ll import data into a database.

1. Click the External Data tab.

2. Click Excel in the Import & Export group.

3. Browse in the File Name box to locate the CompanyNames.xlsx spreadsheet.



Figure 37.7

4. Ensure that the first button to Import data is selected, and press OK. The Import Spreadsheet Wizard will now be displayed.

5. Select the First Row Contains Column Headings check box, and notice how the display changes to remove the column headings from the data records. Click Next.

6. Click the second URL column, and use the drop-down Data Type to change the data type to a Hyperlink, and then click Next.

   

   Figure 37.8

   TIP

   This stage in the wizard allows you to select an appropriate data type for the column that will be created to hold the data.

7. On the next screen, Access will display an ID as the primary key (if you wanted a different column to be the primary key, then it could be selected from the Choose my own primary key drop-down list). For this exercise, accept the default choice of ID, and then click Next.

8. The Import to Table will display the name Companies. You can change the name of the new table at this point. Click Finish.

9. The very last screen allows you to save these steps if you will need to repeat them. Click Close (you do not need to save the steps).

10. Double-click the table now shown in the Navigation pane to display your data.



Figure 37.9

TIP

Click the X next to field list to close this window. This allows for more space to display the data contained in your table.

Importing from another Access database

When you import data from another Access database, you can choose to import any of the available design objects. The MSOfficeProData.accdb database contains data that you will need in the following exercise. You will import some of the items in this database into your blank database.

In this exercise, you’ll import data from another Access database.

1. Click the External Data tab.

2. Click Access in the Import & Export group.

3. Locate the MSOfficeProData.accdb file in the File Name box.

4. Leave the default selection of Import tables, queries, forms active, and click OK.

You will then have the option to import different Access objects from the database.



Figure 37.10

5. On the Tables tab, click Select All to highlight all the tables.

6. Click the Forms tab and click to highlight the frmCustomers form. Then click OK to import all the tables and the selected form.

7. Click Close when prompted to save the import steps (you do not need to save a record of these steps, because you will not repeat this operation).

   TIP

   If you import several tables that have relationships, then the relationships will also be imported by default into the new database.

Exporting data from an Access database

Access supports the export of data from both tables and queries (which we will discuss in the next chapter). To export data, select the table or query in the Navigation pane and then either use the right-click option to select an export format, or use the appropriate icon on the ribbon.

In this exercise, you’ll export data from an Access database.

1. In the Navigation pane, click the Customers table.

2. Click the External Data tab, and click the Text File icon in the Export group.



Figure 37.11

This will then display the Text File Export wizard.

3. Use the File name text box to browse to a location to save your exported data.



Figure 37.12

4. Do not make any further selections, but click OK to proceed.

   TIP

   Choosing to export the data with formatting will result in a format that is not easily imported into other programs, but it does result in a simple text layout that could be printed.

   It is recommended that you follow through and accept the default selections. More details will be revealed regarding important choices as we proceed through the steps. This wizard, which is similar to the Import Text Wizard, has a number of advanced choices.

5. On the next wizard page, you can choose between Delimited or Fixed Width, Delimited; which is the default for a more standard choice. An Advanced option (bottom left) allows you very detailed control of how each column is exported. Click Next to proceed.

6. The next wizard page allows you to change the delimiters used for the export, and you can optionally include field names. Click the box to Include Field Names on First Row.

7. Change the Export to file name setting, if required, and then click Finish.

8. Click Close when prompted to save the export steps (you do not need to save the steps).

You should then have produced an export file.



Figure 37.13