Creating Queries from Tables in Other Databases

Access's Query Properties window includes two properties that let you create a query based on tables contained in a database other than the current database. The database that you open after you launch Access is called the current database. Databases other than the current database commonly are called external databases. The use of these two properties is as follows:

• The value of the Source Database property for desktop databases is the path to the external database and, for Jet databases, the name of the database file. To run a query against tables contained in the Oakmont.mdb sample database from the accompanying CD-ROM, replace (current) in the Source Database text box with the following, as shown in Figure 11.76:

  Figure 11.76. Specify the full path to the external database as the value of the Source Database property.

  

C:\Program Files\Seua10\Oakmont\Oakmont.mdb

You must have installed the sample files from the CD-ROM in the default C:\Program Files\Seua11 folder for this connection string to work.

• The value of the Source Connect Str property depends on the type of external database being used. If your external Jet database isn't secure, leave the Source Connect Str text box empty; otherwise, type UID=UserID;PWD=Password to specify the user ID and password needed to open the external database. For other desktop databases, you type the product name, such as Paradox 3.5 or dBASE IV. ODBC data sources require the complete ODBC connect string.

Running a query against an external database is related to running a query against linked tables. When you link tables, the data in the tables is available at any time that your application is running. When you run a query against an external database, the connection to the external database is open only while your query is open in Design or Datasheet view. A slight performance penalty exists for running queries against an external database each time that you run the query, Jet must make a connection to open the database. The connection is closed when you close the query.

After you specify the external database, its tables appear in the Show Table dialog's list. Figure 11.77 illustrates a query design based on tables in the external Oakmont.mdb sample database. Figure 11.78 shows the result of executing the query design of Figure 11.77.

Figure 11.77. Design view of a query in an external Jet database is the same as for a query against tables in the current database. You can create joins between external and current database tables, but you can't enforce referential integrity.

Figure 11.78. This datasheet displays the result set of the query of Figure 11.77 against the external Oakmont.mdb database.

Note

The Joins11.mdb database in the \Seua11\Chaptr10 folder of the accompanying CD-ROM includes all the sample queries of this chapter.