Run-time error -2147217900 (80040e14) - Join expression not supported

[Jay]

I'm running a third-party database app that provides an xla file for
Excel for querying the back-end database (in MSSQL).

We're upgrading our application servers and moving from Excel 2003 Pro
(32-bit) under Windows Server 2003 64-bit to Excel 2010 Pro Plus (32-
bit) on Windows Server 2008 R2 (64-bit.)

Now, however, I'm running into the above error when trying to run a
query from Excel. Strangely it works perfectly when I run this from my
Windows 7 (64-bit) PC with Excel 2010 Pro Plus (32-bit). So I'm ruling
out a syntax error that 2010 is nit-picking over that 2003 didn't.

I've run it on the app server with domain admin rights, so I can't
imagine it's a permission issue. I've compared installed features for
Excel and things like .NET and can't find anything relevant on my Win7
box that's missing on the Win2008 box.

Any ideas? I do have an open call with the third-party company, but
it's going very slow....

 

[GS]

Jay has brought this to us :

I'm running a third-party database app that provides an xla file for
Excel for querying the back-end database (in MSSQL).

We're upgrading our application servers and moving from Excel 2003 Pro
(32-bit) under Windows Server 2003 64-bit to Excel 2010 Pro Plus (32-
bit) on Windows Server 2008 R2 (64-bit.)

Now, however, I'm running into the above error when trying to run a
query from Excel. Strangely it works perfectly when I run this from my
Windows 7 (64-bit) PC with Excel 2010 Pro Plus (32-bit). So I'm ruling
out a syntax error that 2010 is nit-picking over that 2003 didn't.

I've run it on the app server with domain admin rights, so I can't
imagine it's a permission issue. I've compared installed features for
Excel and things like .NET and can't find anything relevant on my Win7
box that's missing on the Win2008 box.

Any ideas? I do have an open call with the third-party company, but
it's going very slow....

Perhaps?...
Early versions (pre XL12) use a different provider (JET) than the later
versions of Excel. In XL12 (v2007), the provider was changed to ACE and
so any pre XL12 XLAs that run in both early/late versions need to
specify which provider to use based on the version it runs in.

Here's an example of what I currently use since all my XL apps are
still XLAs...

Example:
Construct a connection string something like this:
If Application.Version => 12 Then
'use ACE provider connection string
Else
'use JET provider connection string
End If

This precludes that you'll have to construct 2 separate connection
strings. You could use constants for this:

Const sProvider As String = "Microsoft.Jet.OLEDB.4.0;"
Const sExtProps As String = "Excel 8.0;"

Const sProvider12 As String = "Microsoft.ACE.OLEDB.12.0;"
Const sExtProps12 As String = "Excel 12.0 Xml;"

If you know the data source beforehand, you could configure your code
something like this:

<aircode>
' Use a var to hold data source
sDataSource = "<FullPathAndFilename>"
If Application.Version => 12 Then
'use ACE provider connection string
sConnect = "Provider=" & sProvider12 & _
"Data Source=" & sDataSource & _
"Extended Properties=" & sExtProps12
Else
'use JET provider connection string
sConnect = "Provider=" & sProvider & _
"Data Source=" & sDataSource & _
"Extended Properties=" & sExtProps
End If
</aircode>

' Construct your SQL statement
sSQL = "SELECT * FROM..."

' Grab the data into a recordset
Set rsData = New ADODB.Recordset
rsData.Open sSQL, sConnect, adOpenForwardOnly, adLockReadOnly,
adCmdText

 

[Jay]

Hi Garry,

Perhaps?...
Early versions (pre XL12) use a different provider (JET) than the later
versions of Excel. In XL12 (v2007), the provider was changed to ACE and
so any pre XL12 XLAs that run in both early/late versions need to
specify which provider to use based on the version it runs in.

The strange thing is that this works ok on my Win7 PC with Excel 2010,
but not on the Win2008R2 Server with Excel 2010. So I don't think it's
an Excel issue per se, but Windows. Is it possible the Jet provider is
installed under Win7 but not Win2008R2? I'll try Googling on that and
see what I find...

I'm afraid I'm not the developer, so have no control over the code
(the xla is also password protected, so I can even view it). I'm just
the DBA at a small charity, and the software is provided by a third-
party corporation.

 

[GS]

Jay explained on 1/25/2012 :

Hi Garry,



The strange thing is that this works ok on my Win7 PC with Excel 2010,
but not on the Win2008R2 Server with Excel 2010. So I don't think it's
an Excel issue per se, but Windows. Is it possible the Jet provider is
installed under Win7 but not Win2008R2? I'll try Googling on that and
see what I find...

I'm afraid I'm not the developer, so have no control over the code
(the xla is also password protected, so I can even view it). I'm just
the DBA at a small charity, and the software is provided by a third-
party corporation.

V2010 does not use the JET provider. That was dropped in v2007. You
have options:

a. Contact the developer for an upgrade <**preferred action**>

b. Upload/attach the file so someone here can have a look at it.