Difference between revisions of "Microsoft Access OLE DB"

From database24
Jump to navigation Jump to search
 
(10 intermediate revisions by the same user not shown)
Line 1: Line 1:
 
== General ==
 
== General ==
 
It is possible to access some resources without writing any line of code.  
 
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.
+
All you need is to write a query which makes use of ISAM OLEDB (see the
 +
[http://msdn.microsoft.com/en-us/library/aa164914(office.10).aspx Overview]
 +
provided by Microsoft).
  
−
For further reading:
+
The ISAM OLEDB type must be put in square brackets and and with a semicolon
−
* [http://msdn.microsoft.com/en-us/library/aa164914(office.10).aspx Overview]
+
like in this example for an Microsoft Excel workbook:
−
 
 
−
 
 
−
== 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
 
  
 
<syntaxhighlight lang="sql" highlight="3">
 
<syntaxhighlight lang="sql" highlight="3">
 
SELECT *
 
SELECT *
−
   FROM tblTable
+
   FROM [...]
−
     IN 'c:\source.mdb'
+
     IN '...'[EXCEL 8.0;];
 
</syntaxhighlight>
 
</syntaxhighlight>
  
−
or by applying square brackets around the database path and prefixing the
+
If you need to specify optional parameters they have to be appended to the
−
referenced table with it
+
ISAM type:
−
 
 
−
<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">
 
<syntaxhighlight lang="sql" highlight="3">
 
SELECT *
 
SELECT *
 
   FROM [...]  
 
   FROM [...]  
−
     IN 'c:\source.xls'[EXCEL 8.0;];
+
     IN '...'[EXCEL 8.0;HDR=yes;IMEX=0;];
 
</syntaxhighlight>
 
</syntaxhighlight>
  
 +
* [[Microsoft Access OLE DB &mdash; Microsoft Access|Microsoft Access]]
 +
* [[Microsoft Access OLE DB &mdash; Microsoft Excel|Microsoft Excel]]
 +
* [[Microsoft Access OLE DB &mdash; Text (CSV, TSV, TXT)|CSV, TSV, TXT]]
 +
* [[Microsoft Access OLE DB &mdash; ODBC|ODBC]]
 +
* [[Microsoft Access OLE DB &mdash; Hypertext Marckup Language (HTML)|HTML]]
 +
* [[Microsoft Access OLE DB &mdash; Microsoft Outlook|Microsoft Outlook]]
  
−
=== Named Range ===
+
* [[Microsoft Access OLE DB &mdash; Microsoft SharePoint|Microsoft SharePoint]]
−
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.
 
−
 
 
−
== Exchange ==
 
−
It is possible to query Exchange / Outlook folders and address books as follows:
 
−
 
 
−
<syntaxhighlight lang=sql>
 
−
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 ==
 
−
ODBC is considered as deprecated technology and does nothing which OLEDB is not capable of, but for the sake of backwards compatibility it is possible to use ODBC connections too.
 
−
 
 
−
== Optional Parameters ==
 
−
The optional parameters can be appended to the required part in the brackets. Example:
 
−
 
 
−
<syntaxhighlight lang=sql>
 
−
[EXCEL 8.0;HDR=yes;IMEX=0;]
 
−
</syntaxhighlight>
 
−
 
 
−
=== HDR ===
 
−
Does the data source have headings?
 
−
* Values: yes|no
 
−
* Default: yes
 
  
−
=== IMEX ===
+
[[Category:Microsoft Access]]
−
Should the data be imported "as is" in text format or should windows try to guess the value type of each column?
+
[[Category:SQL]]
−
* Values: 0|1
 
−
* Default: 1 (guess)
 

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"