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

Question

are there any EXCEL SQL syntax links / guides related to Microsoft OLE DB Provider for Jet 4.0?

For example, how to write constants like date?

What keywords / features / suggestions are available?

Answer 1

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

How to use ADO with Excel data from Visual Basic or VBA .

Of particular importance is how an existing common column data type is defined when it contains mixed data types .

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

ADO Provider Properties and Parameters: Microsoft Jet 4.0 Provider Properties

However, the details specific to Excel are better described in the previous article.

One important information you should know when using Excel and SQL:

ERROR: A memory leak occurs when you request an open Excel worksheet using ActiveX Data Objects (ADO)

What keywords / features / are available?

Regarding the use of Jet 4.0 SQL, this article is the best ever found:

Microsoft Jet 4.0 SQL Intermediate

In theory, the SQL section of the access help should matter, but it is of especially poor quality , and Jet 4.0 is particularly affected by poor coverage.

Again, not everything will work directly in Excel. For example, although you can use CREATE TABLE SQL DDL to create a new worksheet and workbook, you cannot create a NOT NULL column because it is physically impossible. In addition, Excel data types are less granular, for example, most numeric types are mapped to DOUBLE FLOAT .

Regarding expressions that can be used in SQL, the Jet 4.0 expression service uses VBA expression services in some way. Generally speaking, Jet can use any VBA 5.0 (not the latest version, which is VBA 6.0), which is not a method that supports values, but returns a single value, only simple internal data types (without arrays, without objects, etc. ) I think I'm right in saying that Microsoft never published the final list of VBA features supported by Jet 4.0. However, I believe that the list in the following article successfully matches the list of VBA functions that can be used in Jet 4.0:

How to configure Jet 4.0 to prevent the use of unsafe features in Access 2003

(The list is in the table under the subtitle "Use Sandbox Mode with Jet 4.0 Service Pack 3 for Jet 4.0.")

Note that some features behave differently in Jet 4.0 than in VBA. From head to toe I can think of two. IIF() may contain a label in Jet 4.0 (undocumented, AFAIK): in VBA, the conditions are TRUE and FALSE ; in Jet 4.0, only the agreed condition is evaluated. CDEC() (cast to DECIMAL ) function is broken in Jet 4.0 .

how to write type date constants? I mean, a way to express 2011.01.20 (this is a constant value) in SQL, for example, am I using '2011-01-20', or # 2011-01-20 #, or something else?

I know this as a "literal meaning."

This is the same as VBA, i.e. #m/d/yyyy# , so today the date will be #1/20/2011# . However, I prefer to use the ISO 8601 date format and single quotes (for portability) and always include a time field (since Jet 4.0 has only one temporary data type, which is DATETIME ), and use the CDATE() cast function to enforce regional settings to DATETIME , for example, today the date will be CDATE('2011-01-20 00:00:00') .

---

[Originally thinking that OP means: "How to create a column of type DATE ?" ]

you can use CREATE TABLE DDL for example.

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

Please note that although Jet 4.0 will abide by the DATETIME data type, there is no such limitation when the workbook is edited in Excel: if the Range table is expanded and non-timed data is added, then the data type 'seen' from Jet 4.0 may change as a result.

---

Answer 2

What keywords / features / suggestions are available?

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

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 can be found here.

Each function works in my SQL queries, so I think this is a complete list of 125 functions.