Microsoft Access OLE DB

General

It is possible to access some resources without writing any line of code. All you need is to write a query which makes use of ISAM OLEDB.

For further reading:

• Overview
• HTML specifica

Microsoft Access

Tables and Queries

SELECT *
  FROM tblTable 
    IN 'c:\data.mdb'

This allows you to easily refer to other databases' tables and queries without the need to create a linked table.

Microsoft Excel

Named Range

SELECT *
  FROM [NamedRange] 
    IN 'c:\data.xls'[EXCEL 8.0;];

Accessing data by naming ranges allows the developer to specify exactly what should be imported. By this—and the optional parameters—it should be easy to retrieve exactly the desired data and nothing else.

Worksheet

SELECT *
  FROM [Worksheet$] 
    IN 'c:\data.xls'[EXCEL 8.0;];

Reference

SELECT *
  FROM [Worksheet$A1:B2] 
    IN 'c:\data.xls'[EXCEL 8.0;];

Optional Parameters

• HDR
• IMEX

Text file

SELECT *
  FROM data.csv 
    IN 'c:\'[TEXT;];

Optional Parameters

• HDR

Optional Parameters

The optional parameters can be appended to the required part in the brackets. Example:

[EXCEL 8.0;HDR=yes;IMEX=0;]

HDR

Does the data source have headings?

• Values: yes|no
• Default: yes

IMEX

Should the data be imported "as is" in text format or should windows try to guess the value type of each column?

• Values: 0|1
• Default: 1 (guess)