Difference between revisions of "Microsoft Access OLE DB"

From database24
Jump to navigation Jump to search
 
(12 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. All you need is to write a query which makes use of ISAM OLEDB.
+
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
 +
[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 ==
+
<syntaxhighlight lang="sql" highlight="3">
−
 
 
−
=== Tables and Queries ===
 
−
<syntaxhighlight lang=sql>
 
−
SELECT *
 
−
  FROM tblTable
 
−
    IN 'c:\source.mdb'
 
−
</syntaxhighlight>
 
−
 
 
−
This allows you to easily refer to other databases' tables and queries without the need to create a linked table.
 
−
 
 
−
== Microsoft Excel ==
 
−
 
 
−
=== Named Range ===
 
−
<syntaxhighlight lang=sql>
 
−
SELECT *
 
−
  FROM [NamedRange]
 
−
    IN 'c:\source.xls'[EXCEL 8.0;];
 
−
</syntaxhighlight>
 
−
 
 
−
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.
 
−
 
 
−
=== Worksheet ===
 
−
<syntaxhighlight lang=sql>
 
−
SELECT *
 
−
  FROM [Worksheet$]
 
−
    IN 'c:\source.xls'[EXCEL 8.0;];
 
−
</syntaxhighlight>
 
−
 
 
−
=== Reference ===
 
−
<syntaxhighlight lang=sql>
 
−
SELECT *
 
−
  FROM [Worksheet$A1:B2]
 
−
    IN 'c:\source.xls'[EXCEL 8.0;];
 
−
</syntaxhighlight>
 
−
 
 
−
=== Optional Parameters ===
 
−
* HDR
 
−
* IMEX
 
−
 
 
−
== Text file ==
 
−
<syntaxhighlight lang=sql>
 
 
SELECT *
 
SELECT *
−
   FROM source.csv
+
   FROM [...]
−
     IN 'c:\'[TEXT;];
+
     IN '...'[EXCEL 8.0;];
 
</syntaxhighlight>
 
</syntaxhighlight>
  
−
=== Optional Parameters ===
+
If you need to specify optional parameters they have to be appended to the
−
* HDR
+
ISAM type:
−
 
 
−
 
 
−
== 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 ==
+
<syntaxhighlight lang="sql" highlight="3">
−
It is possible to query Exchange / Outlook folders and address books as follows:
 
−
 
 
−
<syntaxhighlight lang=sql>
 
 
SELECT *
 
SELECT *
−
   FROM Inbox
+
   FROM [...]
−
     IN 'c:\temp\'[Exchange 4.0;MAPILEVEL=Account - Paul Stevens|;TABLETYPE=0;];
+
     IN '...'[EXCEL 8.0;HDR=yes;IMEX=0;];
 
</syntaxhighlight>
 
</syntaxhighlight>
  
−
where ''Account - Paul Stevens'' has to be replaced by the exact name of the Inbox followed by the pipe (|).
+
* [[Microsoft Access OLE DB &mdash; Microsoft Access|Microsoft Access]]
−
This works for Contacts, Calendar and other folders too.
+
* [[Microsoft Access OLE DB &mdash; Microsoft Excel|Microsoft Excel]]
−
 
+
* [[Microsoft Access OLE DB &mdash; Text (CSV, TSV, TXT)|CSV, TSV, TXT]]
−
== ODBC ==
+
* [[Microsoft Access OLE DB &mdash; ODBC|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.
+
* [[Microsoft Access OLE DB &mdash; Hypertext Marckup Language (HTML)|HTML]]
−
 
+
* [[Microsoft Access OLE DB &mdash; Microsoft Outlook|Microsoft Outlook]]
−
== 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 ===
+
* [[Microsoft Access OLE DB &mdash; Microsoft SharePoint|Microsoft SharePoint]]
−
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"