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. Access calls the database that you open after you launch Access 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 Access databases, the name of the database file. To run a query against tables contained in the Solutions.mdb sample database, replace (current) in the Source Database text box with the following, as shown in Figure 10.61:

  C:Program FilesMicrosoft OfficeAccessSamplesSolutions.mdb

  To run a query against a set of Paradox tables in the D:Paradox folder, you type the path only (d:paradox where d: is the logical drive letter). If you're using the ODBC API to connect to a client/server database, you leave the Source Database text box empty.

  Figure 10.61. Setting the Source Database property for a query against an external database.

  

• The value of the Source Connect Str property also depends on the type of external database being used. If your external Access database is not 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, type the product name, such as Paradox 3.5 or dBASE IV. ODBC data sources require the complete ODBC connect string. Using ODBC databases is one of the subjects of Chapter 24, "Securing Multiuser Network Applications."

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 run mode. A slight performance penalty exists for running queries against an external database—each time that you run the query, Access must make a connection to open the database. The connection is closed when you close the query.

Figure 10.62 shows a query design based on tables contained in the Solutions.mdb sample database that accompanies Access 97. In this case, Access 97's Northwind.mdb is the current database. Figure 10.63 shows the result of executing the query design of Figure 10.62 against the Example Objects, Examples, and Example Topics tables of Solutions.mdb.