Difference between revisions of "Microsoft Access OLE DB"
Jump to navigation
Jump to search
| Line 9: | Line 9: | ||
=== Tables and Queries === | === Tables and Queries === | ||
| − | <syntaxhighlight lang= | + | <syntaxhighlight lang=sql> |
SELECT * | SELECT * | ||
FROM tblTable | FROM tblTable | ||
| − | IN 'c:\ | + | IN 'c:\source.mdb' |
</syntaxhighlight> | </syntaxhighlight> | ||
| Line 20: | Line 20: | ||
=== Named Range === | === Named Range === | ||
| − | <syntaxhighlight lang= | + | <syntaxhighlight lang=sql> |
SELECT * | SELECT * | ||
FROM [NamedRange] | FROM [NamedRange] | ||
| − | IN 'c:\ | + | IN 'c:\source.xls'[EXCEL 8.0;]; |
</syntaxhighlight> | </syntaxhighlight> | ||
| Line 29: | Line 29: | ||
=== Worksheet === | === Worksheet === | ||
| − | <syntaxhighlight lang= | + | <syntaxhighlight lang=sql> |
SELECT * | SELECT * | ||
FROM [Worksheet$] | FROM [Worksheet$] | ||
| − | IN 'c:\ | + | IN 'c:\source.xls'[EXCEL 8.0;]; |
</syntaxhighlight> | </syntaxhighlight> | ||
=== Reference === | === Reference === | ||
| − | <syntaxhighlight lang= | + | <syntaxhighlight lang=sql> |
SELECT * | SELECT * | ||
FROM [Worksheet$A1:B2] | FROM [Worksheet$A1:B2] | ||
| − | IN 'c:\ | + | IN 'c:\source.xls'[EXCEL 8.0;]; |
</syntaxhighlight> | </syntaxhighlight> | ||
| Line 47: | Line 47: | ||
== Text file == | == Text file == | ||
| − | <syntaxhighlight lang= | + | <syntaxhighlight lang=sql> |
SELECT * | SELECT * | ||
| − | FROM | + | FROM source.csv |
IN 'c:\'[TEXT;]; | IN 'c:\'[TEXT;]; | ||
</syntaxhighlight> | </syntaxhighlight> | ||
Revision as of 14:53, 26 August 2010
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
Tables and Queries
SELECT *
FROM tblTable
IN 'c:\source.mdb'
This allows you to easily refer to other databases' tables and queries without the need to create a linked table.
Microsoft Excel
Named Range
SELECT *
FROM [NamedRange]
IN 'c:\source.xls'[EXCEL 8.0;];
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.
Worksheet
SELECT *
FROM [Worksheet$]
IN 'c:\source.xls'[EXCEL 8.0;];
Reference
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
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)