Accessing Excel files on a x64 machine


In the old times while all the CPUs were 32bit, we were happily using JET OLEDB Provider reaching Excel or MDB files for long time without any issues.

After we started using x64 CPUs and x64 Windows machines, we noticed that JET OLEDB Provider is not working. The reason for this was x64 Windows operating systems were not containing x64 bit JET OLEDB Provider but they had 32bit JET OLEDB Provider. We needed to recompile our applications as 32bit by changing the “Target CPU” as x86 in our Visual Studio Projects (remember that default “Target CPU” fro a Visual Studio Project is “Any CPU”), or using  a 32bit application pool for a web application just to be able host our app in a 32bit w3wp.exe.

Now we have a new guy in the town. Let me introduce it : “Microsoft ACE OLEDB Provider”. It’s “ProgID” (in terms of COM/OLEDB) is “Microsoft.ACE.OLEDB.12“. It does not come within the OS, you should install this manually by downloading it from here . The name of the download is “Microsoft Access Database Engine 2010 Redistributable” as this 64bit ACE OLEDB Provider is the result of our Office 2010. It has been around in the scene with Office 2007 but it was available as 32bit only.

With the Office 2010, we have 64bit ACE OLEDB Provider which is good news πŸ™‚

Here are some cases with the details how to use this new OLEDB Provider :

CASE 1: Retrieving data from an Excel file in SQL Server

Please follow the steps below :

  • 1) Download our 64bit version (AccessDatabaseEngine_X64.exe) of our “Microsoft Access Database Engine 2010 Redistributable” from the URL below :

http://www.microsoft.com/downloads/details.aspx?familyid=C06B8369-60DD-4B64-A44B-84B371EDE16D&displaylang=en&WT.mc_id=soc-n-[TR]-loc-[Services]-[farukc]

  • 2) Install AccessDatabaseEngine_X64.exe on your SQL Server machine

 

  • 3) Open a new “Query Window” in SQL Server Management Studio (SSMS) after connecting to your SQL Server Engine and issue the T-SQL commands below :

 

USE [master]

GO

 

sp_configure ‘show advanced options’, 1

GO

RECONFIGURE WITH OverRide

GO

sp_configure ‘Ad Hoc Distributed Queries’, 1

GO

RECONFIGURE WITH OverRide

GO

EXEC master.dbo.sp_MSset_oledb_prop N’Microsoft.ACE.OLEDB.12.0′ , N’AllowInProcess’ , 1

GO

EXEC master.dbo.sp_MSset_oledb_prop N’Microsoft.ACE.OLEDB.12.0′ , N’DynamicParameters’ , 1

GO

  • 4) Now try executing the query below :

 

select * FROM OPENROWSET(‘Microsoft.ACE.OLEDB.12.0’, ‘Excel 12.0;Database=C:\temp\test.xls’, [Sheet1$])

    Yuppe I got the data from XLS πŸ™‚

CASE 2: Retrieving data from an Excel file in a .NET app

  • 1) Download our 64bit version (AccessDatabaseEngine_X64.exe) of our “Microsoft Access Database Engine 2010 Redistributable” from the URL below :

http://www.microsoft.com/downloads/details.aspx?familyid=C06B8369-60DD-4B64-A44B-84B371EDE16D&displaylang=en

  • 2) Install AccessDatabaseEngine_X64.exe on your x64 machine  (Likely that it will be your web server which will be hosting your ASP.NET app in a 64bit app pool or the server that you will run your .NET Windows Service as 64bit etc.)

 

  • 3) Use the System.Data.OleDBConnection with the connection string below :

 

OleDbConnection conn = new OleDbConnection(“Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\\temp\\test.xls;Extended Properties=\”Excel 12.0;HDR=YES;\””);

P.S. 1 :  If you are going to deploy this app to a machine without ACE OLEDB Provider, don’t forget that you should install “Microsoft Access Database Engine 2010 Redistributable” to the target machine.

P.S. 2 : If you have Office 2010 32bit is installed on the machine you cannot install “Microsoft Access Database Engine 2010 Redistributable” 64bit .

Comments (57)

  1. Awsome information.  The Excel connection string change tricked me. Assumed it would automatically increment from 12 to 14.

  2. Oskar says:

    I have a web application that use this component to export a large data set to an excel file in xlsx format. There should be about 12000 rows in the generated file. On a workstation with windows 7 and "access runtime engine" 2007, with SP2 and another update installed, this works fine. However on the production win2003 server, with the same versions of access runtime engine installed, only about 6000 rows get written to the excel file. There is no error message – the file just isn't complete. We use "Provider=Microsoft.ACE.OLEDB.12.0"-

    On a brand new server, with win2008 R2 and access runtime engine 2010 64bit, the same problem occurs, Half the rows are missing.

    On the old server we set it to use the traditional xls-format instead, and that worked but produces horribly big files. And this fails on the new server. Maybe we need to install something else to make "Provider=Microsoft.Jet.OLEDB.4.0" work for producing xls-files.

    I'm having trouble finding documentation on these components. Any pointers?

    /Oskar

  3. Dan says:

    This is great news! However, when I install on 64bit WinServer2008 and attempt to access an Excel file I get:

    The 'Provider=Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine. The application has properly installed (I can see it in Add/Remove programs). I noticed that the version there is 14.0.

    Should the OleDb connection look like below?

    OleDbConnection conn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.14.0;Data Source=c:\temp\test.xls;Extended Properties="Excel 12.0;HDR=YES;"");

  4. Raj Wall says:

    Hi, I have your Case 1 with new Excel 2010 64-bit data I am trying to load into SQL Server 2008 R2 EXPRESS x64. Previously Excel 2007 imported fine, but Office 2010 x64 required uninstall of all 2007 x32 components. I downloaded and installed the AccessDatabaseEngine_X64.exe, rebooted and ran the SQL command (resulting in: : "Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install."), restarted SQL EXRESS but still get the error: "The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine." when I try to import the Excel file. thanks for any help!

    Regards,

    Raj

  5. Michael says:

    Do I still have to do all these steps if U already have installed 64-bit Office 2010?

  6. farukc says:

    Hi Oskar,

    If you check the download page for "Microsoft Access Database Engine 2010 Redistributable", the "Overview" paragraph has an explanation answering your situation. Here's the quote from its download page : ==========================================

    The 2007 Office System Driver are not intended:

    As a general replacement for Jet (If you need a general replacement for Jet you should use SQL Server Express Edition).

    As a replacement for the Jet OLEDB Provider in server-side applications.

    As a general word processing, spreadsheet or database management system -To be used as a way to create files. (You can use Microsoft Office or Office automation to create the files that Microsoft Office supports.)

    To be used within a service program or web application that relies on a Windows service.

    ==========================================

    So, neither ACE OLEDB Provider (either 32bit or this new 64bit) nor our good old friend Jet OLEDB Provider intended to be used in mult-threaded applciations like web applications.

    Best Regards,

    Faruk Celik

  7. Hi Faruk,

    according to your P.S. 2  you say that it is not pssible to install "Microsoft Access Database Engine 2010 Redistributable" 64bit when MS Office 2010 32bit is installed.

    This is only true if you call AccessDatabaseEngine_X64.exe without any arguments.

    But when you call

    AccessDatabaseEngine_X64.exe /passive

    then is installs with no complaints in parallel with ian existing 32bit counter part.

    Best Regards,

    Andreas Marschall

  8. Kevin says:

    So, how would one interact with Office in files in an environment like Oskar’s above?

  9. Dex says:

    Be very careful with reading Excel files in a .NET application as you will get different results if you execute code with the Excel file open vs the Excel file closed.  I've had Dates formated like October-10 show up as a string when the file is closed and show up as an OLE Date integer when the file is open.  This type of hack work is unacceptable out of Office software on release 14 of code.

  10. Hans S says:

    Hi Faruk,

    Regarding the quote

    "The 2007 Office System Driver are not intended:

    As a general replacement for Jet (If you need a general replacement for Jet you should use SQL Server Express Edition).

    As a replacement for the Jet OLEDB Provider in server-side applications. "

    If this is NOT INTENDED for server side use, then what it?

    Do you know any supported way to read Excel files and Access db:s at the SQL Server machine?

    Regards

    Hans S

  11. RedEagle says:

    Hi everyone!

    I'm trying to access Excel files using this instructions and am able to open XLS files but when trying to open XLSX files I get a "External table is not in the expected format" error…

    Details at:

    social.microsoft.com/…/e294886f-9dac-4d1a-b927-5d10ac6d25db

    I really need to open XLSX files so I would very much appreciate your help

  12. RedEagle says:

    Hello everyone!

    I'm having the problem with opening XLSX files (External table not in a recognized format) described here:

    social.microsoft.com/…/c407ada2-4a37-4d77-af4b-ce31a6fb9259

    Aby help would be very much appreciated

  13. Sandeep Limaye says:

    Andreas, thanks for the information. How do we then select the correct (64-bit) version through the program? Do we need to change the connection string in any way?

  14. Rohan says:

    Thanks a million. This problem was pestering me for some time. I tried changing the connection string , but the AccessDatabaseEngine saved me

  15. farukc says:

    In response to Michael's question "Do I still have to do all these steps if U already have installed 64-bit Office 2010?"

    No you don't.

    But if yo've installed Office 2010 on your server please be sure that you're not doing "Office Automation" which will need Excel.exe, WinWord.exe etc. to be spawned. Here's the KB article about "Office Automation" ===> support.microsoft.com/…/257757

  16. antony says:

    hi

    i couldnot install AccessDatabaseEngine_X64.exe, it says i have to remove 32-bit office products. does it mean i have to uninstall the office 2010 i have on the computer? I have 64-bit windows and office 2010.

  17. Ali Reza Pooneh says:

    Thanks very much!

  18. Wyatt Wong says:

    I am using Visual Studio 2010 Ultimate with SP1 and Microsoft Office 2010 x64 on Windows 7 Ultimate x64 with SP1, when I add an Microsoft Access 2010 .accdb file into the VS2010 solution, VS2010 present me a dialog box of "Choose a Database Model" and ONLY "DataSet" option is available. After I click "Next", I got the error of "The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.

    I have already installed"Microsoft Access Database Engine 2010 Redistributable" (AccessDatabaseEngine_x64.exe) from http://www.microsoft.com/…/details.aspx and still got this error.

    What am I missing ?

  19. farukc says:

    @Wyatt,

    As you already have Office 2010 x64 installed on your machine, you have x64 version of 'Microsoft.ACE.OLEDB.12.0' OLEDB Provider is installed.

    Visual Studio 2010 (or 2008, 2005) IDE itself is a 32bit process named "devenv.exe" (You can check from "Task Manager", you will "*32" next to devenv.exe like "devenv.exe *32"). We don't have 64bit version of Visual Studio 2010 (or the old ones), it is/was always 32bit.

    As devenv.exe (Visual Studio 2010 for your scenario) is a 32bit process, it cannot reach 64bit 'Microsoft.ACE.OLEDB.12.0' OLEDB Provider or any 64bit OLEDB provider at all.

    So, you have to have 32bit version of 'Microsoft.ACE.OLEDB.12.0' OLEDB Provider.

    Please download AccessDatabaseEngine_x86.exe to have 32bit version of 'Microsoft.ACE.OLEDB.12.0' OLEDB Provider on your machine.

    I'm not sure if it will allow you to have installed on your machine as you have Office 2010 x64. If it says "sorry, you have 64bit Office I cannot install 32bit AccessDatabaseEngine_x86), you will have to uninstall your Office 2010 x64 and install Office 2010 x86.

    Hope that helps.

    Regards,

    Faruk

  20. Wyatt Wong says:

    I have downloaded the 32-bit AccessDatabaseEngine.exe and it will NOT allow me to install since it detected that I have installed Office x64. I knew the VS2010 is a 32-bit application. It seems the problem is similar to Outlook x64 cannot ActiveSync with Windows Phone 6.5 because WMDC 64-bit driver is calling the 32-bit version instead of a "real" 64-bit driver.

    I think the Office x64 is of no use because of incompatibility with all other 32-bit applications. As such, I would like to ask what is the "use" of the AccessDatabaseEngine_x64.exe as various guys have mentioned in this comment history ?

  21. Wyatt Wong says:

    I have finally uninstalled Office 2010 x64 and installed back Office 2010 x86, now VS2010 can connect to the Access 2010 .accdb files successfully.

  22. farukc says:

    "AccessDatabaseEngine_x86.exe" and "AccessDatabaseEngine_x64.exe" is a runtime for you to have ACE  on your "Server" machines on production. In case if your applications need to connect to .accdb, .xlsx etc. files as data source.

    We do not suggest installing whole Microsoft Office on a server as it's not intended to be used on server machines but consumer (Information Worker, IW) desktop machines.

  23. Wyatt Wong says:

    I want to know how to detect the Access Database Engine 2010 ""AccessDatabaseEngine_x86.exe" and "AccessDatabaseEngine_x64.exe" has already installed or not to a server or desktop machine ?

  24. Vinnícius says:

    Hi

    i follow all steps in case 2 and pay attention to the two warnings, and the error occur:

    "The Microsoft Access database engine cannot open or write to the file ''. It is already opened exclusively by another user, or you need permission to view and write its data"

    But i already give the write permission to all folders… i don't know what to do…

    What am I missing ?

    Tks

    PS:Sorry for my english, i'm brazilian =)

  25. farukc says:

    H Vinnicius,

    Please use our tool Process Explorer (technet.microsoft.com/…/bb896653) and Ctrl-F (Find handle or substring) and type your .accdb or .xlsx file you're trying to connect.

    It will show you the process using your .accdb or .xlsx file.

    HTH πŸ™‚

    By the way you're English is not bad πŸ˜‰

  26. Francisco says:

    I have Office 2010 64-bits installed under Windows 7 64-bits, however when I try to read in SSIS 2008 a .xlsx file, the following message appears:

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

    Why is that?

  27. Nel passato, quando tutte le CPU erano a 32bit, ognuno di noi ha avuto il piacere di utilizzare almeno

  28. farukc says:

    @Francisco

    As BIDS (Business Intelligence Development Studio) tool itself is a 32bit process (devenv.exe, you can check it from Task Manager), it cannot reach 64bit ACE OLEDB Provider.

    You should have 32bit ACE OLEDB Provider installed on your machine. But you cannot install 32bit

    ACE runtime on a machine with Office 2010 64bit installed.  You should uninstall your 64bit Office 2010 and either install 32bit Office 2010 as a whole product or just 32bit ACE Runtime, because you cannot have 64bit and 32bit ACE runtime in the same machine.

  29. farukc says:

    @Wyatt Wong,

    Ona 64bit OS;

    1) If 32bit is installed "ACEOLEDB.DLL" should exists here :

       C:Program Files (x86)Common FilesMicrosoft SharedOFFICE14ACEOLEDB.DLL

    2) If 64bit is installed "ACEOLEDB.DLL" should exists here :

       C:Program FilesCommon FilesMicrosoft SharedOFFICE14ACEOLEDB.DLL

    On a 32bit OS;

      "ACEOLEDB.DLL" should exists here :

       C:Program FilesCommon FilesMicrosoft SharedOFFICE14ACEOLEDB.DLL

    HTH,

    Faruk

  30. zzz says:

    Remember to check files using the

    CommonProgramFiles

    CommonProgramFiles(x86)

    CommonProgramW6432

    env. variables or other solution.

  31. Mahalakshmi S says:

    Hi,

    I'm not able to install the AccessDatabaseEngine_X64. it gives me an error installation of the package failed.

    can u pls help me out with this.

  32. farukc says:

    @Mahalakshmi S,

    If you're receiving an error message during the installationcould you please share ?

    Also I will need the answers to the questions below :

    1)Which OS you're trying to install AccessDatabaseEngine_X64 on ? (Please don't forget to add the CPU version x86, x64 of your OS)

    2) Do you have Microsoft Office installed on your machine ? If it is, which Microsoft Office version (2007, 2010) is installed ? If it is Office 2010, is it 32bit or 64bit ?

  33. Mahalakshmi S says:

    Hi Faruk,

    The exe has been istalled successfully. it is a 64bit OS.

    But even after that i get an error saying The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.

    And the ACEOLEDB.DLL is present in the location : C:Program Files (x86)Common Filesmicrosoft sharedOFFICE14

    this DLL which is present in this location is not the newly installed file.

    Can u please tell me where the newly installed files are stored and should i execute any commands to use the newly installed files.

    The system is a 64bit OS and it has MS Office 2007 version installed.

    Can u pls tell me the steps i need to follow to resolve the error.

    Thanks,

    Mahalakshmi

  34. As Andreas Marschall wrote in his post above, it is possible to install the Access Database Engine in the passive mode.

    AccessDatabaseEngine_X64.exe /passive

    If your programm is still not runing, you could install the 32 bit version in the passive mode too.

    After that your programm should run (also if you choose "Any CPU" in VS).

    (It worked for me)

    Regards

    I.Pfeifer

  35. farukc says:

    Mahalkhshmi,

    Could you please do 64bit and 32bit UDL Test and let me know in which one(s) you see "Microsoft Office 12.0 Access Database Engine OLE DB Provider" in "Provider" list. Here are my blog posts for "UDL Test" (s) .

    1) 64bit UDL Test on a 64bit OS ==> blogs.msdn.com/…/basics-first-udl-test.aspx[TR]-loc-[Services]-[farukc]

    2) 32bit UDL Test on a 64 bit OS ==> blogs.msdn.com/…/udl-test-on-a-64-bit-machine.aspx[TR]-loc-[Services]-[farukc]

    Thanks

  36. You should install both versions.

    It is not possible if you execute the .exe, therefore you must install both of them in the passive mode.

  37. this driver does not work, installed 64-bit drivers, rebooted server 2008 R2, but getting the same error as most of the users here are getting …. driver is not registered…

  38. Mohamed Zaatar says:

    i have VS 2010 while reading Excel sheet from MS Office 32 bit. I installed the 32 OLE provider but i got the error  "Could not find installable ISAM."

    any help?

  39. farukc says:

    @Nick77,

    Could you please do 64bit and 32bit UDL Test and let me know in which one(s) you see "Microsoft Office 12.0 Access Database Engine OLE DB Provider" in "Provider" list. Here are my blog posts for "UDL Test" (s) .

    1) 64bit UDL Test on a 64bit OS ==> blogs.msdn.com/…/basics-first-udl-test.aspx[TR]-loc-[Services]-[farukc]

    2) 32bit UDL Test on a 64 bit OS ==> blogs.msdn.com/…/udl-test-on-a-64-bit-machine.aspx[TR]-loc-[Services]-[farukc]

    This will let us know that which version of ACE OLEDB provider is installed&registered on your machine.

    If you see that "64bit UDL Test on a 64bit OS" succeeds, high likely your application giving you "provider is not registered" error message is a 32bit application and you haven't installed the 32bit version of ACE OLEDB.

    As the other commenters said you will need to install both of them in passive mode using /passive switch (Check the comments above for details to install both) or if you only need 32bit version of ACE OLEDB, then uninstall 64bit package you installed and install 32bit package (After installing 32bit, be sure that 32bit UDL test succeeds)

  40. farukc says:

    @Mohamed Zaatar

    Are you receiving "Could not find installable ISAM" when running the app inside Visual Studio or outside of Visual Studio ?

    As I'm repeating in my comments above, please do "UDL Test"s on your machine to see if the "ACE OLEDB Provider" is working fine first.

    If you get the "Could not find installable ISAM" through "UDL Test"s too, please uninstall/re-install the 32bit ACEOLEDB provider

    please let me know :

    1) Your Office version

    2) You OS version (together with the CPU version : x86 ? x64 ?)

    3) What is the Excel ifle extension (xlsx ? xlsm ? xls ? etc.)

    4) What is your application type ? (consol app, windows forms app, ASP.NET web app etc  ; if it is a web app what is your application pool bitness 32bit or 64bit ?)

  41. IndiaTango says:

    I have this code working on 64-bit Win 7 / Excel 2010. Note the ISAM=1 extended property – this fixed the "Could not find installable ISAM." problem for me. Presumably it will be the same for 32-bit Excel

           public void Method2()

           {

               string cStr = "Provider=Microsoft.ACE.OLEDB.12.0; "

                           + "Data Source=C:\users\<yourname>\documents\Book1.xlsx; "

                           + "Extended Properties="Excel 12.0;HDR=YES;IMEX=1;ISAM=1;"";

               OleDbConnection conn = new OleDbConnection(cStr);

               conn.Open();

               // Select the data from Sheet1 of the workbook.

               string stmt = "select * from [Sheet1$]";

               OleDbDataAdapter ada = new OleDbDataAdapter(stmt, conn);

               DataSet ds = new DataSet();

               ada.Fill(ds);

               dataGridView1.DataSource = ds.Tables[0].DefaultView;

               dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;

               conn.Close();

           }

  42. Bernd Lechner says:

    After the succesful installation I have created a linked server from SQL Server 2008 R2 to a Access database on a network file share. I always get a error when the file is still in use. I don't have this Problem form a linked server from a 32Bit SQL Server. Is there a way that this also works from 64Bit ?? Thanks very much.

  43. Very useful. Thank You says:

    Thanks for the detailed reasoning and solution

  44. Excellent article. Thanks!

  45. ren_123 says:

    Regarding using Microsoft.ACE.OLEDB.12.0 in a web application, does it mean that this provider cannot be used in any web application?

  46. PMC says:

    I get the:

    "The Microsoft Access database engine cannot open or write to the file ''. It is already opened exclusively by another user, or you need permission to view and write its data"

    Error.

    It occurs when running a mailmerge between Excel & Word and sends the word mail files as attachments to email addresses using outlook, all the code is in a VBA script in the source excel file. This worked fine in XP & office 2010 (32 bit), it gets about 25% of the way through the exercise (so sends some fine) but then falls over.

    Currently running W7E 64bit SP1 & Office 2010. Is this related to the Jet DB issue and will patching my local client with the redistributable file be OK?

    Sorry I'm just a n Excel user rather than an IT developer so not sure if this make sense.

  47. farukc says:

    I don't think that the issue you are experiencing is related to the one I'm explaining in this blog post.

    I will suggest you to ask this in Microsoft Forums here :

    social.technet.microsoft.com/…/home

  48. Uğur Aldanmaz says:

    Thank you for information Faruk. Also thanks for humorous wording. πŸ™‚

  49. PMC says:

    Thanks for responding Faruk I'll try over there.

  50. Sandy says:

    Hi Faruk,

    Thanks for your post. I am having a 64 bit dev  machine with office 2010 64 bit. I am using this connection string

    string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + excelFilePath + ";" +

               @"Extended Properties=""Excel 8.0;IMEX=1;""";

    This works while i am trying to read excel file.

    Howveer the same fails onto prod machine with the error as

    System.Web.HttpUnhandledException (0x80004005): Exception of type 'System.Web.HttpUnhandledException' was thrown. —> System.Data.OleDb.OleDbException (0x80004005): Unspecified error

      at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection)

    Please help

  51. farukc says:

    Hello Sandy,

    It looks like your app is an ASP.NET app because the callstack/stack trace in the error shows calls from System.Web namespace. It is possible that you are getting the 0x80004005 error because your IIS application pool's identity cannot reach to the file you specified in "excelFilePath". Please check this first.

    Also, JET OLEDB provider will need to create .tmp files under %temp% folder of the user running the process hosting it. In this case, process is w3wp.exe and the user running the process is the application pool identity you set. Please try "Response.Write"ing the %TEMP% environmental variable (Refer to : msdn.microsoft.com/…/77zkk0b6(v=vs.110).aspx ). In this way you will see the %TEMP% folder for the current user/applicaiton pool identity running your application pool.

    You should grant read/write access to the %temp% folder.

    Our free tool "Process Monitor" (technet.microsoft.com/…/bb896645.aspx) will be your best friend to see the file activities. You should find your w3wp.exe, add a filter to it and see if it gets any "ACCESS DENIED" for any of its calls.

    Hope that helps

  52. Sandy says:

    Hi Faruk,

    You simply rock. Thanks for your prompt reply. Yes i did install the process monitor and saw that one of the temp folder inside 64 bit windows 2008 was not having proper permissions. it was right here

    C:windowsservicesserviceprofilesnetworkserviceappdatatemp.

    The issue is now resolved. Thanks

    BTW: I am having another DCom related issue related to word 2010 access. Is this the right forum i can post my question? Please let me know.

  53. farukc says:

    Hi Sandy,

    It's good to hear that your issue is resolved πŸ™‚

    For your DCOM issue I will suggest checking our MSDN Forums. A search for "dcom" keyword in MSDN forums bring lots of things : social.msdn.microsoft.com/…/home

    HTH,

    faruk

  54. T Neahr says:

    Faruk,

    I'm attempting to import/export data back and forth between MS Access 2010 64 bit and SSMS 2012.  In the Import/Export Wizard of SSMS, I can not view the Microsoft 12.0 Access Database Engine OLE DB Provider in the Data Source drop down.  

    I have installed the Microsoft Access Database Engine 2010 Redistributable and ran the queries you mentioned above.  I received the following message,

    OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Unspecified error".

    Msg 7303, Level 16, State 1, Line 1

    Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

    I have run the UDL test from the link you provided above, and the test connection failed.  

    Thanks in advance for your time,

    Tneahr

  55. farukc says:

    Hi T Neahr,

    SSMS is a 32bit process and actually you are spawning another 32bit process (C:Program Files (x86)Microsoft SQL Server100DTSBinnDTSWizard.exe) as well when you ran Import/Export Wizard.

    We have 64bit version of DTSWizard.exe located in C:Program FilesMicrosoft SQL Server100DTSBinnDTSWizard.exe . You can run this directly instead of selecting "Tasks/Import Data" (or Export Data) database context menu item while you're in SSMS.

    As you have MS Access 2010 64bit installed on your machine, you already have ACE in 64bit and you should see ACE OLEDB provider in the combobox when you ran 64bit DTSWizard.

    Thanks,

    Faruk

  56. T Neahr says:

    Faruk,

    Thanks for the response.  I'll keep investigating this topic as I only have 'MSDTSSrver.ini.xml' in the directory of C:Program FilesMicrosoft SQL Server100DTSBinn.  I located the DTSWizard.exe in a different directory: C:Program FilesMicrosoft SQL Server120DTSBinn.

    Thanks,

    T Neahr

  57. farukc says:

    Hi T Neahr,

    My bad, I gave the path for 100 which is SQL Server 2008 or 2008 R2 (90 is for SQL Server 2005, 100 is for 2008/2008R2, 110 is for SQL 2012 and 120 is for SQL 2014). Looks like you have SSMS of SQL 2014 and it is also ok to use it as long as it is 64bit.

    Regards,

    Faruk