IN THIS CHAPTER
Exchanging information between Access and another program is an essential capability in today's database world. Information is usually stored in a wide variety of application programs and data formats. Access, like many other products, has its own native file format, designed to support referential integrity and provide support for rich data types, such as OLE objects. Most of the time, Access alone is sufficient for the job. Occasionally, however, you need to move data from one Access database file to another or use data from another program's format.
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.
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 works with these external data sources in several ways:
Each method has clear advantages and disadvantages, as covered in the following sections.
Linking in Access enables you to work with the data in another application's format—thus, sharing the file with the existing application. If you leave data in another database format, Access can read the data while the original application is still using it. This capability is useful when you want to work with data in Access that other programs also need to work with. However, there are limitations as to what you can do with linked data. For example, you can't update data in a linked Excel spreadsheet or a linked text file. The ability to work with external data is also useful when you use Access as a front end for a SQL Server database—you can link to a SQL Server table and directly update the data, without having to batch-upload it to a SQL Server.
Access databases are often linked to external data so that people can use Access forms to add and update the external data or to use the external data in Access reports.
You can link to the following types of data in Access:
A big disadvantage of working with linked tables is that you lose the capability to enforce referential integrity between tables (unless all the linked tables are in the same external Access database or all are in some other database management system that supports referential integrity). Linked tables may exhibit somewhat poorer performance than local tables. Depending on the source, and the location of the source data, users might experience a noticeable delay when they open a form or report that is based on linked data.
Performance issues become more pronounced when joining linked and local data in a query. Because Access is unable to apply optimization techniques to foreign data, many joins are inefficient and require a lot of memory and CPU time to complete. However, Access can work with many different types of external data, which makes it the ideal platform for applications requiring these features.
Importing data enables you to bring an external table or data source into a new or existing Access table. By importing data, Access automatically converts data from the external format and copies it into Access. You can even import data objects into a different Access database or Access project from the one that is currently open. If you know that you'll use your data in Access only, you should import it. Generally, Access works faster with its own local tables.
One of the principal reasons to import data is to customize it to meet your needs. After a table has been imported into an Access database, you can work with the new table as if you'd built it in the current database. With linked tables, on the other hand, you're greatly limited in the changes you can make. For example, you can't specify a primary key or assign a data entry rule, which means that you can't enforce integrity against the linked table. Also, because linked tables point to external files, which Access expects to find in a specific location, it can make distributing your application more difficult.
Data is frequently imported into an Access database from an obsolete system being replaced by a new Access application. When the import process is complete, the obsolete application can be removed from the user's computer.
Exporting data enables you to pass data to other applications. By exporting data, Access automatically converts data to the external format and copies it to a file that can be read by the external application. As we've already mentioned, sometime you have to import data into Access as opposed to just linking to the external data source if you want to be able to modify the data. If you still need to be able to work with the modified data in the external application, you have little choice but to create a new file by exporting the modified data.
A common reason to export data is because you want to share the data with other users who don't have Access installed.
Before examining the processes of importing and exporting, let's take a brief look at the various options for importing and exporting data with Access.
Access is often described as a “landing pad” for many types of data. This means that Access can use and exchange data among a wide range of applications. For example, you might need to get data from SQL Server or Oracle, a text file, or even an XML document. Access can move data among several categories of applications, database engines, and even platforms (mainframes and Macintosh computers).
Open the Chapter06_1.accdb
database in Access, and click the External Data tab of the Ribbon (see Figure 6.1). You'll see the following groups: Import & Link, Export, and Web Linked Lists.
The Import & Link group includes the following options:
The Export group includes the following options:
Obviously, Microsoft has prepared Access well for its role as a “landing pad” for data.
An import copies external data into an Access database. The external data remains in its original state, but, after the import, a copy exists within Access. When you import a file (unlike when you link tables), Access converts a copy of the data from an external source into records in an Access table. The external data source is not changed during the import. No connection to the external data source is maintained once the import process is complete.
You can import information to new or existing tables. Every type of data can be imported to a new table. However, some types of imports—such as spreadsheets and text files—don't necessarily have a table structure compatible with Access. In those cases, Access will create a table structure for you. If you want to control the structure of the table, you should create the table before importing.
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:
Chapter06_1.accdb
database.Chapter06_2.accdb
).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.
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:
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.
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.
In delimited text files (sometimes known as comma-delimited text files, comma-separated-values text files, or tab-delimited text files), each record is on a separate line in the text file. The fields on the line contain no trailing spaces, normally use commas or tab characters as field separators, and might have certain fields that are enclosed in qualifying characters (such as single or double quotation marks). Here's an example of a comma-delimited text file:
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
Notice that the file has nine records (rows of text) and five fields. A comma separates each field. In this example, text fields are not qualified with double quotation marks. Notice also that the rows are different lengths because of the variable data within each row.
To import a delimited text file named ImportDelim.txt
, follow these steps:
Chapter06_1.accdb
database.ImportDelim.txt
file, select the Import option button, and click OK. The first screen of the Import Text Wizard (shown in Figure 6.11) appears. The Import Text Wizard displays the data in the text file and lets you choose between delimited or fixed width.
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:
Chapter06_1.accdb
database.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.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:
Chapter06_1.accdb
database.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:
tblDepartments.xml
and click OK. The Import XML dialog, shown in Figure 6.21, displays how Access interprets the XML data.
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.
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:
Chapter06_1.accdb
database and select tblEmployees from the Navigation bar.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.
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:
Chapter06_1.accdb
database.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.
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.
An export copies data from an Access table to some other application or data source, such as an XML document. The exported result uses the format of the destination data source and not the format of an Access database. You can copy data from an Access table or query into a new external file. You can export tables to several different sources.
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:
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.
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:
Chapter06_1.accdb
and select an object to export, such as tblEmployees.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.
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).
The real power of exporting to Word is to use the data in Word Merge. With Word Merge, you can control where your data ends up in the Word document. This is useful for such tasks as sending letters, addressing envelopes, producing reports, and creating file folder labels.
To create file folder labels for each department in tblDepartments, follow these steps:
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
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.