x64 Support - Microsoft.Jet.OLEDB.4.0

[rossh...@gmail.com]

Hi,

I am 

• using LinqToExcel x64 Version 1.6.2 
  
• building to x64 target platform
• running site on windows server 2003 x64
• installed the 64 bit version of the Access Database Engine 

Despite all of the above, I still get the following error:

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

Building the site as 32bit and running the site using visual studio 2010 .net development server work just fine.

If I'm using the LinqToExcel x64 dll files, building and running on x64 machine; why is it using Jet.OLEDB.4.0?

Please help.

Thanks.

[Paul Yoder]

Oh, I forgot to mention that you will need to set the DatabaseEngine property to DatabaseEngine.Ace.

var excel = new ExcelQueryFactory("excelFileName");
excel.DatabaseEngine = DatabaseEngine.Ace;

I updated the documentation to reflect this.

Thanks,

Paul

[rossh...@gmail.com]

Thank Paul, working like a charm on x64 now.

I'm relieved that there was a missing step in the documentation; I was beginning to feel like an idiot, not being able to get things to work.

LinqToExcel = Awesome

On Wednesday, 25 April 2012 16:49:08 UTC+1, Paul wrote:

Oh, I forgot to mention that you will need to set the DatabaseEngine property to DatabaseEngine.Ace.

var excel = new ExcelQueryFactory("excelFileName");
excel.DatabaseEngine = DatabaseEngine.Ace;

I updated the documentation to reflect this.

Thanks,

Paul

[simoncragg]

Hi,

I am still experiencing the same issue as described by rosshew. It
seems your prescribed fix to explicitly set the database engine to Ace
has worked for rosshew, however sadly for me I am still in the same
situation :(

Please allow me to clarify:-

- I am using LinqToExcel x64 Version 1.6.2 (obtained via NuGet).
- I am building to x64 target platform.
- I am running a website on Windows Server 2008 x64 (actually a
Windows Azure, web role).
- I have installed the 64-bit version of the Access Database Engine
from 'http://download.microsoft.com/download/2/4/3/24375141-E08D-4803-
AB0E-10F2E3A07AAA/AccessDatabaseEngine_x64.exe'.
- I am explicitly setting the DatabaseEngine in code to
DatabaseEngine.Ace.

The problem I am experiencing is:-

I am able to successfully query an uploaded .xlsx file, however when I
attempt to query an uploaded .xls (Excel 97-2003) file, I receive the
dreaded 'The 'Microsoft.Jet.OLEDB.4.0' provider is not registered on
the local
machine.' exception.

However, If I set the "Enable 32-bit Applications" setting in IIS to
"true" (against my web role's application pool), then I am able to
successfully query an uploaded ".xls" file, HOWEVER when I then
attempt to query an uploaded ".xlsx" file, I receive this exception:-

The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local
machine.

So, I am currently stuck between a rock and a hard place :)

Ideally, I would like to run my site in full 64-bit mode and be able
to query both .xlsx files and .xls files.

Any insights would be greatly appreciated!

Thanks
Simon

On Apr 26, 8:13 am, rosshew...@gmail.com wrote:
> Thank Paul, working like a charm on x64 now.
> I'm relieved that there was a missing step in the documentation; I
> was beginning to feel like an idiot, not being able to get things to work.
>

> *LinqToExcel = Awesome*

>
>
>
>
>
>
>
> On Wednesday, 25 April 2012 16:49:08 UTC+1, Paul wrote:
>
> > Oh, I forgot to mention that you will need to set the DatabaseEngine
> > property to DatabaseEngine.Ace.
>
> > var excel = new ExcelQueryFactory("excelFileName");
> > excel.DatabaseEngine = DatabaseEngine.Ace;
>
> > I updated the documentation to reflect this.
>
> > Thanks,
> > Paul
>
> > On Wed, Apr 25, 2012 at 9:54 AM,  wrote:
>
> >> Hi,
>
> >> I am
>

> >>    - using LinqToExcel x64 Version 1.6.2
> >>    - building to x64 target platform
> >>    - running site on windows server 2003 x64
> >>    - installed the 64 bit version of the Access Database Engine<http://www.microsoft.com/downloads/info.aspx?na=41&srcfamilyid=c06b83...>

>
> >> Despite all of the above, I still get the following error:
>

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

[Paul Yoder]

Simon,

Can you send me the code you're using (only the code dealing with LinqToExcel). I can't figure out why it's happening off hand, but looking at your code might help me.

Thanks,

Paul

[simoncragg]

Hi Paul,

Here is the code which queries the .xls (and .xlsx file).

using System;

using System.Collections.Generic;

using System.Linq;

using LinqToExcel;

using LinqToExcel.Domain;

using Remotion.Collections;

namespace MyNamespace.Domain

{

    public class StockItemWorkBook

    {

        protected const string WorksheetName = "StockItems";

        protected readonly ExcelQueryFactory _excel;

        public StockItemRows StockItemRows { get; set; }

        public StockItemWorkBook(string filePath)

        {

            _excel = new ExcelQueryFactory(filePath);

            _excel.DatabaseEngine = DatabaseEngine.Ace;

            StockItemRows = new StockItemRows();

            StockItemRows.AddRange(from x in _excel.Worksheet<StockItemRow>(WorksheetName) select x);

        }

    }

    public class StockItemRow

    {

        public string ItemCode { get; set; }

        public string ItemDescription { get; set; }

    }

    public class StockItemRows : List<StockItemRow>

    {

    }

}

The code above has been taken from my production code.  I've only changed the namespace name, consolidated a number of classes together and removed some methods which are not relevant to the issue (i.e. Validation logic etc).

The exception is occurring within the constructor for StockItemWorkBook, on the last line (the one that performs the query).  I haven't pruned or changed this constructor logic in any way; it is identical to the production code.

Please let me know if there's anything else I can send you.  One last thing, when I compile I am using the "Any CPU" target.  I am 100% confident though that the production code is running in a 64 bit process.  Again, I am using the x64 version of the AccessDatabaseEngine.

Thanks for your help!

Regards

Simon

On Friday, 8 June 2012 04:17:42 UTC+1, Paul wrote:

Simon,

Can you send me the code you're using (only the code dealing with LinqToExcel). I can't figure out why it's happening off hand, but looking at your code might help me.

Thanks,

Paul

[simoncragg]

Hi Paul,

As requested, I set all my project settings to target x64.  I deployed the solution and re-tested.  Unfortunately, I am still experiencing the same behavior, that is, I am able you successfully query a .xlsx file, but attempting to query a .xls file results in the "The 'Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine.' exception.

Looking on the web server, I can see that all W3WP processes are running in 64 mode.  I am also using the 64 Bit version of the 2010 AccessDataEngine, downloaded from this link:-

http://download.microsoft.com/download/2/4/3/24375141-E08D-4803-AB0E-10F2E3A07AAA/AccessDatabaseEngine_x64.exe

Back on my local machine, I have added LinqToExcel x64 to my project using NuGet.  When managing my NuGet packages, it's clear that I am using the 64-Bit version of LinqToExcel.

I don't know what else to try?  Any ideas?

Thanks

Simon

[kirilen...@gmail.com]

Hi,

The problem is in hardcoded connection string buildind -  internal class: LinqToExcel.Query.ExcelUtilities, method:

       

 internal static string GetConnectionString(ExcelQueryArgs args)

        {

            string str1 = args.FileName.ToLower();

            string str2 = !str1.EndsWith("xlsx") && !str1.EndsWith("xlsm") 

                ? (!str1.EndsWith("xlsb") 

                ? (!str1.EndsWith("csv") 

                ? (args.DatabaseEngine != DatabaseEngine.Jet 

                ? string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\"", (object)args.FileName) 

                : string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"", (object)args.FileName)) 

                : (args.DatabaseEngine != DatabaseEngine.Jet ? string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"text;Excel 12.0;HDR=YES;IMEX=1\"", (object)Path.GetDirectoryName(args.FileName)) 

                : string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"text;HDR=YES;FMT=Delimited;IMEX=1\"", (object)Path.GetDirectoryName(args.FileName)))) 

                : string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\"", (object)args.FileName)) 

                : string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\"", (object)args.FileName);

            if (args.NoHeader)

                str2 = str2.Replace("HDR=YES", "HDR=NO");

            return str2;

        }

This conditional statement does not work properly for all cases, e.g. here is my code:

        public string FilePath

        {

            get { return Path.Combine(Directory.GetCurrentDirectory(), "Data", "Dev Skills profile template.xls"); }

        }

        public void ReadSkills(string filePath)

        {

            var excel = new ExcelQueryFactory(filePath) { DatabaseEngine = DatabaseEngine.Ace };

            

            var worksheets = excel.GetWorksheetNames().Select(x => new Skill { SkillTitle = x }).ToList();

        }

In this case GetConnectionString() returns:

Provider=Microsoft.Jet.OLEDB.4.0;

Data Source=C:\template.xls;

Extended Properties="Excel 8.0;HDR=YES;IMEX=1"

And oleDbConnection1.Open(); fails with mentioned error.

If I correct conn string by set mannualy Provider to "Microsoft.Ace.OLEDB.12.0" it works.

I suppose that nice option would be good to add public property Providel into ExcelQueryFactory or somethong like that, to be able to specisy conn str explicitly

Середа, 25 квітня 2012 р. 17:54:55 UTC+3 користувач rossh...@gmail.com написав:

[Paul Yoder]

I believe this issue is fixed in the most recent release (version 1.6.6)

Please let me know if the issue is still around after updating to 1.6.6

Thanks,

Paul

[jt]

Hi Paul,

I'm having a similar issue. Here is my code:

            ExcelQueryFactory excel = new ExcelQueryFactory(filePath);

            excel.DatabaseEngine = DatabaseEngine.Ace;

            List<string> workSheets = excel.GetWorksheetNames().ToList();

If I run this code on an xls file, it works regardless of whether or not I specify the engine to be ace or jet. If I run this code on an xlsx file, it fails with the exception "The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine." (regardless of whether or not I specify the engine).

I have installed the 64 bit access database engine, and am using the 64 bit version of LinqToExcel. I have tried using the 32 bit version of LinqToExcel and that did not make a difference either.

[jtro...@tstoverland.com]

I should also mention this is an asp.net mvc application, and I'm using version 1.6.6 of Linq to Excel.

[Paul Yoder]

jt, can you try reading some rows from the spreadsheet and see if you receive the same error. (Comment out the line that gets the worksheet names)

            ExcelQueryFactory excel = new ExcelQueryFactory(filePath);

            excel.DatabaseEngine = DatabaseEngine.Ace;
            foreach (var row in excel.Worksheet())
               // read the row

[jt]

Hi Paul,

I actually managed to fix this issue by installing the access engine for office 2007 instead of 2010.

[dave.00....@gmail.com]

Hi All,

It seems MS have changed the download link for the office 2007 redistributable. try this one:-

http://www.microsoft.com/en-us/download/confirmation.aspx?id=23734

or this one

http://www.microsoft.com/en-gb/download/details.aspx?id=23734

but either way make sure that the page title is something like 

2007 Office System Driver: Data Connectivity Components

and the file version is 12.0.4518.1014 (or similar)

I've tried this in x86 only, and I don't know if this works for x64 or not. Come on Linq2Excel - time to start supporting later versions of office so this hunting around for old drivers isn't needed!

(fab tool BTW)