Any References/Manuals on SQL in Excel with Microsoft Ole Db Provider for Jet 4.0

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

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.

---

Microsoft Jet OLEDB 4.0 SQL Function Reference with ADO?

For question 1, this SO answer lists some good resources, including a link to an Intermediate SQL for Jet 4.0 article.

For question 2, do a search for the Iif and Switch functions.

OLE DB provider 'Microsoft.Jet.OLEDB.4.0' cannot be used for distributed queries

According to this thread,:

Microsoft.Jet.OLEDB.4.0 is not supported for 64-bit OS

Assuming you are running SQL Server 64-bit, you likely need the 64-bit Microsoft Access Database Engine 2010 Redistributable.

And be aware that there is a minor wrinkle when trying to install the software if the other version is already installed. In this case install the second version from the command line using the /passive switch. According to this thread:

Launching the install of a Microsoft ACE OLEDB Provider on a machine with an Office install other than the current one (e.g. 32 on 64) will cause the install to fail. To have it run properly you need to launch it from a command line with the “/passive” argument specified.

That is talking about an existing Office install but the same applies to coexisting database engine installations.

EDIT: Also make sure to use "Microsoft.ACE.OLEDB.12.0" not "Microsoft.Jet.OLEDB.4.0" for the provider string. (Props to @Rumi)

Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine

I found a solution for this problem. The issue I described in my question occured basically due to the incompatibility of the Microsoft.Jet.OLEDB.4.0 driver in 64 bit OS.

So if we are using Microsoft.Jet.OLEDB.4.0 driver in a 64 bit server, we have to force our application to build in in 32 bit mode (This is the answer I found when I did an extensive search for this known issue) and that causes other part of my code to break.

Fortunately, now Microsoft has released a 64 bit compatible 2010 Office System Driver which can be used as replacement for the traditional Microsoft.Jet.OLEDB.4.0 driver. It works both in 32 bit as well as 64 bit servers. I have used it for Excel file manipulation and it worked fine for me in both the environments. But this driver is in BETA.

You can download this driver from Microsoft Access Database Engine 2010 Redistributable

OleDB not supported in 64bit mode?

Here is a discussion of what to do about deprecated MDAC. I am afraid the answer is not very satisfying ...

These new or converted Jet
applications can continue to use Jet
with the intention of using Microsoft
Office 2003 and earlier files (.mdb
and .xls) for non-primary data
storage. However, for these
applications, you should plan to
migrate from Jet to the 2007 Office
System Driver. You can download the
2007 Office System Driver, which
allows you to read from and write to
pre-existing files in either Office
2003 (.mdb and .xls) or the Office
2007 (*.accdb, *.xlsm, *.xlsx and
*.xlsb) file formats. IMPORTANT Please read the 2007 Office System End User
License Agreement for specific usage
limitations.

Note: SQL Server applications can also
access the 2007 Office System, and
earlier, files from SQL Server
heterogeneous data connectivity and
Integrations Services capabilities as
well, via the 2007 Office System
Driver. Additionally, 64-bit SQL
Server applications can access to
32-bit Jet and 2007 Office System
files by using 32-bit SQL Server
Integration Services (SSIS) on 64-bit
Windows.

---

Related Topics