Difference between revisions of "Microsoft Access OLE DB"

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;];

Microsoft SharePoint

@@ Line 23: / Line 23: @@
 </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]]
-== SELECT ==
+* [[Microsoft Access OLE DB &mdash; Microsoft SharePoint|Microsoft SharePoint]]
-=== Microsoft Access ===
+[[Category:Microsoft Access]]
-This allows you to easily refer to other database tables and queries
+[[Category:SQL]]
-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;
-== 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;
-=== Microsoft Sharepoint ===
-&hellip;

Difference between revisions of "Microsoft Access OLE DB"

Latest revision as of 11:04, 15 November 2016

General

Navigation menu

Search