MS-Access / Getting Started

---

IN Clause

In a desktop database (.accdb), specifies the source for the tables in a query. The source can be another Access database; a dBASE, or any database for which you have an Open Database Connectivity (ODBC) driver. This is an Access extension to standard SQL.

Syntax

IN <"source database name"> <[source connect string]>

Enter "source database name" and [source connect string]. (Be sure to include the quotation marks and the brackets.) If your database source is Access, enter only "source database name". Enter these parameters according to the type of database to which you are connecting, as shown in Table-1.

Table-1 IN Parameters for Various Database Types

Database Name	Source Database Name	Source Connect String
Access	"drive:\path\filename"	(none)
dBASE III	"drive:\path"	[dBASE III;]
dBASE IV	"drive:\path"	[dBASE IV;]
dBASE 5	"drive:\path"	[dBASE 5.0;]
ODBC	(none)	[ODBC; DATABASE= defaultdatabase; UID=user; PWD= password;DSN= datasourcename]

Notes The IN clause applies to all tables referenced in the FROM clause and any subqueries in your query. You can refer to only one external database within a query, but if the IN clause points to a database that contains more than one table, you can use any of those tables in your query. If you need to refer to more than one external file or database, attach those files as tables in Access and use the logical attached table names instead.

For ODBC, if you omit the DSN= or DATABASE= parameter, Access prompts you with a dialog box showing available data sources so that you can select the one you want. If you omit the UID= or PWD= parameter and the server requires a user ID and password, Access prompts you with a login dialog box for each table accessed.

For dBASE, you can provide an empty string ("") for source database name and provide the path or dictionary filename using the DATABASE= parameter in source connect string instead, as in

"[dBase IV; DATABASE=C:\MyDB\dbase.dbf]"

Example

In a desktop database (.accdb), to retrieve the Company Name field in the Northwind Traders sample database without having to attach the Customers table, enter the following:

SELECT Customers.CompanyName
FROM Customers
IN "C:\My Documents\Shortcut to NORTHWIND.ACCDB";