IN Clause

Identifies tables in any external database (external database: The source of the table that is to be linked or imported to the current database, or the destination of a table that is to be exported.) to which the Microsoft Access database engine can connect, such as a dBASE or Paradox database or an external Microsoft Access database.


To identify a destination table:

[SELECT | INSERT] INTO destination IN
{path | ["path" "type"] | ["" [type; DATABASE = path]]}

To identify a source table:

FROM tableexpression IN
{path | ["path" "type"] | ["" [type; DATABASE = path]]}

A SELECT statement containing an IN clause has these parts:

Part Description
destination The name of the external table into which data is inserted.
tableexpression The name of the table or tables from which data is retrieved. This argument can be a single table name, a saved query, or a compound resulting from an INNER JOIN, LEFT JOIN, or RIGHT JOIN.
path The full path for the directory or file containing table.
type The name of the database type used to create table if a database is not a Microsoft Access database (for example, dBASE III, dBASE IV, Paradox 3.x, or Paradox 4.x).

You can use IN to connect to only one external database at a time.

In some cases, the path argument refers to the directory containing the database files. For example, when working with dBASE, Microsoft FoxPro, or Paradox database tables, the path argument specifies the directory containing .dbf or .db files. The table file name is derived from the destination or tableexpression argument.

To specify a non-Microsoft Access database, append a semicolon (;) to the name, and enclose it in single (' ') or double (" ") quotation marks. For example, either 'dBASE IV;' or "dBASE IV;" is acceptable.

You can also use the DATABASE reserved word to specify the external database. For example, the following lines specify the same table:



For improved performance and ease of use, use a linked table (linked table: A table stored in a file outside the open database from which Access can access records. You can add, delete, and edit records in a linked table, but you cannot change its structure.) instead of IN.

You can also use the IN reserved word as a comparison operator in an expression (expression: Any combination of mathematical or logical operators, constants, functions, and names of fields, controls, and properties that evaluates to a single value. Expressions can perform calculations, manipulate characters, or test data.).

Applies to:
Access 2007