ACCESS interacts with other external data

As an application in the MSOffice office suite, ACCESS often needs to interact with other data sources, such as using data in EXCEL or text record files for query, you can also output the query results to the EXCEL data table or other data files. In addition to the commonly used pour out function, you can also through the JET-SQL I

As an application in the MS Office suite, ACCESS often needs to interact with other data sources, such as using data in EXCEL or text record files in queries, you can also output the query results to the EXCEL data table or other data files. In addition to the common import/export function, you can also through the JET-SQL I

As an application in the MS Office suite, ACCESS often needs to interact with other data sources, such as using data in EXCEL or text record files in queries, you can also output the query results to the EXCEL data table or other data files. IN addition to the common import/export function, can also be achieved through the IN clause of the JET-SQL.

External Database
It can be an ODBC database (such as Microsoft SQL Server) residing on a remote Server or an external database, such as Paradox, dBASE, Microsoft FoxPro, Microsoft Excel, Microsoft Access, Lotus 1-2-3, HTML, and Text.

The following describes the IN clause IN Microsoft Jet SQL reference.

IN clause

Identifies tables in an external database, such as dBASE, Paradox database, or Microsoft Jet External Database, which can be connected by the Microsoft Jet Database Engine.

Syntax

Identify target table:

[SELECT | INSERT]DestinationIN
{Path| ["Path""Type"] | [""[Type; DATABASE =Path]}

Identify the data source table:

FROMTableexpressionIN
{Path| ["Path""Type"] | [""[Type; DATABASE =Path]}

The SELECT statement containing the IN clause has the following parts:

Part	Description
Destination	The name of the External table to insert data into the table.
Tableexpression	Table name. You can obtain data from these tables. This variable may be a separate table name, a saved query, or inner join, left join, or right join.
Path	Directory or file path, and these directories or files containTable.
Type	Name of the database type. If the database is not a Microsoft Jet Database (such as dBASE III, dBASE IV, Paradox 3. x, or Paradox 4.x), use this type to createTable.

Description

With IN, you can only connect to one external database at a time.

In some cases,PathParameters can reference directories containing database files. For example, when dBASE, FoxPro, or Paradox database table is used,PathThe parameter specifies the directory that contains the. dbf or. db file. ByDestinationOrTableexpressionYou can obtain the name of the table file.

To specify a non-Microsoft JET Database, You can append a semicolon (;) after the name and enclose it with single quotation marks ('') or (" ") double quotation marks. For example, either 'dbase IV; 'or "dBASE IV;" can be used.

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

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

... FROM Table IN "C:/DBASE/DATA/SALES" "dBASE IV ;"

Note:

To improve performance and make it easy to use, use a chain table instead of IN.

The IN reserved words can also be used as comparison operators IN expressions. For more information, seeInOperator.

IN clause example

The following list shows how to use the IN clause to capture data from external databases. In each example, assume that the customer table is stored in an external database.

External Database	SQL statement
Microsoft? Jet Database	SELECT CustomerID FROM MERs IN OtherDB. mdb WHERE CustomerID Like "*";
DBASE III or IV. The captured data is composed of a dBASE III table, replacing "dBASE IV;" with "dBASE III ;".	SELECT CustomerID FROM Customer IN "C:/DBASE/DATA/SALES" "dBASE IV ;" WHERE CustomerID Like "*";
DBASE III or IV uses the database syntax.	SELECT CustomerID FROM Customer IN "" [dBASE IV; Database = C:/DBASE/DATA/SALES;] WHERE CustomerID Like "*";
Paradox 3.x or 4. x. To capture data from a Paradox version 3.x table, replace "Paradox 4.x;" with "Paradox 3.x ;".	SELECT CustomerID FROM Customer IN "C:/PARADOX/DATA/SALES" "Paradox 4.x ;" WHERE CustomerID Like "*";
Paradox 3.x or 4.x uses the database syntax.	SELECT CustomerID FROM Customer IN "" [Paradox 4.x; Database = C:/PARADOX/DATA/SALES;] WHERE CustomerID Like "*";
Microsoft EXCEL worksheet	SELECT CustomerID, CompanyName FROM [MERs $] IN "c:/documents/xldata.xls" "EXCEL 5.0 ;" WHERE CustomerID Like "*" Order by CustomerID;
A name range in the worksheet	SELECT CustomerID, CompanyName FROM CustomersRange IN "c:/documents/xldata.xls" "EXCEL 5.0 ;" WHERE CustomerID Like "*" Order by CustomerID;

The above is taken from Microsoft Jet SQL reference

The JET type is all types recorded in the ISAM registry.

Example:

Text File [Text; DATABASE = Text file path]. [Text file name]

Select * from [Text; DATABASE = c:/tempw.test.txt]

EXCEL [Excel 8.0; Database = fullFileName]. [SheetName]

[Excel 8.0; Database = fullFileName]. [SheetName $ A1: B10]

[Excel 8.0; Database = fullFileName]. [Name]

SELECT * INTO [Excel 8.0; Database = c:/book1.xls]. [Sheet1] FROM MERs
SELECT * INTO [Sheet1] IN ''[Excel 8.0; Database = c:/book1.xls] FROM MERs
SELECT * INTO [Sheet1] IN 'C:/book1.xls ''excel 8.0;' FROM MERs"
Insert into [Sheet1 $] IN ''[Excel 8.0; Database = c:/book1.xls] SELECT * FROM MERs
Insert into [Sheet1 $] IN 'C:/book1.xls ''excel 8.0; 'select * FROM MERs

Select * from [Excel 8.0; Database = c:/info. XLS]. [sheet1 $ A1: B5]

Select * from [Excel 8.0; HDR = Yes; IMEX = 1; Database = c:/info. XLS]. [Sheet1 $ C5: B10]

Select * from [C:/temp/access/db1.mdb]. SC

Select * from [; pwd = 123; database = C:/temp/access/db1.mdb]. SC

SELECT * FROM table in odbc [ODBC; Driver = {Microsoft ODBC for Oracle}; Server = XE; Uid = SYSTEM; PWD = 123;];

SELECT * FROM [ODBC; Driver = SQL Server; Server = servername; DataBase = dbname; UID = sa; PWD = 123]. table1