Difference between revisions of "Microsoft Access OLE DB"
| 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. | ||
For further reading: | For further reading: | ||
* [http://msdn.microsoft.com/en-us/library/aa164914(office.10).aspx Overview] | * [http://msdn.microsoft.com/en-us/library/aa164914(office.10).aspx Overview] | ||
| + | |||
== Microsoft Access == | == 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> | ||
SELECT * | SELECT * | ||
FROM tblTable | FROM tblTable | ||
| Line 14: | Line 19: | ||
</syntaxhighlight> | </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 == | == Microsoft Excel == | ||
| + | Every worksheet or (named) range is considered as table when accessing Microsoft Excel files. | ||
| + | |||
| + | <syntaxhighlight lang="sql" highlight="3"> | ||
| + | SELECT * | ||
| + | FROM [''RangeQualifier''] | ||
| + | IN 'c:\source.xls'[EXCEL 8.0;]; | ||
| + | </syntaxhighlight> | ||
| + | |||
=== Named Range === | === Named Range === | ||
| − | <syntaxhighlight lang=sql> | + | 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. | ||
| + | |||
| + | <syntaxhighlight lang="sql" highlight="2"> | ||
SELECT * | SELECT * | ||
FROM [NamedRange] | FROM [NamedRange] | ||
| Line 25: | Line 49: | ||
</syntaxhighlight> | </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"> | |
| − | <syntaxhighlight lang=sql> | ||
SELECT * | SELECT * | ||
FROM [Worksheet$] | FROM [Worksheet$] | ||
| Line 35: | Line 60: | ||
=== Reference === | === Reference === | ||
| − | <syntaxhighlight lang=sql> | + | In rare cases it is necessary to retrieve the content of a certain cell range. |
| + | |||
| + | <syntaxhighlight lang="sql" highlight="2"> | ||
SELECT * | SELECT * | ||
FROM [Worksheet$A1:B2] | FROM [Worksheet$A1:B2] | ||
Revision as of 01:08, 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.
For further reading:
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 [''RangeQualifier'']
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
- IMEX
Text file
SELECT *
FROM source.csv
IN 'c:\'[TEXT;];
Optional Parameters
- HDR
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.
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
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:
[EXCEL 8.0;HDR=yes;IMEX=0;]
HDR
Does the data source have headings?
- Values: yes|no
- Default: yes
IMEX
Should the data be imported "as is" in text format or should windows try to guess the value type of each column?
- Values: 0|1
- Default: 1 (guess)