Difference between revisions of "Microsoft Access OLE DB"

From database24
Jump to navigation Jump to search
(New structure / separate pages for every application)
 
(2 intermediate revisions by the same user not shown)
Line 25: Line 25:
 
* [[Microsoft Access OLE DB — Microsoft Access|Microsoft Access]]
 
* [[Microsoft Access OLE DB — Microsoft Access|Microsoft Access]]
 
* [[Microsoft Access OLE DB — Microsoft Excel|Microsoft Excel]]
 
* [[Microsoft Access OLE DB — Microsoft Excel|Microsoft Excel]]
−
* [[Microsoft Access OLE DB — Comma Separated Values (CSV)|CSV]]
+
* [[Microsoft Access OLE DB — Text (CSV, TSV, TXT)|CSV, TSV, TXT]]
 +
* [[Microsoft Access OLE DB — ODBC|ODBC]]
 
* [[Microsoft Access OLE DB — Hypertext Marckup Language (HTML)|HTML]]
 
* [[Microsoft Access OLE DB — Hypertext Marckup Language (HTML)|HTML]]
 
* [[Microsoft Access OLE DB — Microsoft Outlook|Microsoft Outlook]]
 
* [[Microsoft Access OLE DB — Microsoft Outlook|Microsoft Outlook]]
  
−
== Microsoft Access ==
+
* [[Microsoft Access OLE DB — Microsoft SharePoint|Microsoft SharePoint]]
−
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
 
−
 
 
−
<syntaxhighlight lang="sql" highlight="3">
 
−
SELECT *
 
−
  FROM tblTable
 
−
    IN 'c:\source.mdb'
 
−
</syntaxhighlight>
 
−
 
 
−
or by applying square brackets around the database path and prefixing the
 
−
referenced table with it
 
−
 
 
−
<syntaxhighlight lang="sql" highlight="2">
 
−
SELECT *
 
−
  FROM [c:\source.mdb].tblTable
 
−
</syntaxhighlight>
 
−
 
 
−
 
 
−
== Microsoft Excel ==
 
−
Every worksheet or (named) range is considered as table when accessing Microsoft Excel files.
 
−
 
 
−
<syntaxhighlight lang="sql" highlight="3">
 
−
SELECT *
 
−
  FROM [...]
 
−
    IN 'c:\source.xls'[EXCEL 8.0;];
 
−
</syntaxhighlight>
 
−
 
 
−
 
 
−
=== Named Range ===
 
−
Accessing data by naming ranges allows the developer to specify exactly what should be imported.
 
−
By this&mdash;and the optional parameters&mdash;it should be easy to retrieve exactly
 
−
the desired data and nothing else.
 
−
 
 
−
<syntaxhighlight lang="sql" highlight="2">
 
−
SELECT *
 
−
  FROM [NamedRange]
 
−
    IN 'c:\source.xls'[EXCEL 8.0;];
 
−
</syntaxhighlight>
 
−
 
 
−
=== Worksheet ===
 
−
The selection of worksheets is usually not recommended, as the selection will also
 
−
contain empty cells and maybe other unwanted content.
 
−
 
 
−
<syntaxhighlight lang="sql" highlight="2">
 
−
SELECT *
 
−
  FROM [Worksheet$]
 
−
    IN 'c:\source.xls'[EXCEL 8.0;];
 
−
</syntaxhighlight>
 
−
 
 
−
=== Reference ===
 
−
In rare cases it is necessary to retrieve the content of a certain cell range.
 
−
 
 
−
<syntaxhighlight lang="sql" highlight="2">
 
−
SELECT *
 
−
  FROM [Worksheet$A1:B2]
 
−
    IN 'c:\source.xls'[EXCEL 8.0;];
 
−
</syntaxhighlight>
 
−
 
 
−
=== 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&mdash;and highly recommended&mdash;to specify the content
 
−
of the text file by creating a ''schema.ini'' with the specification details.
 
−
 
 
−
<syntaxhighlight lang="sql" highlight="2-3">
 
−
SELECT *
 
−
  FROM source.csv
 
−
    IN 'c:\'[TEXT;];
 
−
</syntaxhighlight>
 
−
 
 
−
=== 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 [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 ==
 
−
It is possible to query Exchange / Outlook folders and address books as follows:
 
−
 
 
−
<syntaxhighlight lang="sql" highlight="2-3">
 
−
SELECT *
 
−
  FROM Inbox
 
−
    IN 'c:\temp\'[Exchange 4.0;MAPILEVEL=Account - Paul Stevens|;TABLETYPE=0;];
 
−
</syntaxhighlight>
 
−
 
 
−
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 ==
 
−
&hellip;
 
−
 
 
−
 
 
−
== Microsoft SharePoint ==
 
−
&hellip;
 
  
 
[[Category:Microsoft Access]]
 
[[Category:Microsoft Access]]
 
[[Category:SQL]]
 
[[Category:SQL]]

Latest revision as of 11:04, 15 November 2016

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;];
Retrieved from "http://wiki.database24.net/index.php?title=Microsoft_Access_OLE_DB&oldid=525"