Types of external data

Access can use and exchange data among a wide range of applications. For example, you may need to get data from a Microsoft Excel file, a SQL Server, Oracle, or even a text file. Access can move data among several categories of applications, including other Windows applications, Macintosh applications, database management systems, text files, and even mainframe files.

Ways of working with external data

Often, you need to move data from another application or file into your Access database, or vice versa. You might need to get information you already have in an external spreadsheet file. You can reenter all that information by hand or have it automatically imported into your database.

Access has tools that enable you to exchange data with another database or spreadsheet file. In fact, Access can exchange data with many different file types, including the following:

• Access database objects (all types)
• Text files
• Excel files
• ODBC databases (SQL Server, Sybase Server, Oracle Server, and other ODBC-compliant databases)
• HTML tables, lists, and documents
• XML documents
• Outlook tables
• Microsoft Exchange documents
• SharePoint lists
• Word documents
• Rich Text Format (RTF) documents

Access works with these external data sources in several ways:

• Linking: Linking to data creates a connection to a table in another Access database or links to the data from a different format. Linking uses the data in the source file format (such as Excel or XML). The linked data remains in its original file. The file containing the linked data should not be moved, deleted, or renamed; otherwise, Access won't be able to locate the data the next time it's needed. If moving or renaming the linked data source is unavoidable, Access provides tools for relinking to the source.
• Importing: Importing copies data from a data source, another Access database, or another application's database file into an Access table. The imported data is converted to the appropriate Access data type, stored in a table, and managed by Access from that point on.
• Exporting: Exporting copies data from an Access table into a text file, another Access database, or another application's file. Like importing, changing the source data does not affect the exported data.

Each method has clear advantages and disadvantages, as covered in the following sections.

Importing from another Access database

You can import items from a source database into the current database. The objects you import can be tables, queries, forms, reports, macros, or modules. Import an item into the current Access database by following these steps:

1. Open the destination database you want to import into. In this case, open the Chapter06_1.accdb database.
2. Select the External Data tab.
3. Click the Access option in the Import & Link group, and then click the Browse button to select the filename of the source database (Chapter06_2.accdb).
4. Select the Import Tables, Queries, Forms, Reports option button and click OK. The Import Objects dialog box (shown in Figure 6.2) appears. It gives you options for importing a database object.

   

5. Select a table and click OK. If an object already exists in the destination database, then a sequential number is added to the name of the imported object, distinguishing it from the original item. For example, if tblDepartments already exists, the new imported table is named tblDepartments1.

   The Get External Data – Save Import Steps dialog box appears, with a very useful feature that allows you to store the import process as a saved import, as shown in Figure 6.3.

   

6. Provide a name for the import process to make it easy to recall the saved import's purpose. You can execute the saved import again at a later date by clicking the Saved Imports button in the Import & Link group of the External Data tab of the Ribbon. From the Manage Data Tasks dialog box (see Figure 6.4), you can change the name of the saved import, the location of the source file, and the description of the saved import. All other information about the saved import (such as the destination table name) can't be changed. If you need to change other information, create a new saved import with the proper parameters.

   

Importing from an Excel spreadsheet

You can import data from Excel spreadsheets to a new or existing table. The primary rule when importing Excel data is that each cell in a column must contain the same type of data. When you're importing Excel data into a new table, Access guesses at the data type to assign to each field in the new table based on the first few rows of Excel data (other than column headings). An import error may occur if any Excel row past the first row contains incompatible data. In Figure 6.5, the Age column should contain all numeric data, but it contains an age written out as words. This is likely to cause an error during the import process. The data in Row 5 should be changed so that the entire column contains numeric data (as shown in Figure 6.6).

You can import all the data from an Excel spreadsheet, or just the data from a named range of cells. Naming a range of cells in your spreadsheet can make importing into Access easier. Often a spreadsheet is formatted into groups of cells (or ranges). One range may contain a listing of sales by customer, for example, while another may include total sales for all customers, totals by product type, or totals by month purchased. By providing a range name for each group of cells, you can limit the import to just one section of the spreadsheet data.

To import EMPLIST.xls, follow these steps:

1. Click the Excel button in the Import & Link group on the External Data tab.
2. Browse to the Excel file.
3. Select Import the Source Data into a New Table in the Current Database and click OK. The first Import Spreadsheet Wizard screen (see Figure 6.7) shows lists of worksheets or named ranges, and a preview of the data, in the Excel spreadsheet.

   

4. Select a worksheet or named range and click Next.
5. On the next screen (shown in Figure 6.8), select the First Row Contains Column Headings check box and click Next. Normally you don't want the Excel column headings stored as field data. Access uses the column headings as the field names in the new table.

   

6. On the next screen (shown in Figure 6.9), you can override the default field name and data type, remove fields from the import, and create an index on a field. When you're done, click Next.

   

7. On the next screen, set a primary key for the new table (see Figure 6.10) and click Next. A primary key uniquely identifies each row in a table.

   

8. Specify the new table's name and click Finish.
9. If you want to, save the import process for later execution. The new table now appears in the Navigation pane.

Importing a SharePoint list

SharePoint lists are candidate data sources for Access databases. Because SharePoint lists reside on web servers, SharePoint data is accessible across a network to qualified users. This gives Access the ability to share data virtually anywhere in the world.

Because SharePoint is deployed on a large number of corporate intranets, Access is likely to continue as a component in enterprise environments.

Importing data from text files

There are many reasons for text file output, such as business-to-business (B2B) data transfers. Also, mainframe data is often output as text files to be consumed in desktop applications. Access can import from two different types of text files: delimited and fixed width. The Access Import Text Wizard assists you in importing or exporting both delimited and fixed-width text files.

1,Davolio,Nancy,5/1/14 0:00:00,4000
2,Fuller,Andrew,8/14/14 0:00:00,6520
3,Leverling,Janet,4/1/14 0:00:00,1056
4,Peacock,Margaret,5/3/15 0:00:00,4000
5,Buchanan,Steven,10/17/15 0:00:00,5000
6,Suyama,Michael,10/17/15 0:00:00,1000
7,King,Robert,1/2/14 0:00:00,1056
8,Callahan,Laura,3/5/14 0:00:00,1056
9,Dodsworth,Joseph,11/15/14 0:00:00,1056

Fixed-width text files

Fixed-width text files also place each record on a separate line. However, the fields in each record are fixed in length. Fields are padded with trailing spaces to maintain spacing within each line, as shown in Figure 6.13.

Notice that the fields in a fixed-width text file are not separated by delimiters. Instead, they start at exactly the same position in each record, and each record has exactly the same length.

Text values, such as first and last names, are not surrounded by quotation marks. There is no need for delimiting text values because each field is a specific width. Anything within a field's position in a row is considered data and does not require delimiters.

To import a fixed-width text file, follow these steps:

1. Open the Chapter06_1.accdb database.
2. Select the External Data tab.
3. Click Text File in the Import & Link group.
4. Browse to ImportFixed.txt, select the Import option button, and click OK. The first screen of the Import Text Wizard (refer to Figure 6.11) appears. The Import Text Wizard displays the data in the text file and lets you choose between delimited or fixed width.
5. Select Fixed Width and click Next. The next screen of the Import Text Wizard (shown in Figure 6.14) appears.

   

6. Adjust field widths as needed. Access guesses at the best breaks to use for fields, based on the most consistent spacing across rows. In this case, the field breaks are very consistent. If necessary, however, use the mouse to grab a dividing line and move it left or right to change the width of fields in the file.
7. Click the Advanced button at the bottom of the wizard. The Import Specification dialog box (shown in Figure 6.15) appears. The Import Specification dialog box lets you change the default formats for dates, times, field names, indexing, and data types. It also provides an option for skipping fields you don't want to import. (For detailed information on this dialog box, see the following sidebar.)

   

8. Ensure that the Date Order is set to MDY and the Four Digit Years check box is selected.
9. Select the Leading Zeros in Dates check box.
10. Click OK to dismiss the Import Specification dialog box.
11. Continue through the remaining Import Text Wizard screens.

Importing and exporting XML documents

Importing XML documents is easy with Microsoft Access. XML is often used to transfer information between disparate platforms, databases, operating systems, applications, companies, planets, universes—you name it! XML is used for raw data, metadata (data descriptions), and even processing data. It's safe to say that most Access developers eventually import or export data in XML format.

Presenting XML in Access needs to be done in an odd way. You could easily import a simple XML document in your Access database, but the best way to find out how well Access uses XML is to begin by exporting something into XML.

Follow these steps to export data from Access to an XML file:

1. Open the Chapter06_1.accdb database.
2. Open tblDepartments in Datasheet view.
3. Select the External Data tab, and click XML File in the Export section.
4. Name the XML file tblDepartments.xml, and click OK. The Export XML dialog box (shown in Figure 6.16) appears.

   

The Export XML dialog box includes options for specifying advanced options for the XML export process. Clicking the More Options button opens a dialog box (see Figure 6.17) with several important XML settings.

The data contained in an XML file may be relational or hierarchical. For example, a single XML file might contain information on both product categories and the products themselves. A schema file is needed for complex XML to be understood by other applications. Access automatically produces a schema file (XSD extension) for data exported in XML format. Figure 6.18 shows the Schema tab of the Export XML dialog box.

The XML schema file includes information such as the data type of each field and the source table's primary key and indexes.

A further refinement of the XML export process is to specify how the XML data should be presented in an application using the exported data. (The presentation is specified using HTML conventions.) In most cases, the XML presentation file (XSL extension) is not needed, because the application that is designed to use the XML file displays the data as required by its users. Figure 6.19 shows the Presentation tab of the Export XML dialog box. Notice that none of the options on this tab is selected by default.

In a text editor, such as Notepad, open tblDepartments.xml. You'll see the contents of the XML file, as shown in Figure 6.20.

The first two lines of the text file define the version of XML that was used and the schema. The exported data and structure start on the third line. XML is a hierarchy of tags that define the structure of the data, and each piece of data is within an opening and closing tag. Each record begins with a tag for the name of the table. In this example <tblDepartments> is the tag that defines the table. A few lines down, there is a closing tag, </tblDepartments>, signifying the end of the record.

In between these two tags are the fields and data for that record. The first field of the first record is recorded as <DeptNumber>1000</DeptNumber>. An application that understands XML will interpret that line to mean there is a field named DeptNumber and this record has 1000 in that field. This hierarchy of tags and data continues for each field in the record and for each record in the table.

Just as Access can export to XML, it can also import it. To import the tblDepartments.xml file that you just exported, follow these steps:

1. Click on the XML File button on the Import & Link group of the External Data tab.
2. Browse to tblDepartments.xml and click OK. The Import XML dialog, shown in Figure 6.21, displays how Access interprets the XML data.

   

3. Click OK.

Access converted the well-formed XML into a table. The <tblDepartments> tag determine what the imported table will be named, the tags within the <tblDepartments> tag and its closing tag define the fields, and the data in between the field tags will become data in the new table.

Importing and exporting HTML documents

Access enables you to import HTML tables as easily as any other database, Excel spreadsheet, or text file. You simply select an HTML file to import and use the HTML Import Wizard. The HTML Import Wizard works exactly like the other import wizards described earlier in this chapter.

And just like demonstrating XML in the previous section, we'll do an HTML import in reverse, as well. First, you export a table to generate an HTML file, and then you import the file back into Access to create a new table:

1. Open the Chapter06_1.accdb database and select tblEmployees from the Navigation bar.
2. Select the External Data tab, click the More drop-down button in the Export group, and select HTML Document.
3. Specify an HTML file as the export destination in the Export – HTML Document dialog box (see Figure 6.22).

   

4. Select your HTML output options and click OK. The HTML export is completed as soon as you click the OK button. Unless you check Export Data with Formatting and Layout, no options other than what you see in Figure 6.22 are available when exporting HTML data.

Exporting data with formatting and layout, an option in the Export – HTML Document dialog box, presents additional exporting options. The most important option is that it allows you to specify an HTML template for your export. An HTML template is a normal HTML file, except that it includes special tags that Access recognizes. These tags instruct Access where to place certain data when exporting, allowing you to define the other aspects of the HTML document, such as styling and logos.

Importing the HTML is much like importing a text file shown previously in this chapter. In fact, the Import HMTL Wizard has most of the same screens and options as the Import Text Wizard, such as defining data types for fields and identifying the primary key.

Importing Access objects other than tables

You can import other Access database tables or any other object in another database, which means you can import an existing table, query, form, report, macro, or module from another Access database. You can also import custom toolbars and menus.

As a simple demonstration, follow these steps:

1. Open the Chapter06_1.accdb database.
2. Select the External Data tab, and in the Import & Link group, click the option to import from another Access database. The screen in Figure 6.23 appears. Notice that this dialog box enables you to specify whether to import database objects or link to tables in an external Access database.

   

3. Browse to the Chapter06_2.accdb database and click OK. Figure 6.24 shows that you can import every type of Access object.

   

When including tables, queries, forms, reports, macros, or modules—all in the same import—you can select objects from each tab and then import all the objects at once.

Figure 6.24 shows the Import Objects dialog box with the Options button clicked. Clicking Options shows the options for importing table relationships, menus, toolbars, and other Access database objects. Importing (and exporting, for that matter) is an excellent way of backing up objects prior to making changes to them.

Importing an Outlook folder

An interesting Access import capability is the option to import data directly from Outlook. Although most people think of Outlook as an e-mail system, it supports a number of important business needs, such as scheduling and contact management.

When working with Outlook data, Access doesn't care whether an imported item is an e-mail or contact. Access handles all types of Outlook objects with equal ease.

Select Outlook Folder from the More drop-down list in the Import & Link group to open the initial Outlook Folder import dialog box (shown in Figure 6.25). Access provides options for importing Outlook data, adding it to an existing Access table, or linking to it from the current Access database.

Selecting the import option opens the Import Exchange/Outlook Wizard (shown in Figure 6.26). As shown in this dialog box, Access can import Outlook e-mail, contacts, calendars, journals, and other folders.

Depending on which item you select in the Import Exchange/Outlook Wizard, the remaining wizard screens walk you through the process of bringing Outlook data into Access. You can import Outlook data into a new or existing table, add a primary key, specify data types, and save the import process for later execution.

Exporting objects to other Access databases

When the destination of an export process is an Access database, you can export every type of Access object (tables, queries, forms, reports, and so on). Unlike importing, which allows you to import many objects at once, exporting only allows you to export one object at a time. To export an object to another Access database, follow these generic steps:

1. Open the source database and select an object to export.
2. Click the Access button under the Export section of the External Data tab. The Export – Access Database dialog box appears.
3. Use the Browse button to locate the destination Access database.
4. Click OK. The Export dialog box is shown.

   If an object already exists in the target database, you'll be asked whether you want to replace the object in the target database. If you don't, you can create a new object in the target database.

5. The last step of the wizard enables you to save the export configuration for future use. This option can be quite handy if you'll be frequently performing the same export process.

Exporting through ODBC drivers

Access can export to any Open Database Connectivity (ODBC) compliant database. ODBC compliant databases come with an ODBC driver that serves as the connection between Access and the database. Many of the most popular databases, including Access, are ODBC compliant.

To export through an ODBC driver, follow these steps:

1. Open Chapter06_1.accdb and select an object to export, such as tblEmployees.
2. Click More ODCB Database under the Export group of the External Data tab. The Export dialog box appears as shown in Figure 6.27.

   

3. Type a name for the table or simply click OK to use the default name.
4. Select the appropriate driver for your database from the Select Data Source dialog box (see Figure 6.28). This example uses SQLite3, a free, open-source, ODBC compliant database. Consult your databases documentation for the name of its driver.

   

5. Provide the remaining information required by the ODBC driver to complete the export. The nature of the information needed is highly dependent on the driver and will be different for different databases. Figure 6.29 shows the SQLite3 ODBC Driver Connect dialog box.

   

When the export is complete, you can use the new table in the other database. Figure 6.30 shows that tblEmployees was successfully exported to a SQLite3 database.

Exporting to Word

Access provides two ways to transfer data to Word: Export to Rich Text Format and Word Merge. Rich Text Format (RTF) is a plain text file with special characters that define the formatting. Exporting to RTF creates a document with an RTF extension, not a native Word document (though Word can read RTF, as can WordPad and many other text editors).

Publishing to PDF or XPS

The PDF and XPS file formats were developed to display data as it would appear on a printed page. Data displayed in these formats is generally not editable. Publishing to PDF or XPS outputs a relatively small file and is useful when you want to share data but don't want the other person to be able to change it.

To export tblEmployees to a PDF, follow these steps

1. Select tblEmployees in the Navigation pane.
2. Select PDF or XPS from the Export group on the External Data tab of the Ribbon. The Publish as PDF or XPS dialog box appears.
3. Select PDF from the Save As Type drop-down list (see Figure 6.34).

   

4. Click the Publish button.

The result, as shown in Figure 6.35, is a PDF file that can be opened by many different PDF reader programs. Most computers have some PDF reader software installed, which makes it a great format for sharing data you don't want changed.