Description
Identifies tables in any external database to which the Microsoft Jet database engine can connect, such as a dBASE or Paradox database or an external Microsoft Jet database.
Syntax To identify a destination table: [SELECT | INSERT] INTO destination INPart | 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 isn't a Microsoft Jet 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, 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 Jet 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;"
Notes
External database | SQL statement |
Microsoft Jet 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 |
A Microsoft Excel worksheet | SELECT CustomerID, CompanyName |
A named range in a worksheet | SELECT CustomerID, CompanyName |
External database | SQL statement |
Microsoft Jet database. JetData.mdb is the name of the Jet database that contains the Customers table. | SELECT CustomerID FROM Customers IN 'C:\My Documents\JetData.mdb' WHERE CustomerID Like 'A*'; |
Microsoft Excel workbook (.xls file). XLData.xls is a Microsoft Excel workbook that contains a worksheet called Customers. CustomersRange is a named range in that worksheet. Note that to denote the entire worksheet as the table, you must append a dollar sign ($) and enclose the name in brackets. | SELECT CustomerID, CompanyName FROM [Customers$] IN 'C:\My Documents\XLData.xls' 'EXCEL 5.0;' WHERE CustomerID Like 'A*'; – or – SELECT CustomerID, CompanyName FROM CustomersRange IN 'C:\My Documents\XLData.xls' 'EXCEL 5.0;' 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 Customers IN C:\DBASE\DATA\SALES dBASE IV; WHERE CustomerID Like A*; |
External database | SQL statement |
dBASE III or IV (by using DATABASE syntax). To retrieve data from a dBASE III table, substitute dBASE III for dBASE IV. | SELECT CustomerID FROM Customers 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 Customers IN C:\PARADOX\DATA\SALES Paradox 4.x; WHERE CustomerID Like A*; |
Paradox 3.x or 4.x (by using DATABASE syntax). To retrieve data from a Paradox version 3.x table, substitute Paradox 3.x for Paradox 4.x. | SELECT CustomerID FROM Customers IN [Paradox 4.x;DATABASE=C:\PARADOX\DATA\SALES;] WHERE CustomerID Like A*; |