IN Clause (Microsoft Access SQL)
Access Developer Reference |
Identifies tables in any external database to which the Microsoft Access database engine can connect, such as a dBASE or Paradox database or an external Microsoft® Access database engine database.
Syntax
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 engine database (for example, dBASE III, dBASE IV, Paradox 3.x, or Paradox 4.x). |
Remarks
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 engine 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:
|
|
Notes |
---|
For improved performance and ease of use, use a linked table instead of IN.
You can also use the IN reserved word as a comparison operator in an expression. For more information, see the In operator.
Example
The following table shows how you can use the IN clause to retrieve data from an external database. In each example, assume the hypothetical Customers table is stored in an external database.
External database | SQL statement |
---|---|
Microsoft® Access atabase engine database |
|
|
|
dBASE III or IV.
To retrieve data from a dBASE III table, substitute "dBASE III;" for "dBASE IV;". |
|
|
|
dBASE III or IV using Database syntax. |
|
|
|
Paradox 3.x or 4.x.
To retrieve data from a Paradox version 3.x table, substitute "Paradox 3.x;" for "Paradox 4.x;". |
|
|
|
Paradox 3.x or 4.x using Database syntax |
|
|
|
A Microsoft Excel worksheet |
|
|
|
A named range in a worksheet |
|
|
See Also
FROM Clause (Microsoft Access SQL)
INNER JOIN Operation (Microsoft Access SQL)
INSERT INTO Statement (Microsoft Access SQL)
LEFT JOIN, RIGHT JOIN Operations (Microsoft Access SQL)
SELECT Statement (Microsoft Access SQL)