Any references/manuals on SQL in EXCEL with Microsoft OLE DB Provider for Jet 4.0?

Question

is there any references/manuals on SQL syntax in EXCEL connected with Microsoft OLE DB Provider for Jet 4.0 ?

For example, how do I write constants of type date?

What keywords/functions/clauses are available?

Udelle

Answer 1

When using Excel as a Jet 4.0 data source, the best reference I know of is:

How To Use ADO with Excel Data from Visual Basic or VBA.

Of particular importance is how an existing column's overall data type is determined when it contains mixed data types.

As regards the Microsoft OLE DB Provider for Jet 4.0, the most relevant article is this:

ADO Provider Properties and Settings: Microsoft Jet 4.0 Provider Properties

That said, the Excel-specific detail is better covered in the earlier article.

One important gotcha you should be aware of when using Excel and SQL:

BUG: Memory leak occurs when you query an open Excel worksheet by using ActiveX Data Objects (ADO)

What keywords/functions/clauses are available?

As regards using Jet 4.0 SQL, this article is the best I've ever found:

Intermediate Microsoft Jet 4.0 SQL

In theory, the SQL section of the Access Help should be relevant but it is of a particularly low quality and Jet 4.0 specifically suffers poor coverage.

Again, not everything will work directly in Excel. For example, though you can use CREATE TABLE SQL DDL to create a new worksheet and workbook, you can't create a NOT NULLable column because this isn't physically possible. Also, Excel's data types as less granular e.g. most numeric types map to DOUBLE FLOAT.

As regards expressions that can be used in SQL, the Jet 4.0 expression service somehow uses the VBA expression services. Broadly speaking, Jet can use any VBA 5.0 (not the latest version, being VBA 6.0) function that is not a method that involes values, and returns returns a single value, of simple intrinsic data types only (no arrays, no objects, etc). I think I am correct in saying that Microsoft have never explicitly published a definitive list of the VBA functions that are supported by Jet 4.0. However, I believe a list in the following article happens to coincide perfectly with the list of VBA functions that useable in Jet 4.0:

How to configure Jet 4.0 to prevent unsafe functions from running in Access 2003

(The list is in a table under the subheading "Use Sandbox mode operations with Jet 4.0 Service Pack 3 and later".)

Note that some functions behave differently in Jet 4.0 than in VBA. Off the top of my head, I can think of two. IIF() can shortcut in Jet 4.0 (undocumented, AFAIK): in VBA, both TRUE and FALSE conditions are evaluated, in Jet 4.0 only the matched condition is evaluated. CDEC() (cast to DECIMAL) function is broken in Jet 4.0.

how do I write constants of type date? I mean, the way to express 2011.01.20 (this constant value) in SQL, for example, do I use '2011-01-20', or #2011-01-20#, or something else?

I know this as a 'literal value'.

This is the same as VBA i.e. #m/d/yyyy#, so today's date would be #1/20/2011#. However, I prefer to use ISO 8601 date format and single quotes (for portability) and always include the time field (because Jet 4.0 has but one temporal data type, being DATETIME) and, to ensure regional settings are honoured, use the CDATE() cast to DATETIME function e.g. today's date would be CDATE('2011-01-20 00:00:00').

---

[Originally thinking the OP meant, "How do I create a column of type DATE?"]

you can use CREATE TABLE DDL e.g.

CREATE TABLE [Excel 8.0;DATABASE=C:\MyNewWorkbook.xls].MyTable 
(
 my_date_col DATETIME
);

Note that while Jet 4.0 will honour the data type of DATETIME, there is no such constraint when the workbook is edited in Excel: if the table Range is extended and non-temporal data added then the data type 'seen' by Jet 4.0 could change as a result.

---

Whaler

By saying writting constants of type date, I mean, the way to express 2011.01.20 (this constant value) in SQL, for example, do I use '2011-01-20', or #2011-01-20#, or something else? – Udelle

Thank you, that is a very comprehensive link list. All that I would add is that .xlsb files seem to be worse than xlsm or xls; and a reliable (if cumbersome) workround is to read the data indirectly: dump the data into csv files in a local temp folder and read them using the Jet Text ISAM. – Richmond

Answer 2

What keywords/functions/clauses are available?

Regarding the functions list, I found the following names list in the file MSMDCB80.DLL:

YEAR, WEEKDAY, VarType, Val, UCase$, UCase, TypeName, TRIM$, TRIM, TIMEVALUE, TimeSerial, Timer, TIME$, TIME, TAN, SYD, Switch, String$, String, StrConv, StrComp, Str$, Str, Sqr, Space$, Space, SLN, SIN, Sgn, SECOND, RTrim$, RTrim,Round, Rnd, RIGHTB$, RIGHTB, RIGHT$, RIGHT, RGB, RATE, QBColor, PV, PPMT, PMT, Partition, Oct$, Oct, NPV, NPER, NOW, MONTH, MIRR, MINUTE, MIDB$, MIDB, MID$, MID, LTrim$, LTrim, LOG, LENB, LEN, LEFTB$, LEFTB, LEFT$, LEFT, LCase$, LCase, IsObject, IsNumeric, IsNull, ISERROR, IsEmpty, IsDate, IRR, IPMT, INT, InStr, IMEStatus, IIF, HOUR, Hex$, Hex, Fv, Format$, Format, Fix, EXP, Error$, Error, DDB, Day, DATEVALUE, DATESERIAL, DatePart, DateDiff, DATEADD, DATE$, Date, CVErr, CvDate, CVAR, CSTR, CSNG, COS, CLNG, CINT, CHRW$, CHRW, CHRB$, CHRB, CHR$, CHR, Choose, CDBL, CDATE, CCUR, CBYTE, CBOOL, ATN, ASCW, ASCB, ASC, Array, ABS

Their arguments and description could be found here

Every function is working in my SQL queries, so I guess, this is a comprehensive list of 125 functions.

Famine

StackOverflow: making up for vendor laziness since 2008 – Shivers

I couldn't get Array to work in a SQL query. IIF behaves differently to VBA's IIf (able to short-circuit). No mention of CDEC in your list but then it doesn't do anything useful in SQL anyhow! – Whaler

I think your list is the same as the one I link to in my answer: support.microsoft.com/en-gb/kb/294698 – Whaler