Chapter 6: Importing and Linking Data

You can certainly build all your tables, design queries, forms, and reports, and then enter from scratch all the data into your empty tables. However, in many cases you’ll have some of the data you need lying around in other files. For example, you might have a customer list in a spreadsheet or a text file. Your list of products might be in another non-Access database file. Microsoft Office Access 2007 provides tools to help you bring the data into your new application.

Although you can use Office Access 2007 as a self-contained database and application system, one of its primary strengths is that it allows you to work with many kinds of data in other databases, in spreadsheets, or in text files. In addition to using data in your local Access 2007 database, you can import (copy in) or link (connect to) data that’s in text files, spreadsheets, other Access databases, dBASE, Paradox, and any other SQL database that supports the Open Database Connectivity (ODBC) software standard (including Microsoft Visual FoxPro). As you’ll learn later in Chapter 23, “Using XML,” Access also supports files in eXtensible Markup Language (XML), which is the standard format for defining and storing data on the Web.

A Word About Open Database Connectivity (ODBC)

If you look under the hood of Access, you’ll find that it uses a database language called SQL (Structured Query Language) to read, insert, update, and delete data. SQL grew out of a relational database research project conducted by IBM in the 1970s. It has been adopted as the official standard for relational databases by organizations such as the American National Standards Institute (ANSI) and the International Organization for Standardization (ISO). When you’re viewing a query window in Design view, you can see the SQL statements that Access uses by first clicking the Design tab below Query Tools, clicking the arrow below the View button in the Results group, and then clicking the SQL View command.

Article 2, “Understanding SQL,” on the companion CD provides more details about how Access uses SQL The Appendix, “Installing Your Software,” provides details about installing and managing ODBC connections on your computer.

In an ideal world, any product that “speaks” SQL should be able to “talk” to any other product that understands SQL. You should be able to build an application that can work with the data in several relational database management systems using the same database language. Although standards exist for SQL, most software companies have implemented variations on or extensions to the language to handle specific features of their products. Also, several products evolved before standards were well established, so the companies producing those products invented their own SQL syntaxes, which differ from the official standard. An SQL statement intended to be executed by Microsoft SQL Server might require modification before it can be executed by other databases that support SQL, such as DB2 or Oracle, and vice versa.

To solve this problem, a group of influential hardware and software companies-more than 30 of them, including Microsoft Corporation-formed the SQL Access Group. The group’s goal was to define a common base SQL implementation that its members’ products could all use to “talk” to one another. The companies jointly developed the Common Language Interface (CLI) for all the major variants of SQL, and they committed themselves to building CLI support into their products. About a dozen of these companies jointly demonstrated this capability in early 1992.

In the meantime, Microsoft formalized the CLI for workstations and announced that Microsoft products-especially those designed for the Microsoft Windows operating system-would use this interface to access SQL databases. Microsoft calls this formalized interface the Open Database Connectivity (ODBC) standard. In the spring of 1992, Microsoft announced that more than a dozen database and application software vendors had committed to providing ODBC support in their products by the end of 1992. With Access, Microsoft provides the basic ODBC Driver Manager and the driver to translate ODBC SQL to the SQL understood by Microsoft SQL Server. Microsoft has also worked with several database vendors to develop drivers for other databases. You can see a diagram of the ODBC architecture in Figure 6–1.

Figure 6–1: The Microsoft ODBC architecture allows any ODBC-enabled application to link to any SQL database for which you have a driver.

Access was one of Microsoft’s first ODBC-compliant products, and the ODBC Driver Manager is a standard part of Microsoft’s operating systems. Microsoft has further refined this architecture with ActiveX Data Objects (ADO). ADO is a special library of objects that you can use to fetch and modify information about the database structure and fetch and update data from any database, including Access. You can also fetch data from ODBC databases using the standard Data Access Objects (DAO) library used to manipulate native Access tables. After you’ve added the drivers for the other SQL databases that you want to work with, you can use Access to build an application using data from any of these databases.

Note 

You can use ADO as a “universal interface” to both databases that support ODBC as well as to those that do not See Chapter 19, “Understanding Visual Basic Fundamentals,” for details about working with ADO using Visual Basic.