Table of contents
TOC
Collapse the table of content
Expand the table of content

IN Clause (Microsoft Access SQL)

office 365 dev account|Last Updated: 4/3/2018
|
3 Contributors

Applies to: Access 2013 | Access 2016

In this articleSyntaxRemarks Example

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:

PartDescription
destinationThe name of the external table into which data is inserted.
tableexpressionThe 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.
pathThe full path for the directory or file containing table.
typeThe 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:

…FROM Table IN "" [dBASE IV; DATABASE=C:\DBASE\DATA\SALES;]; 

…FROM Table IN "C:\DBASE\DATA\SALES" "dBASE IV;"

Note

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 databaseSQL statement
Microsoft® Access atabase engine database
SELECT CustomerID
FROM Customers
IN OtherDB.mdb 
WHERE CustomerID Like "A*";

| |dBASE III or IV. To retrieve data from a dBASE III table, substitute "dBASE III;" for "dBASE IV;".|

SELECT CustomerID
FROM Customer
IN "C:\DBASE\DATA\SALES" "dBASE IV;"
WHERE CustomerID Like "A*";

| |dBASE III or IV using Database syntax.|

SELECT CustomerID
FROM Customer
IN "" [dBASE IV; Database=C:\DBASE\DATA\SALES;] 
WHERE CustomerID Like "A*";

| |Paradox 3.x or 4.x. To retrieve data from a Paradox version 3.x table, substitute "Paradox 3.x;" for "Paradox 4.x;".|

SELECT CustomerID
FROM Customer
IN "C:\PARADOX\DATA\SALES" "Paradox 4.x;"
WHERE CustomerID Like "A*";

| |Paradox 3.x or 4.x using Database syntax|

SELECT CustomerID
FROM Customer
IN "" [Paradox 4.x;Database=C:\PARADOX\DATA\SALES;] 
WHERE CustomerID Like "A*";

| |A Microsoft Excel worksheet|

SELECT CustomerID, CompanyName
FROM [Customers$] 
IN "c:\documents\xldata.xls" "EXCEL 5.0;"
WHERE CustomerID Like "A*"
ORDER BY CustomerID;

| |A named range in a worksheet|


SELECT CustomerID, CompanyName
FROM CustomersRange
IN "c:\documents\xldata.xls" "EXCEL 5.0;"
WHERE CustomerID Like "A*"
ORDER BY CustomerID;

|

ACCESS SUPPORT RESOURCES
Access for developers forum on MSDN
Access help on support.office.com
Access help on answers.microsoft.com
Search for specific Access error codes on Bing
Access forums on UtterAccess
Access wiki on UtterAcess
Access developer and VBA programming help center (FMS)
Access posts on StackOverflow

© 2018 Microsoft