Difference between revisions of "Microsoft Access OLE DB"

From database24
Jump to navigation Jump to search
Line 24: Line 24:
  
  
== Microsoft Access ==
+
== SELECT ==
 +
 
 +
=== Microsoft Access ===
 
This allows you to easily refer to other database tables and queries  
 
This allows you to easily refer to other database tables and queries  
 
without the need to create a linked table.
 
without the need to create a linked table.
Line 45: Line 47:
  
  
== Microsoft Excel ==
+
=== Microsoft Excel ===
 
Every worksheet or (named) range is considered as table when accessing Microsoft Excel files.
 
Every worksheet or (named) range is considered as table when accessing Microsoft Excel files.
  
Line 55: Line 57:
  
  
=== Named Range ===
+
==== Named Range ====
 
Accessing data by naming ranges allows the developer to specify exactly what should be imported.  
 
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  
 
By this—and the optional parameters—it should be easy to retrieve exactly  
Line 66: Line 68:
 
</syntaxhighlight>
 
</syntaxhighlight>
  
=== Worksheet ===
+
==== Worksheet ====
 
The selection of worksheets is usually not recommended, as the selection will also
 
The selection of worksheets is usually not recommended, as the selection will also
 
contain empty cells and maybe other unwanted content.
 
contain empty cells and maybe other unwanted content.
Line 76: Line 78:
 
</syntaxhighlight>
 
</syntaxhighlight>
  
=== Reference ===
+
==== Reference ====
 
In rare cases it is necessary to retrieve the content of a certain cell range.
 
In rare cases it is necessary to retrieve the content of a certain cell range.
  
Line 85: Line 87:
 
</syntaxhighlight>
 
</syntaxhighlight>
  
=== Optional Parameters ===
+
==== Optional Parameters ====
 
;HDR
 
;HDR
 
:The header parameter specifies, whether the first row should be interpreted as column headers.
 
:The header parameter specifies, whether the first row should be interpreted as column headers.
Line 97: Line 99:
  
  
== Text file ==
+
=== Text file ===
 
When dealing with text files the folder of the file is interpreted as database  
 
When dealing with text files the folder of the file is interpreted as database  
 
and the text file itself as table source.
 
and the text file itself as table source.
Line 110: Line 112:
 
</syntaxhighlight>
 
</syntaxhighlight>
  
=== Optional Parameters ===
+
==== Optional Parameters ====
 
;HDR
 
;HDR
 
:The header parameter specifies, whether the first row should be interpreted as column headers.
 
:The header parameter specifies, whether the first row should be interpreted as column headers.
Line 116: Line 118:
  
  
== HTML ==
+
=== HTML ===
 
While this works theoretically in general, there are many problems with selecting data from web sites. These [http://ewbi.blogs.com/develops/2006/12/reading_html_ta.html HTML specifica] make it impracticable to work with HTML tables this way.
 
While this works theoretically in general, there are many problems with selecting data from web sites. These [http://ewbi.blogs.com/develops/2006/12/reading_html_ta.html HTML specifica] make it impracticable to work with HTML tables this way.
  
  
== Microsoft Exchange ==
+
=== Microsoft Exchange ===
 
It is possible to query Exchange / Outlook folders and address books as follows:
 
It is possible to query Exchange / Outlook folders and address books as follows:
  
Line 133: Line 135:
  
  
== ODBC ==
+
=== ODBC ===
 +
&hellip;
 +
 
 +
 
 +
=== Microsoft Sharepoint ===
 +
&hellip;
 +
 
 +
 
 +
== INSERT INTO ==
 +
 
 +
=== Microsoft Access ===
 +
&hellip;
 +
 
 +
 
 +
=== Microsoft Excel ===
 +
&hellip;
 +
 
 +
 
 +
=== Text file ===
 +
&hellip;
 +
 
 +
 
 +
=== HTML ===
 +
&hellip;
 +
 
 +
 
 +
=== Microsoft Exchange ===
 +
&hellip;
 +
 
 +
 
 +
=== ODBC ===
 +
&hellip;
 +
 
 +
 
 +
=== Microsoft Sharepoint ===
 +
&hellip;
 +
 
 +
 
 +
== SELECT INTO ==
 +
 
 +
=== Microsoft Access ===
 +
&hellip;
 +
 
 +
 
 +
=== Microsoft Excel ===
 +
&hellip;
 +
 
 +
 
 +
=== Text file ===
 +
&hellip;
 +
 
 +
 
 +
=== HTML ===
 +
&hellip;
 +
 
 +
 
 +
=== Microsoft Exchange ===
 +
&hellip;
 +
 
 +
 
 +
=== ODBC ===
 
&hellip;
 
&hellip;
  
  
== Microsoft Sharepoint ==
+
=== Microsoft Sharepoint ===
 
&hellip;
 
&hellip;

Revision as of 01:34, 16 April 2012

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 (see the Overview provided by Microsoft).

The ISAM OLEDB type must be put in square brackets and and with a semicolon like in this example for an Microsoft Excel workbook:

SELECT *
  FROM [...] 
    IN '...'[EXCEL 8.0;];

If you need to specify optional parameters they have to be appended to the ISAM type:

SELECT *
  FROM [...] 
    IN '...'[EXCEL 8.0;HDR=yes;IMEX=0;];


SELECT

Microsoft Access

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

The database can be specified by the use of the IN clause

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

or by applying square brackets around the database path and prefixing the referenced table with it

SELECT *
  FROM [c:\source.mdb].tblTable


Microsoft Excel

Every worksheet or (named) range is considered as table when accessing Microsoft Excel files.

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


Named Range

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.

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

Worksheet

The selection of worksheets is usually not recommended, as the selection will also contain empty cells and maybe other unwanted content.

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

Reference

In rare cases it is necessary to retrieve the content of a certain cell range.

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

Optional Parameters

HDR
The header parameter specifies, whether the first row should be interpreted as column headers.
Possible values: HDR=yes, HDR=no
IMEX
In order to identify the content of a column usually the first eight rows are scanned for values and the rest of the column is identified accordingly.
Unfortunately this behaviour often leads to misinterpretation and error values.
Setting this parameter to 0 suppresses the value type interpretation and enforces the data type "text" for all columns.
Possible values: IMEX=0


Text file

When dealing with text files the folder of the file is interpreted as database and the text file itself as table source.

It is possible—and highly recommended—to specify the content of the text file by creating a schema.ini with the specification details.

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

Optional Parameters

HDR
The header parameter specifies, whether the first row should be interpreted as column headers.
Possible values: HDR=yes, HDR=no


HTML

While this works theoretically in general, there are many problems with selecting data from web sites. These HTML specifica make it impracticable to work with HTML tables this way.


Microsoft Exchange

It is possible to query Exchange / Outlook folders and address books as follows:

SELECT *
  FROM Inbox 
    IN 'c:\temp\'[Exchange 4.0;MAPILEVEL=Account - Paul Stevens|;TABLETYPE=0;];

where Account - Paul Stevens has to be replaced by the exact name of the Inbox followed by the pipe (|). This works for Contacts, Calendar and other folders too.


ODBC


Microsoft Sharepoint


INSERT INTO

Microsoft Access


Microsoft Excel


Text file


HTML


Microsoft Exchange


ODBC


Microsoft Sharepoint


SELECT INTO

Microsoft Access


Microsoft Excel


Text file


HTML


Microsoft Exchange


ODBC


Microsoft Sharepoint